SQL Server Log File Truncation

Had a little problem this morning running out of space on one of our SQL server boxes in development. The problem was caused by log file growth on an ICON instance.

Image

A standard shrink did not work so I detached the database and then deleted log file.

Image

To re-create the log file I had to copy the existing MDF file to a temporary file, create a new database of the same name and size, stop SQL server, copy back the original MDF file over the newly created file and then restart SQL Server.

I then put the database into emergency mode and ran a CHECKDB.

To get the database back out of single user mode I needed to kill my own SQL Server Management Studio connection to the database using kill <spid>.

All fixed now.

Image

Fixed the root cause of the problem by setting the database recovery model to simple rather than full (which should be OK for development)prevent future problems:

Image

Share

Leave a Reply