Freigeben über

Shrink a Transaction log file

  • Artikel

I came across a very important issue with one of my customers they had a SharePoint site with a transaction log file that grew aggressively and filled up the partition that it was stored on. So they contacted me to assist them in shrinking the transaction log database after researching I didn’t find anyone that actually explains how this can be accomplished in a comprehensive manner without breaking the chain. So I decided to provide the steps myself and after that I provide an internal look in part to of this series.

Please Run the Following Script under the
SharePoint Configuration database in management studio

 

DECLARE
   @DBName VARCHAR(255)
SET
   @DBName =  'AdventureWorks2008R2'              ---Replace with Database Name
DECLARE
   @TransactionLogName VARCHAR(255)
SET
   @TransactionLogName =  'AdventureWorks2008R2_Log'  ---Replace with Transaction log file Name
-------------------STEPS TO GET TRANSACTION LOG FILE NAME-----------------------------
--RIGHT CLICK ON YOUR DATABASE-->Properties--> From the left panel choose files-->Under File Type search for log-->select Logical Name from that row
--------------------------------------------------------------------------------------
---------------------------------------------------------------
--My good friend/Mentor Rafid suggested another way to get the Log File Name (Rafid’s Blog: http://blogs.msdn.com/b/rafidl/)
-- select name from sys.database_files
-- where type_desc='log'
-- should also work
---------------------------------------------------------------------

DECLARE @SetMode NVARCHAR(MAX)
SET @SetMode  = 'ALTER DATABASE {DataBaseName} set recovery {RecoveryMode}'

DECLARE @ShrinkLog NVARCHAR(MAX)
SET @ShrinkLog = 'DBCC SHRINKFILE ({TransactionLogName},1)'

DECLARE @SCRIPT NVARCHAR(MAX)
SET @SCRIPT = REPLACE(@SetMode, '{DataBaseName}', @DBName)
SET @SCRIPT = REPLACE (@SCRIPT, '{RecoveryMode}','Simple')
EXECUTE (@SCRIPT)

SET @SCRIPT = REPLACE(@ShrinkLog, '{TransactionLogName}', @TransactionLogName)
EXECUTE (@SCRIPT)

SET @SCRIPT = REPLACE(@SetMode, '{DataBaseName}', @DBName)
SET @SCRIPT = REPLACE (@SCRIPT, '{RecoveryMode}','Full')
EXECUTE (@SCRIPT)

Choose Management from management studio and then right click on Maintenance Plans


  

Select Maintenance plan wizard

 

In the SQL Server Maintenance Plan Wizard click on Next

 

After adding a name and a description click on  Change

 

In the Job Schedule properties Make sure that the frequency is set to daily

Daily Frequency is set to 1 hour

And Duration is set to no end date

 

In the Select Maintenance Task select Back up Database (Transaction Log)

 

In the Select Maintenance Task Order Click on Next

 

In the Define Database Backup (Transaction Log) Tasks

Select the Database you want to shrink

  

In Select Reporting Action Click on Next

  

In Complete Wizard Click on Finish

The way this works is a set of prerequisites that are required to run the SHRINK Command

The problem is the structure of the Log file as illustrated in the link below

  
  
  

Source: http://msdn.microsoft.com/en-us/library/ms179355(SQL.105).aspx

Log files store a step by step on each transaction that occurs on your MDF or NDF files since the last time you have taken a full backup of the database

So every time you have taken a backup the transactions are deleted from the log file (they are not actually deleted they are just marked as dirty) this process is called truncating the log file

Transaction log file are divided into Virtual Log File if a virtual Log File does contain transactions marked as active then this VLF cannot be truncated and hence to overcome this issue we change the
recovery mode from Full to Simple this cases a checkpoint to run every min and hence continuously marks VLF as in active. The last part of the script is to make sure that the Transaction logs will be continuously marked as inactive through backing up and this is the schedule wizard that we followed

Comments

  • Anonymous
    August 14, 2012
    nice..it will be great if the SQL script are formatted with T SQL syntax coloring.

  • Anonymous
    August 14, 2012
    The comment has been removed

  • Anonymous
    August 14, 2012
    Try the prettifier for T-SQL syntax coloring: extras.sqlservercentral.com/.../prettifier.aspx

  • Anonymous
    August 14, 2012
    Dirk i love it :) what do you think about it guys now?

  • Anonymous
    August 14, 2012
    Ok, next try to write another comment since the other one got lost. Only a few thoughts regarding the log shrink: As we all know, shrinking the log isn't best practice at all and should be avoided. You should try to find out what is causing the log to grow. As a hint: www.sqlservercentral.com/.../72488 You should also check how often log backups are taken. We may need a different schedule. The Log chain gets broken due to the switch of the recovery model, not due to the shrink command. My thought here is: is it really necessary to change the recovery model? I'd try the following: Just run a log backup first. Then shrink the file but do not set the size too low). We now have unused VLFs inside out T-LOG which can be reused by SQL Server. Run another log backup and shrink again. We should be near the expected size of the log file. You should find a size for the log file which fit the needs of the system. Shrinking the file to the minimum won't help that much since SQL server will expand the file again once the space is needed (if autogrow is enabled). Also, check the number of VLFs with DBCC LOGINFO.  I can't remember exactly where I've found the info, but a too large number of VLFs also have a performance impact. Regards Dirk

  • Anonymous
    August 14, 2012
    Nice post :) but a boxes and straight arrows will be better than mouse drawings :)

  • Anonymous
    August 14, 2012
    What do you mean by "without breaking the chain"? If your script changes the recovery model to simple and back to full, doesn't this break the chain? (Ref msdn.microsoft.com/.../ms190440(v=sql.105).aspx)

  • Anonymous
    August 14, 2012
    Nice post :) give us more in related topics

  • Anonymous
    August 14, 2012
    @Dirk yes you are right about not being best practice that is perfectly correct however in some cases this is your ownly option @David and @Dirk yes it will break the chain i dont remember what was in my mind when i wrote that title just by changing it to simple will cause a checkpoint to run every min @Dirk yes you are right usually a large number of VLF will cause a performance impact specially in the write pages and TLF are considered heavy write files

  • Anonymous
    August 15, 2012
    This is horrible advice and the title of the post alone is misleading.  You ABSOLUTELY break the log chain by changing from FULL to SIMPLE like your Dynamic SQL does.  The only way to shrink the log file without breaking the chain is to BACKUP the log file.  This is yet another disservice to the Accidental DBA's out there that don't know what they are doing with SQL Server, and would expect that something posted on the MSDN Blogs is authoritative enough to follow.  Hopefully they will read the comments so that they understand the actual impact of what they are doing here because these steps will eliminate their ability to restore to a point in time after the recovery model change unless they immediately follow the steps with a new FULL backup or a DIFFERENTIAL backup to restart the recovery chain for the database. Another point is that changing to SIMPLE does not cause CHECKPOINT to occur every minute.  CHECKPOINT will occur as often as is necessary to attempt to maintain a 1 minute recovery interval, that could be as fast as once every few seconds under heavy transactional load or as long as multiple hours if there aren't any changes occuring to the database.

  • Anonymous
    August 16, 2012
    As Jon says, this is a terrible post with extremely bad guidance. The way to properly handle a runaway transaction log is to take a transaction log backup, then shrink the transaction log. You may have take a second transaction log backup and shrink the log file again in order to actually get the log to shrink. After this, you need manually grow the transaction log to an appropriate size.  This appropriate size depends on your transaction log generation rate and how frequently you take transaction log backups. Changing the recovery model to Simple absolutely does does break the recovery chain for the database!