Truncating Log File in SQL Server

Sometimes a transaction log file in SQL Server gets too large and needs to be shrunk.

To see how much free space you will be able to reclaim, run the following query before and / or after you shrink the log file.

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Below is a quick T-SQL snippet of code that you can run to truncate (shrink) you SQL Server transaction log file.The snippet will shrink you database to 1MB. You will need to change MyDB and MyDB_Log to match your database.

USE MyDB
GO
DBCC SHRINKFILE(MyDB_Log, 1)
BACKUP LOG MyDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDB_Log, 1)
GO

I believe the log file is usually named using the convention MyDB_Log, but if that does work, or you want to check for sure, just get properties on your database by right-clicking it in SQL Server Management Studio, and going to the Files page. Look at the Logical name of the log file. That is what you want to use.

SQL Server needs some free space just for daily operations. So, don’t be surprised if your log file grows a little after you shrink it. Though, typically, it will be a small amount.

WARNING: With any of this, you will lose the log of transactions since you have deleted the transaction log.

Troubleshooting

The above didn’t error, but it didn’t reduce the size of the log file. Here are some things to check.

• Is there a backup job that is currently running? If so, wait for it to stop, or stop the backup job.
• Is there a long transaction that is currently running? If so, wait for it to stop, or kill the transaction.
• Is there an SSIS package or other job running that could potentially lock the database you are trying to shrink? If so, wait for it to stop or kill it.
• If the log file doesn’t shrink (usually due to a transaction running), you may need wait for the transaction to finish, or to put the database in single user mode (under Properties | Options). Then run the DBCC SHRINKFILE command. 
• If all else fails and you get desperate, you can detach your database (you may need to put it in single user mode first especially if you are out of disk space), then manually go to the file system and manually delete the log file. Then attach the database again; a new log file will be created.

You may also find other entry on this topic useful. For more information on the topic, I recommend MSDN docs. They are actually quite helpful on this topic. I also, recommend this blog posting. It is where I started.