June 3, 2008 at 1:53 pm
One of my databases has a data file size around 30 GB. Used for OLTP. The transaction log is usually 200 - 300 MB. We perform daily full backups of the db and transaction log with 3 transaction log backups also occurring during the day. The database is set to automatically grow the data file by 20% and the transaction log by 10%.
Once a month a scheduled maintenance plan job does a Reorganize Index on all user databases on the server. For this database, and only this database, the transaction log grows to be larger than the database. For example, after the last run it ended up at almost 50GB! There is very little activity on the database between the last backup and the time the job starts the re-org.
This is the only database that does anything like this. Should the Reorganize be run more often? What should I look for to try to identify the problem?
Running SQL 2005 Standard Edition on 64-bit Windows Enterprise server . Clustered, failover, active/standby.
Thanks.
June 3, 2008 at 2:01 pm
Re-org operation is same as DBCC DBREINDEX in SQL 2000. It needs space of approximately 1.2-1.5 times of database size for successful execution. To reduce the transaction log file usage try performing the re-org opeation after switiching database recovery model to Bulk-logged.
Change recovery model back to original one once re=org completes its execution successfully.
Manu
June 5, 2008 at 10:08 am
THanks for the response. The current job is a scheduled maintenance plan built via the wizard with just the 'Reorganize Index' task selected along with 'all user databases'. How would I add the modification to bulk logging before the reorg and then back to full logging when done? Sounds like I would have to redo the current task and make it a script that goes through each user db, sets it to bulk logging, does the reorg (via dbcc?), then sets it back to full logging, making note of any and all errors, and of course with all of the activity logging that the maintenance plan currently gives me (e.g., logging errors to Windows Application Event Log).
That sounds quite involved, but I really do need to get this resolved. What would you recommend?
June 5, 2008 at 10:11 am
I also should have mentioned that the database is on-line during this and can be taken off line. How much risk does the bulk-log change pose?
(It will probably be about a year before the app this supports is modified so I can take the database off line more often for maintenance).
June 5, 2008 at 11:27 am
No real risk, but I think you might need to grab a full backup when you change it back.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 5, 2008 at 11:42 am
As far as I can recall - index reorgs are always fully logged, so switching your recovery model will have no impact on what is logged during a reorg. On the other hand - index REBUILD's can be bulk-logged.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 6, 2008 at 5:02 pm
We came across this about 3-4 years ago.
A weekly SQL server agent job was reindexing a production database with a 1GB mdb and now and then the log would shoot to 20GB!
Afterwards we would sometimes hang Enterprise manage - activity manager.
A colleague up north came across a convoluted method to allegedly fix it.
We used a sledgehammer instead.
Backup database.
backup log with truncate only -- resets pointer
backup database again
sql command to shrink log.
backup full database again.
User has not complained since and performance on the old clunker is ok.
As I said - a bit of a sledgehammer.
Hope that helps.
June 7, 2008 at 12:10 am
I agree with Matt.
In one of my bulk-logged DB I do see a very big transaction log backup everytime a reindex has been perfromed. At the time of reindexing my DB has absolutely no logging of data. So its just reindexing that's being backed up during Tlog Backup.
All other times its a usual smaller Tlog Backup.
Ankit
June 7, 2008 at 8:05 am
One thing that you could do is to only rebuild or reorganize indexes that are fragmented. This means that you reduce the amount of log records that is generated and the time that is needed considerable.
In SQL Server 2005 you could use sys.dm_db_index_physical_stats to check the index fragmentation.
http://msdn.microsoft.com/en-us/library/ms188917.aspx
http://msdn.microsoft.com/en-us/library/ms189858.aspx
I have a stored procedure that could help you with this.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
You could also use the script in Books Online.
http://msdn.microsoft.com/en-us/library/ms188917.aspx
Ola Hallengren
September 16, 2009 at 2:02 pm
It might be that you can reduce the number of transactions occurring when your code runs.
Some simple stored procedure coding slip ups can cause much more log activity than you expect...
See this blog entry at:
corrected url!:-)
September 18, 2009 at 5:53 am
Hi Andy,
The link given is not opening.
Can u please check and reconfirm the link to all of us.
Thanks
Ankit Mathur
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply