Transaction Log does not truncate and shrink using a SQL Server 2005 Maintenance Plan

Transaction Log does not truncate and shrink using a SQL Server 2005 Maintenance Plan

• tarania

  Hello All,

  I am new to this forum. I have faced a similar situation.

  During this we used to perform a manual truncation of the transaction log.

  It is a technique published in the ExpertsExchange.com.

  http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20651271.html

  In simple steps, i will list down the points

  1.) Stop the Database Server and Start it before it detects all the databases,

  Detach the database which you need to truncate the transaction log by running the below statement in the query window

  sp_detach_db [DatabaseName]

  2.) Once you detach the database, now Open the folder where you find the transaction log file of the database server.

  In this case , it is "\Microsoft SQL Server1\MSSQL\Data\"

  Rename the transaction log file to a different name.

  3.) Then attach the database again with the below statement ( attach DB using the data file)

  sp_attach_db @dbname = N'DatabaseName',

  @filename1 = N'D:\Microsoft SQL Server1\MSSQL\Data\DatabaseName.MDF'

  Note : sp_attach_db requires two parameters namely @filename1=datafile and @filename2=logfile.

  but here do not mention the filename2 i.e dont mention log file name.

  This statement creates a new transaction log file to the database. The Renamed Transaction can be moved to different folder or your can delete it.

  NOTE :

  NORMALLY THIS IS NOT ADVICEABLE TO DEVELOPER OR NEW DBA's as it requires MORE CARE WHEN DOING THIS PROCESS EXECUTION. PLEASE DO THIS CAREFULLY.

  BUT THIS PROCESS SURELY HELP THE MAINTENANCE PEOPLE TO GET RID OF THE BACKUP PROBLEMS, STORAGE ISSUES.

  Hope this helps a little to you all.

  REGARDS,

  DHINESH KUMAR

  
  
  

• JongAm Park

  Doing a full backup every night might not be enough. Our database (full-recovery) constantly (24*7*266) writes about 100K per hour. So we do a full backup weekly, a differential backup nightly and a transaction backup every 15min in a rotating biweekly backup device scheme.

  This scheme keeps our transaction logs to a minimum. The transaction log backups are typical small as are the differential backups.

  The only problem are cleanups (indexes, historic data etc.) So this is done during scheduled maintenance windows.

  
  

• shi11

  Check your Recovery Model for the Database, if you chose Full the log will not be truncated as part of the maintenance plan. If you choose the Simple Recovery Model than the log can be truncated as part of the maintenance plan. Use the SQL 2005 help and search on Recovery Models for further detail....
  

• ArtRI

  I experience the same thing as davidg12. We also do a full backup every night (including of the transaction logs!!) - using the Full recovery model. However, no truncation is being done.
  Please post the solution if you find one...!
  
  
  

• Hibri Marzook

  Thanks Deepak, your response and the postings of the other forum were very useful.

  Here are my takeaways...

  1) Shrink the TLOG once

  2) Moving forward, backup the TLOG frequently

  3) TLOG size growth will be limited to transactions processed in between TLOG backups

  Make sense

  
  

• ny3ranger

  We are doing a full backup every night. Do you still need to do a transaction log backup
  

• MikeB2007

  I tried to backup the transaction log to see if its size would shrink, but it didn't. I double checked and the 'Truncate...' option was set. Is there another setting which affects this

  Also, I'm a little confused about why the transaction log is useful at all after a full backup is taken. It stands to reason that any restore plan which would incorporate the transaction log would begin with the database restore and a roll forward of the transaction log, if it was available. As such, it is currently my plan to tuncate the log file each night after the backup is complete. (or should it be before )

  Any advice would be appreciated, or please corect me if I misunderstand.

  
  

• xuzhong

  Based on your advice, I modified the maintenance plan to include a shrink before backup, a complete database backup, a transaction log backup and then another shrink after backup. This seems to have worked. The transaction logs have shrunk considerably. Thank you.

  David

  
  

• Dortoh

  You need to take the T-log backup and then shrink the log file,
  

  1. Backup log database name to disk='Path\filename.trn'
  2. DBCC shrinkfile('log file name', 500)

  You can get more information about the commands I mentioned from BOL
  
  Also check this thread where it discussed about t-log growth and how to minimize the same,
  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=2728787&SiteID=1
  If you need anything else pls feel free to ask the same !
  
  - Deepak
  
  
  

• lohit

  As a rough guideline your database's transaction log size should be about 25% of the size of the combined total of your database's data files. If the log is significantly larger, and is causing you problems, then you can always backup the log more regularly to keep its size down if you wish.

  The log is not truncated when you perform either a full or differential backup. To see this for yourself run PerfMon and add the following counter, choosing one of your databases to monitor:

  SQL Server - Log -> Log Space Used (%)

  Watch the counter and perform a full database backup - you should see no drop in the counter's value. Perform a log backup and the counter's value should drop.

  Confusingly in SQL Server 2005 if you create a new database then perform a full backup then the log appears to be truncated for the first backup only, however if you take a log backup before the first full backup then subsequent full backups do not truncate the log.

  Shrinking the log file every day is not necessarily a good thing as the constant growth and shrinkage of the file can cause fragmentation on disk. It is generally better to set the log (or shrink it) to a realistic size and then perform more frequent log backups to manage the space usage within the log file. Backing-up the log more frequently also means that you will lose less data in the event of a disaster.

  The log file deletion process described above (by Dhinesh) should only be carried out as a last resort and should not be incorporated into a regular maintenance schedule.

  Chris

  
  
  

• fatcat1111

  I was under the impression that the transaction log can only be shrunk after a transaction log backup. Currently for Maintenance plans I have transaction log backups running durring the week and then a full backup on weekends that first shrinks the database and then backs it up. It seems to be shrinking the transaction logs correctly. This may depend upon the recovery model used by the database as well. We use full.
  
  

• Erin G

  This seemed to be a common occurance. However, my solution was a little different and a throw back. I ran the transaction backup then run a dbcc shrinkfile task. That worked. However, I will try the shrinkdatabase task and see how that works.

  Akinja

  
  

• Jigar_Patel

  The only problem with the autoshrink command is that, as the name suggests, you have no control over when the shrinking will occur, and you can bet that SQL Server will decide to shrink the log file during a busy period - for this reason it is generally recognised as a best practice to set autoshrink off. Again if the log file is regularly shrunk then forced to grow again then fragmentation is likely, which will hurt performance as log files benefit hugely from contiguous disk space.

  Agreed that disk space is cheap these days, although it isn't easy to add extra or higher-capacity drives to existing RAID arrays.

  Another point to consider is that the larger the files then the longer the database will take to restore, which obviously may or may not be a problem.

  Everyone's environments and requirements are different but I'm of the opinion that regular scheduled log backups should be taken and that transactions should be kept as short as possible, both of which will help keep the log file to a manageable size with minimal manual intervention.

  Chris

  
  
  

• Mattdrinks

  There is a database option called autoshrink. This will reduce the log files (I believe) automatically.

  But why the hassle HD space is cheap these days. What we do is let the database run for a few days having the Log file auto resize it self, add a few Gigs for kicks and than switch off autoresizing. You might want to run a defragmentation after that to have all the database files (mdf, ldf, and ndf) nicely contiguous. Letting the log file "eat up" all free space might run you in serious problems you're only come aware of when it is too late.

  Jo

  
  
• Transaction Log does not truncate and shrink using a SQL Server 2005 Maintenance Plan