While working on a project that uses WCF to connect to an endpoint that checks SQL for my credentials, I suddenly started receiving errors from SQL Server in the Event Log reporting:
The transaction log for database ‘…’ is full due to ‘LOG_BACKUP’.
Then the app was stuck with the message whenever I (attempted to) start it. After a bit of googling, it came back that probably one of the SQL files that hold information on the database grew too large. Sure enough, the log file size on disk was huge at 471 GB.
The following steps should only be applied to developer targeted throw-away databases. It does not backup the database in case of error. Review other solutions to safely shrink the database files in production systems or if the data cannot be restored without existing backups. Here be dragons!
The solution that finally worked for me is at StackOverflow. As simple SQL script that shrinks the any database file – but I will only shrink the log file. Shrinking is the process of removing all unused file space and only using exactly what the file needs. To do so, you need the logical name for the file you want to shrink and the database name (blurred below).
- Start SSMS.
- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
- Expand Databases, right-click the database from which to delete the file, and then click Properties.
- Select the Files page.
- In the Database files grid, examine the Logical Name for the file you want to shrink.
USE MyDB -- This is the database
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE MyDB
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ('MyDB_log', 1)
GO
-- Reset the database recovery model.
ALTER DATABASE MyDB
SET RECOVERY FULL;
GO
When I ran the script, it took several seconds to complete and file disk size went from 471GB to 230MB. As you can imagine, my amount of free storage on the disk went up by a similar amount.
Final Conclusion
I have no idea why SQL thought it needed such a huge amount of extra space. I don’t really care enough to research it since it has cost me several hours to fix it. Don’t judge me too harshly.