January 22, 2010 at 4:09 am
We run full backups around 1am every night integrity checks around 11pm (check, rebuild, reorganise, shrink, update). The last transaction log backup of the day, about 9pm, is very small, but the first one the following morning (8am) is huge. Given there is unlikely to be much, if any, activity between 9pm and 8am, can anyone tell me why this is happening?
Many thanks,
Jules
January 22, 2010 at 4:14 am
Because of the index rebuild. It touches every row of every table, and all of that is logged
Two questions on your maint plan.
Why are you rebuilding and reorganising? Rebuild does all that reorganise does, and more.
Why on earth are you shrinking your database? Doing it after the index rebuild just refragments (badly) all those indexes that you rebuild. If you insist on shrinking, either don't bother rebuilding the indexes at all (as the shrink will likely result in fragmentation higher than what the indexes had before the rebuild) or, if you absolutely insist on shrinking, rebuild after.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
and http://brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
and all the posts that those two link to.
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
January 22, 2010 at 6:51 am
Thanks Gail I knew you'd come through!
The integrity checks are set up as per instruction given by someone else; thanks for your suggestions and I'll review what we actually do (and don't) need to do, and in what order.
Cheers,
Jules
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply