Search This Blog

2015-06-11

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

FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
225395281921519220640
22539522621441519230640
22539525160961519240640
227852877004815192501280
2262144104857615192606423000000019400021
2262144131072015192706424000000009300239
2262144157286415192806425000000016500010
2262144183500815192906426000000015700030
2262144209715215193006427000000016200020
2262144235929615193106428000000008000001
325395281921522300640

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

FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
21341521928192145522640
2134152192134160384145480640

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

1 comment:

Tekslate Trainings said...

it's good blog about guidewire basics, want for more information and training and free demo, tutorials can visit....
Check this site Tekslate for in- guiddewire Training
Go here if you’re looking for information guidewire Training

Blog Archive