asked
Transaction log backup has grown huge growth during index reorganize
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. i have another maintenance plan for shrinking log file at 11:30 pm every day ,in this plan take a transaction log backup before the shrink log file
OK, a few things... (1) Index maintenance will blow out the transaction log as it is altering data on the disk (2) The SQL Server Maintenance Plan reindex task is unintelligent, and will do a full rebuild on every index whether it needs it or not. Suggest you investigate alternatives, such as [Ola Hallengren's SQL Server maintenance tools][1] - free to use. (I should point out that our hosts, Redgate software, do an [index manager tool][2] too, but it's not free. It does, however, have some other functionality) (3) You can help keep the transaction logs down by making more frequent t-log backups during index maintenance (I've done this before), but that doesn't help the volume of backup sizes (4) Are you compressing your backups natively (ie getting SQL Server to compress them as it goes)? This can help save huge amounts of disk space - depending on the data being stored, of course. If you've got a database storing compressed PDFs, say, it's not going to do much good, but for normal text / numeric data, you can get 75-80% compression. Chews up a bit more processor power, but saves a lot of I/O. Your mileage may vary. (5) Generally, don't shrink log files - they're the size they are for a reason. If you shrink them, you'll just have to wait for SQL Server to grow them again. And, depending on the settings, that could slow a transaction right down. (6) There's a gap in your TLog backup schedule between 23:00 & 00:45 - I assume that's a consequence of (5), above, but it really shouldn't be necessary. Keep the Tlog backup running all day. Lots to think about. I would also suggest reading through [Tony Davis & Gail Shaw's book on Transaction Log Management][3]. Free PDF from Redgate's online bookstore, with dead tree options available. Good luck. [1]:
http://ola.hallengren.com [2]:
http://www.red-gate.com/products/dba/sql-index-manager/ [3]:
http://www.red-gate.com/library/sql-server-transaction-log-management
19 People are following this question.
