February 6, 2012 at 6:18 pm
Sorry, I am rather junior in my SQL Server knowledge.
I have a maintenance stored procedure which calls a separate procedure to reindex tables. The reindex procedure uses two threshold values to determine whether to reorganize the indexes or rebuild them.
This all runs within a transaction, and for certain clients the transaction log grows from a few megabytes to several gigabytes after the scripts run.
The recovery model on the database is set to Simple.
Questions:
1) Should I be running the reindexing stored procedure within a transaction?
2) What is the best way to remedy this situation?
2) If I cannot prevent the transaction log growth, is it safe to shrink the file at the end of the maintenance script?
Thank you.
February 7, 2012 at 1:57 am
cheetahkatsu (2/6/2012)
1) Should I be running the reindexing stored procedure within a transaction?
Probably not because that prevents the log from been marked reusable between the individual index rebuilds.
2) What is the best way to remedy this situation?
Well you're in simple recovery, so you already have minimally logged operations. Other than that, rebuild just what needs rebuilding, not everything, and maybe run a checkpoint between each one.
2) If I cannot prevent the transaction log growth, is it safe to shrink the file at the end of the maintenance script?
No, because it's just going to grow again.
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
February 8, 2012 at 7:12 am
1) use ola.hallengren.com's maintenance suite. free, documented and awesome
2) track what gets fragmented and adjust fill factors appropriately to avoid too much fragmentation between index maintenance interval
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply