Wednesday, May 12, 2010

How To: Shrink a Log File

Are you in a dev environment and by screwing around with large dml statements you've created a monsert log file that you need to truncate and are not worried about loss of data? Is your database in Simple Recovery Mode? If you answered yes to these questions . . . fire away:

DBCC SHRINKFILE (<Log Name>, 0, TRUNCATEONLY)

If you get an error stating that the log file is in use but you have an immediate need to truncate the log file and don't want to wait you can also do the following:

1) Detach the database
2) Delete the log file from the file system
3) Attach the database and remove the the reference to the log file prior to completing the process in the UI

Upon attach, SQL Server will create a new log file at the minimum size specified.