Shrink / Delete a SQL Server Log File

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

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

or

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.

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.