Monday, August 15, 2011

Difference between truncating and shrinking the transaction log file


Truncating the transaction log:


Truncating a log file will change one or more VLF files status from active to inactive. By default, the SQL server has change the VLF from active to inactive and reuses it automatically (Just like a circular). 


Shrinking the transaction log:

       Shrinking the log file will reduce the physical log file size. Shrinking the log file is not best practice until you don’t have any other option.


Truncating the transaction log:
 
When does it truncate and reuse it automatically?

It depends upon the recovery model.

  •  SQL server run a CHECKPOINT  an every minute truncate/mark the inactive portion of the VLF files and reuse it automatically, When a database is in simple recovery model.
  • SQL server run the CHECKPOINT an every time truncate/mark the inactive portion of the VLF files and reuse it automatically, When you perform the log backup in under the FULL or BULK LOGGED Recovery model.
You can truncate the log file(VLF) manually by using the TRUNCATE_ONLY or  NO_LOG commands.

Here is the test:

Create DATABASE test


ALTER DATABASE test SET RECOVERY full


BACKUP DATABASE test TO DISK ='e:\backup\test.trn'
BACKUP LOG test WITH truncate_only

Now run the log backup.

BACKUP LOG test TO DISK ='e:\backup\test.trn'

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

Check the error log by using below command

SP_READERRORLOG 0,1,'BACKUP LOG WITH TRUNCATE_ONLY'

BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.
The simple recovery model should be used to automatically truncate the transaction log.

Note:  When you use this command you can’t do regular log backup and point in time recovery too. This command will truncate the log files (VLF) and break the log chain. To maintain the log chain again you have to run full or differential backup.

Microsoft has removed this command in SQL server 2008 onwards.

What to do in SQL server 2000 and 2005?

You can use the ‘3031’ trace flag to stop the user to perform the deprecated option.

DBCC TRACEON (3031,1)

Let's check,

BACKUP LOG test WITH TRUNCATE_ONLY

Ha...SQL server throwing the following error


Msg 8309, Level 16, State 1, Line 1
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.

Shrinking the transaction log:

      

OK, How to avoid the shrinking?

  •  Frequently run the log backup and maintain the log size as small as possible.
  •  Alter the database and point the log file to another drive/Add new drive.
  •  If your company ready to accept the data loss (minimum like 2 to 3 hours) then put the database in simple recovery mode and run the differentially backup every 2 to 3 hours.
You can manually shrink the log file by using the below command.

Important note: Shrinking the data/log file will increase the fragmentation. Try to truncate the log file instead of shrinking.
SELECT name FROM SYS.MASTER_FILES WHERE DB_NAME(database_id)='test'
Test_log is logical name of “test db”.

DBCC SHRINKFILE(Test_Log)

Sometimes your shrink command will not shrink the database. When there is an active portion of log is available OR the transaction is not closed (not yet committed).

See the Shrink result :
DbId
FileId
CurrentSize
MinimumSize
UsedPages
EstimatedPages
5
2
305760
70
305760
64


Alright the shrinking doesn't reduce the file size.


Let's check the open transaction by using DBCC OPENTRAN.You can also check the log size and status using DBCC loginfo() and sys.master_files commands.


DBCC OPENTRAN()

Transaction information for database 'test'.

Oldest active transaction:
    SPID (server process ID): 54
    UID (user ID) : -1
    Name          : INSERT
    LSN           : (197:11774:2)
    Start time    : Mar  7 2011  4:18:44:240PM
    SID           : 0x01

DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Sys.master_files’ will return the size of each file.
SELECT size/128,type_desc FROM SYS.MASTER_FILES WHERE DB_NAME(database_id)='test'

'DBCC LOGINFO()' will return the status of the log file.

If the status is 2 VLF in use (Active)
If the status is 0 VLF not in use (Inactive)





Look the BOL for more about DBCC loginfo().
Then, How to shrink the  log file?


Ok...Wait until the open transaction finished.


Check the log_reuse_wait_descsys.databases by running below command and see what value it returns. It will help to minimize the database running out of space.



SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases name='test'

Look the BOL for more about  log_reuse_wait_desc.
 
ALTER DATABASE [Test] SET RECOVERY SIMPLE WITH rollback immediate

DBCC SHRINKFILE(Test_Log)

ALTER DATABASE [Test] SET RECOVERY FULL WITH rollback immediate
Changing the recovery model and truncate_only both options are works same.

Note:  When you use this command you can’t do regular log backup and point in time recovery too. This command will truncate the log files (VLF) and break the log chain. To maintain the log chain again you have to run full or differential backup.


Conclusion
Shrinking the log file will reduce the physical log file size and truncating the log file will change one or more VLF files from active to inactive and reuse it again.

1 comment: