•  
  • DBA
  • Difference between truncating and shrinking the transaction log file

Difference between truncating and shrinking the transaction log file

 

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)



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

Muthukkumaran kaliyamoorthy

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

More Posts - Website

Share this
21 Comments - Leave a comment
  1. Anonymous says:

    Hi,
    very well written.Excellent!

    FYI,
    Your checkpoint url doesn't work man.

  2. Muthukkumaran Kaliyamoorthy says:

    Now both URLs are fixed(recovery & checkpoint)

    Thanks.People are reading 🙂

    (May i know your name)

  3. Muthukkumaran Kaliyamoorthy says:

    I always forget to close the tag /a

  4. Anonymous says:

    Muthu,
    Excellent job.

    Ram

  5. Muthukkumaran Kaliyamoorthy says:

    @ram Thanks.

  6. Anonymous says:

    TRUNCATE_ONLY & NO_LOG has been removed.
    I am going use the trace flag 3031

    Great post muthukkumaran!Keep up the good work.


    Richard

  7. Muthukkumaran Kaliyamoorthy says:

    Richard,
    Thanks.Glad you liked it.

  8. Anonymous says:

    Hi muthukkumaran,
    Its great post.

    by
    Meena rao

  9. […] simple and back to full and shrinking the log file daily.The DBA send an email to his manager. The shrinking is not good practices, […]

  10. […] Shrinking the files is not recommended but, anyway to prevent the database down we can shrink the log files at least some crucial situation. Try to avoid shrinking database files as much as possible. See my previous post How to avoid the shrinking. […]

  11. Melfi says:

    I just required some good info as well as was searching upon search engines for it. We visited each site which arrived on first site and didnt obtained any kind of relevant end result i quickly considered to take a look at the second 1 and also got your site. that is what I desired!

  12. Barmes says:

    That is actually awesome notion dude.iam actually proud of you .

  13. Roshan says:

    Hey,

    Great article Muthu!

    Keep up the good work

  14. Mario says:

    i´m new using sql, thanks a lot for the explanation.

    I would like you to please help me telling me if there is no problem applying a job for shrink a log file every certain time?

  15. rammy says:

    Thanks for the article..
    all at one place.Its like a hand notes..

    look forward more articles like this in the near future..

  16. craig higgins says:

    Hi Muthu

    I have been told that even in simple recovery mode I may still need to clear transaction logs outside of the DB backup. This point seems to stack up as I have a DB which had issues when the transaction log backup schedule failed to run and the transaction log itself was full (viewed error in event logs).

    The transaction log backup schedule I refer to was set up with a snap drive manager due to the server being on VMware. The problem I have with this is that it can only be scheduled at given time intervals using the snap manager. Affectively we would be putting the server to more work than needs be by scheduling frequent Tlog backups, when I know sql server can deal with it as and when required.

    I have just gone to set up a Tlog backup within the management studio itself, using the maintenance plan wizard, and when I go to select the databases to backup it displays a warning stating ‘Databases with simple recovery will be excluded’.

    I am now a bit confused. Should the Tlog actually ever fill if in simple recovery mode or is there something else I am missing?

    Any help would be greatly appreciated

    • Hi craig,

      Firstly if you have much transaction/continuous transaction the log file will grow and fill as much as.

      Second,In simple recovery model you can not take Tlog backup. You can shrink the log file if needed.

  17. John Walker says:

    You can also take the help of this link to know the difference between shrinking and truncate transaction log file. Must read from here: http://sqltechtips.blogspot.com/2016/07/shrinking-and-truncating-transaction-log.html

Leave a comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">


Welcome , today is Sunday, June 25, 2017