October 10, 2011 at 12:39 pm
We are running SQL Server 2005 (SP3) on Windows 2003 Server. I have a weekly DB Maintenance Job that runs every Sunday morning at 6 A.M. I noticed when I get to work on Monday morning the Transaction Log has grown by 10 GB from Sunday Morning to Monday morning. The weekly DB Maintenance Job contains the following tasks:
--Before Backup
BACKUP DATABASE TestDB TO
DISK= \\testserver\backups\TestDB_Before.bak
GO
--Rebuild Indexes
DBCC DBREINDEX('TableA','',70)
DBCC DBREINDEX('TableB','',70)
DBCC DBREINDEX('TableC','',70)
GO
--Backup with truncate only: (Removes Inactive or Committed Transactions)
BACKUP LOG TestDB WITH TRUNCATE_ONLY;
GO
--Shrink the log file to 50 MB:
DBCC SHRINKFILE (TestDB_log, 50);
GO
--After Backup
BACKUP DATABASE TestDB TO
DISK= \\testserver\backups\TestDB_After.bak
GO
My questions are;
1) What is causing the Transaction Log to grow? Is it the DBCC ShrinkFile Task? Should I put this task before the "BACKUP LOG TestDB WITH TRUNCATE_ONLY" Task?
2) I have a User DB Maintenance Plan which runs every day at 4 A.M. So, it runs at 4 A.M. on Sunday before the Weekly Maintenance Job runs at 6 A.M. The User DB Maintenance Job also run at 4 A.M. on Monday morning before I arrive to work. This User DB Maintenance Plan executes a Check Database Integrity Task, Shrink Database Task and Update Statistics Task. Do you think one of these Tasks are the cause of the Transaction Log Growth?
Does these tasks (Check Database Integrity Task, Shrink Database Task and Update Statistics Task) affect the Transaction Log?
Whatever the cause, it is occurring between Sunday and Monday mornings. We do not have a heavy user load during this time.
Thanks, Kevin
October 10, 2011 at 12:45 pm
Ok kevin, I will probably be the first of many......
its the reindex that is causing the log growth. Thats normal behaviour.
remove the truncate log and shrink, totally pointless and in fact dangerous.
If your database is in any thing other than simple recovery mode you are breaking your restore chain.
Your log grows to 10GB because it needs to to. You then shrink it so it has to grow again next week. thats a waste of resources. Leave your log at 10GB.
---------------------------------------------------------------------
October 13, 2011 at 7:50 am
Thanks SSCarpal.
I understand the DBCC REINDEX is causing the log growth but I thought the "BACKUP LOG TestDB WITH TRUNCATE_ONLY;" and "DBCC SHRINKFILE (TestDB_log, 50);" commands would reduce the size of the log after the DBCC REINDEX. If these two commands are reducing the size of the log then it may be something in my Maintenance Plan (which runs the next morning after the weekly job) which is causing the log to grow again.
This is why I asked, "This User DB Maintenance Plan executes a Check Database Integrity Task, Shrink Database Task and Update Statistics Task. Do you think one of these Tasks are the cause of the Transaction Log Growth?" Note the Weekly Maintenance Job runs Sunday at 6 A.M. and the Maintenance Plan runs Monday at 4 A.M. and something in one of these jobs is increasing the size of the log without reducing the size of it. I notice this when I check my log sizes on Monday morning. The user activity is not causing the log to grow.
Thanks, Kevin
October 13, 2011 at 8:10 am
Yes, the index rebuild is causing your log to grow. It will.
Truncate and Shrink is a very, very bad thing to be doing regularly to the log, it's breaking your point-in-time recovery and the re-growth will be slowing down the system, the backups, restores, recovery and a few other things.
Please read through this: http://www.sqlservercentral.com/articles/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2011 at 8:10 am
what recovery mode is your database in?
---------------------------------------------------------------------
October 13, 2011 at 8:11 am
GilaMonster (10/13/2011)
Yes, the index rebuild is causing your log to grow. It will.Truncate and Shrink is a very, very bad thing to be doing regularly to the log, it's breaking your point-in-time recovery and the re-growth will be slowing down the system, the backups, restores, recovery and a few other things.
Please read through this: http://www.sqlservercentral.com/articles/64582/
'bout time someone else weighed in, where ya been ? 🙂
---------------------------------------------------------------------
October 13, 2011 at 8:16 am
george sibbald (10/13/2011)
GilaMonster (10/13/2011)
Yes, the index rebuild is causing your log to grow. It will.Truncate and Shrink is a very, very bad thing to be doing regularly to the log, it's breaking your point-in-time recovery and the re-growth will be slowing down the system, the backups, restores, recovery and a few other things.
Please read through this: http://www.sqlservercentral.com/articles/64582/
'bout time someone else weighed in, where ya been ? 🙂
In Seattle, at SQL Pass 2011.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2011 at 1:21 pm
Thanks for the article and the suggestions to remove the truncate log and shrink log commands. I have more information which should make the DB Maintenance and Backup Tasks simplier.
Per the vendor, the DB is in Full Recovery Mode. (However, we do not need the transaction log backups for our environment.) Again, this is per vendor. We take a full database backup every night. It was just the Weekly Maintenance Tasks that were causing the log to grow and I was trying to figure out what specific task was causing this to happen. As was pointed out, the Index Rebuild is causing the log to grow, however I was thinking the Truncate and Shrink should have reduced the size of the log. This is were I am confused. Then, I thought well maybe something in the Maintenance Plan (which executes after the Weekly Maintenance Tasks, after the Truncate and Shrink Commands) was causing the log to grow. This is why I asked, does the Check Database Integrity Task, Shrink Database Task and Update Statistics Task affect the Transaction Log?
To improve this process and keep it simple, I will remove the Truncate and Shrink Commands from the Weekly Maintenance Job. It appears that from reading the article (http://www.sqlservercentral.com/articles/64582/), I can take a backup of the transaction log just to truncate it (remove inactive entries). I am going to change our nightly backup and weekly maintenance jobs to:
Nightly Job
Take Full Database Backup
Weekly Job
--Before Backup
BACKUP DATABASE TestDB TO
DISK= \\testserver\backups\TestDB_Before.bak
WITH INIT;
GO
--Rebuild Indexes
DBCC DBREINDEX('TableA','',70)
DBCC DBREINDEX('TableB','',70)
DBCC DBREINDEX('TableC','',70)
GO
--After Backup
BACKUP DATABASE TestDB TO
DISK= \\testserver\backups\TestDB_After.bak
WITH INIT;
GO
Per the article, taking a backup of the transaction log will tuncate the log. This is why I am thinking I need to back up the transaction log (to truncate it) and shrink it. I feel like (overtime) it may grow and grow and grow if I do not truncate it and shrink it. Remember, the vendor does not want me to use Simple Recovery Mode. I hope I am not making this too complicated.
Thanks, Kevin
October 13, 2011 at 1:26 pm
If you don't need the t-logs why are you running using the full recovery model? If you aren't taking transaction log backups, the t-log will continue to grow until you truncate it or switch the recovery model to simple.
At this point, switch the recovery model to simple. Then you don't need to truncate the t-log as part of the maintenance process.
October 13, 2011 at 2:01 pm
As Lynn has said if you dont want log backups, put the database in simple mode, it doesn't matter what the vendor says, they rarely know very much about database maintenance.
If you want to keep the database in full recovery mode you must also take log backups, at least daily. This is the only thing that will stop the log growing.
do not regularly shrink your log file.
---------------------------------------------------------------------
October 14, 2011 at 8:26 am
What I do for my Rebuild Index maintenance tasks is that I perform the following steps:
1. Backup the transaction log
2. Change the recovery model to Bulk-logged
3. Execute the index Rebuild tasks
4. Change the recovery model back to Full
5. Perform another backup of the transaction log.
Rebuilding indexes on SQL Server are minimally logged operations when the database is in the Bulk-Logged or Simple recovery models. This will significantly reduce the amount of growth seen on the transaction log.
Keep in mind however that with Bulk-Logged transaction log backups, you cannot do a point-in-time restore from a transaction log backup that has minimally logged operations in it. This is why I perform a transaction-log backup before and after my minimally logged operations, to minimize that loss of restore functionality.
Also, you do NOT want to have your Transaction Log and Database files constantly growing and shrinking. If this happens, then it is highly likely that your Database and T-Log files will become fragmented, which will impact database performance. Increase the database size, increase the Transaction Log size, and leave them with room to spare. Then do a disk-defrag during maintenance.
One last piece of advice: The DBCC REINDEX command is going to be removed in future versions of SQL Server. It still exists in Denali (SQL 2012), but I think that will be the last version to support it. Microsoft recommends using the ALTER INDEX command instead, and I would follow their advice. BOL Source
Below is a sample of the Syntax.
--Command below is equivalent to DBCC DBREINDEX('TableA','',70)
ALTER INDEX ALL ON TableA REBUILD WITH (FILLFACTOR = 70)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply