DBA

Difference between truncating and shrinking the transaction log file

March 7, 2011
Advertisements

 

Difference between truncating and shrinking the transaction log file

I have noticed in many forum the frequently asked question is “Truncating and shrinking the log file both are same”

The answer is obviously NO. These both are totally different. Before going into discussion, let’s read the transaction log file architecture.

Truncating the transaction log:

Truncating a log file will change one or more VLF file’s status from active to inactive and mark the space for reuse. SQL server will change the VLF from active to inactive and reuses it automatically (Just like a circular).

 

When does it truncate and reuse it automatically?

It depends upon your setting: recovery model and backup type.

  • SQL server runs a CHECKPOINT  an every time truncate/mark the inactive portion of the VLF files and reuse it automatically, When a database is in simple recovery model.
  • SQL server runs 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 DATABASEtest 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 TODISK='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 1BACKUP 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:

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. 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 puts the database in simple recovery mode and run the differential backup every 2 to 3 hours. It will maintain the log file as much as but not for the continuous huge transaction database.

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. This means running the log backup more frequently.

 

SELECT name FROM SYS.MASTER_FILES WHERE DB_NAME(database_id)='test'

 

Test_log is the 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).
Here is the code to find you can able to shrink your database files or not. It will give you a clear picture about your database files used and free space.
use Databasename

SELECT DB_NAME() AS DBNAME,
NAME AS FILENAME,
SIZE/128.0 AS CURRENTSIZE_MB,
SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0)  AS USEDSPACE_MB,
SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0 AS FREESPACEMB,
PHYSICAL_NAME,DATABASEPROPERTYEX (DB_NAME(),'RECOVERY') AS RECOVERY_MODEL,TYPE_DESC,
CASE WHEN IS_PERCENT_GROWTH = 0
THEN LTRIM(STR(GROWTH * 8.0 / 1024,10,1)) + ' MB, '
ELSE 'BY ' + CAST(GROWTH AS VARCHAR) + ' PERCENT, 'END +
CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'
ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'
END AS AUTO_GROW
FROM SYS.MASTER_FILES
WHERE DATABASE_ID = DB_ID()
 
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 1) open transaction by using DBCC OPENTRAN.2) Check the virtual log file status using DBCC loginfo().

 

Step:1

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'

Step:2

 
‘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)

VLF_status

 
 

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. If you want you can automate the script to your Non Prod servers. It will help to minimize the database running out of space.

 

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE 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

 

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 marked the space for reuse.

Thanks to Paul Randal and Kalen Delaney.

My special thanks go to GilaMonster (Gail Shaw).

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

22 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

50 + = 56