0

I have a maintenance plan that does a full backup and a 'check database integrity' every night. I backup transaction logs every 15 minutes. The transaction log backup size spikes during index reorganize - exponentially bigger. I used to reorganize indexes and statistics every night - I thought that is what was causing the transaction log backup spike during index reorganize ? in addition to reorganize index maintenance task runs too long so my transaction backup folders is too big (100 GB before running maintenance cleanup task job for transaction backup)

my plan :

Transaction log backup schedule : Occurs every day every 15 minute(s) between 12:45:00 AM and 11:00:00 PM. Schedule will be used starting on 10/5/2015.

Full backup schedule : Occurs every day at 12:05:00 AM. Schedule will be used starting on 10/5/2015.

Maintenance Cleanup Task (for Transaction log backup and full backup) schedule : Occurs every day every 1 hour(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 10/5/2015.

Reorganize index schedule : Occurs every week on Monday, Tuesday, Wednesday, Thursday, Saturday, Sunday at 3:00:00 AM. Schedule will be used starting on 10/5/2015.

Rebuild index schedule : Occurs every week on Friday at 3:00:00 AM. Schedule will be used starting on 10/5/2015.

shaghayegh
  • 23
  • 6
  • I've got a couple of questions about this setup. 1) Why suspend log backups for 1h45m every day? 2) I haven't used maintenance plans in a *really* long time, but I don't remember the index maintenance tasks having a threshold. That is, they will re-organize/rebuild a table whether it needs it or not (which will cause a lot of unnecessary churn/log activity in your database). If that's the case, I'd suggest using the Ola Hallengren maintenance script with thresholds set appopriately. – Ben Thul Oct 16 '16 at 16:31
  • I am taking full backup on 12:05 am everyday before that i have another maintenance plan for shrinking databases at11:30 pm every day ,in this plan take a transaction log backup before the shrink databases – shaghayegh Oct 18 '16 at 04:51
  • Oh goodness. Shrinking should be a one-time operation and not automated. Something along the lines of "oh... I just deleted a bunch of data/dropped a table. I can get 100 Gb back if I shrink it down". The shrinking is also an operation that fragments your data. So, by shrinking, you're making more work for your re-org process! Check this for more info: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/. – Ben Thul Oct 18 '16 at 04:58
  • So sorry i just shrink log file (DBCC SHRINKFILE ) – shaghayegh Oct 18 '16 at 05:26
  • I still stand by my statement of shrinking shouldn't be automated. If your log grows to a certain size every day, that's is natural size. Your slowing down your transactions during the day having to wait for log growths. But shrinking the log won't fragment your data. – Ben Thul Oct 18 '16 at 14:28

0 Answers0