MS SQL Server: Truncate Transaction Log File

Product: MS SQL Server
Version: 2008R2 - 2012

Following is the steps to truncate and optionally shrink DB transaction log file.  Unlike other post you found in the internet, this step includes replication database, which many others do not consider

Step 1: Determine whether DB replication is enabled.  If the database name shows up, then you have a choice to disable it (applicable if you restore the DB), or tell replications to ignore data in transaction log files (will require to re-sync replication later)

select name, log_reuse_wait_desc from sys.databases where log_reuse_wait_desc = 'REPLICATION'

Step 2a: If you want to tell replication to ignore all data in transaction logs.  The database must be published with replication in place.  This won't work if this database is restored, where replication is not setup

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

If you encounters following error, then the replication is not setup, and use Step 2b

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

Step 2b: Disable database replication.  Suitable to use in restored DB

USE [Database Name]
EXEC sp_removedbreplication

Before execute above statement, if you check sys.databases, you will see it shown "REPLICATION"

select name, recovery_model_desc, log_reuse_wait_desc from sys.databases

Step 3: Verify that all transaction logs are inactive, where STATUS=0.  If recovery mode is SIMPLE, then it must be 0 at this step.  If recovery mode is FULL, then there will be some still in STATUS=2.  Once you execute Step 7, then it will be 0

DBCC LOGINFO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	253952	8192	151922	0	64	0
2	253952	262144	151923	0	64	0
2	253952	516096	151924	0	64	0
2	278528	770048	151925	0	128	0
2	262144	1048576	151926	0	64	23000000019400021
2	262144	1310720	151927	0	64	24000000009300239
2	262144	1572864	151928	0	64	25000000016500010
2	262144	1835008	151929	0	64	26000000015700030
2	262144	2097152	151930	0	64	27000000016200020
2	262144	2359296	151931	0	64	28000000008000001
3	253952	8192	152230	0	64	0

Step 4: Verify transaction log wait status is no longer in Replication anymore.  It must display NOTHING

select name, log_reuse_wait_desc from sys.databases

Step 5: Verify whether DB in Simple or Full recovery mode.  Column recovery_model_desc will be either SIMPLE or FULL

select name, recovery_model_desc, log_reuse_wait_desc from sys.databases

Step 6: Check whether transaction log in first virtual log (StartOffset=8192) for 1st log file

DBCC LOGINFO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	134152192	8192	14552	2	64	0
2	134152192	134160384	14548	0	64	0

If last row has STATUS=2, then you won't be able to shrink it smaller than this size.

Regardless of recovery mode, change it to Simple recovery mode in order to force it to first virtual file

ALTER DATABASE [Database Name]  SET RECOVERY SIMPLE WITH NO_WAIT
CHECKPOINT
CHECKPOINT
DBCC LOGINFO
DBCC SHRINKFILE([Transaction Log File Name], 1)

Step 7: If DB recovery mode is SIMPLE, then proceed with shrink the transaction log file to 1 MB size

DBCC SHRINKFILE([Transaction Log File Name], 1)

If you don't want to shrink it, then use this statement

DBCC SHRINKFILE([Transaction Log File Name], EmptyFile)

Step 7: If DB recovery mode is FULL, then create a dummy backup before shrink

BACKUP database [Database Name] TO DISK = 'nul:'
BACKUP LOG [Database Name] TO DISK = 'nul:'

DBCC SHRINKFILE([Transaction Log File Name], 1)

Note: "nul" doesn't backup the database to physical file.  It just mimic a DB backup, but doesn't write to anywhere

If you don't want to shrink it, then use this statement

DBCC SHRINKFILE([Transaction Log File Name], EmptyFile)

If you restored the DB and would like to change it to Simple recovery mode, then use following:

ALTER DATABASE [Database Name]  SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE([Transaction Log File Name], 1)

Now the size of the ldf file should be 1 MB, if you specify 1, but no change if you specify EmptyFile

Step 8: Verify data in transaction log files.  There should be only 1 record with STATUS=2, if there is no other user using it.  You will get 1 row for each additional transaction log files with STATUS=0.  This is a good time to drop the additional transaction log files, but not the 1st transaction log file.  This is the restriction of database, and you can easily see that primary (1st) transaction log file has an entry with STATUS=2 (Active), so it can't be dropped

DBCC LOGINFO

Quick Steps - With Simple Recovery

Assume DB name is test1, and transaction log name is test1_log, and I would like to shrink the transaction log file to 1 MB

ALTER DATABASE test1 SET RECOVERY SIMPLE WITH NO_WAIT

EXEC sp_removedbreplication
DBCC LOGINFO
CHECKPOINT 2 times if DBCC LOGINFO not showing STATUS=2 for first row with StartOffset=8192

DBCC SHRINKFILE(test1_log, 1)

DBCC LOGINFO

Quick Steps - With Full Recovery

EXEC sp_removedbreplication

BACKUP database test1 TO DISK = 'nul:'
BACKUP LOG test1 TO DISK = 'nul:'

DBCC SHRINKFILE(test1_log, 1)

DBCC LOGINFO

If LOGINFO shows last row with STATUS=2, then you have to use next option

Quick Steps - With Full Recovery and Temporary Set to Simple Recovery

ALTER DATABASE test1 SET RECOVERY SIMPLE WITH NO_WAIT

EXEC sp_removedbreplication
DBCC LOGINFO
CHECKPOINT 2 times if DBCC LOGINFO not showing STATUS=2 for first row with StartOffset=8192

DBCC SHRINKFILE(test1_log, 1)

DBCC LOGINFO

ALTER DATABASE test1 SET RECOVERY FULL WITH NO_WAIT