.. title: Shrink / Delete a SQL Server Log File
.. slug: shrink_mssql_log_file
.. date: 2010/01/27 13:03
.. tags: sql, mssql, database
.. description: How to shrink the MSSQL transaction logs
.. type: text

When you want to delete a transaction log file (when you have more than one), you need to do the following steps:
 
.. code-block:: sql

 ALTER DATABASE dbname SET RECOVERY SIMPLE
 ALTER DATABASE dbname SET RECOVERY FULL

or

.. code-block:: sql

 USE TimsDatabase
 BACKUP LOG FileName WITH TRUNCATE_ONLY
 GO
 DBCC SHRINKFILE ( 'TimsDB_log1' )
 GO
 DBCC SHRINKFILE ( 'TimsDB_log2' )
 
 GO
 
 ALTER DATABASE TimsDB REMOVE FILE TimsDB_log2

That should then delete the log file. Don't forget to do a complete backup afterwards.

.. code-block:: sql

 USE Master
 GO
 DECLARE @DEST AS VARCHAR ( 200 )
 SELECT @DEST = 'Z:\SQL\Backup\TimsDB\TimsDB-' + CONVERT ( CHAR ( 10 ), GetDate(), 120 ) + '.BAK'
 BACKUP DATABASE TimsDB TO DISK = @DEST
 GO

That gives you a complete backup for additions to the Transaction Log.

There is a word of warning over this - There are not many times you would really want to shrink a Transaction Log. SQL Server expands it as it's needed. The only REAL time I've found it necessary is when there was a SIGNIFICANT amount of growth due to some special processing that was being done. This isn't something you would normally want to do as it does have a negative impact to performance when SQL Server has to expand the Transaction Log.