December 12, 2011 at 12:45 pm
We have a database with many tables. We take hourly transaction log dumps and run a nightly maintenance job. The normal trans log dump files for a certain database are about 200-400kb but after the maintenance job the first trans log dump can be 5GB! (On successive dumps the logs shrink back to 200-400K -- until the next night) The maintenance job checks database integrity, rebuilds indexes, updates statistics reindexes.
So…is my observation about log file size and maintenance jobs a common problem or does it indicate we are not doing something “right?
TIA,
Barkingdog
December 12, 2011 at 12:57 pm
That's typical. Your reinedexing is causing higher than normal transaction volume.
Some people choose to switch to bulk-logged recovery for indexing operations, but if you have the space, you can rest assured that this is normal and just leave it as is.
December 12, 2011 at 12:59 pm
Index rebuilds.
They're fully logged in full recovery and the log space used will be > the size of the index rebuild, hence the log backups will be as well. (bulk logged reduces the log space used, but not the log backup size)
About the only thing you can do to reduce the size of the log backups is to not rebuild everything every time (which the maint plan does), it's a waste of time. Rebuild just what needs rebuilding and nothing more.
You can use Ola Hallengren's rebuild scripts (http://ola.hallengren.com/Versions.html) or Michelle's (http://www.sqlfool.com), or you can roll your own if you're really, really bored.
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
December 12, 2011 at 1:29 pm
GilaMonster,
I think when the Maintenance PLan was setup they simply selected all options..... no one thought about the consequences. You remark
>>>
About the only thing you can do to reduce the size of the log backups is to not rebuild everything every time
>>>
Is the answer I am looking for. I will checkout Ola Hallengren's rebuild scripts.
barkingdog
December 12, 2011 at 1:38 pm
If they selected all options, then you have bigger problems than just index rebuilds.
Are they shrinking, rebuilding and reorganising, updating all statistics instead of just column stats?
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
December 12, 2011 at 1:44 pm
GliaMonster,
I looked at the job in greater detail. They
1) check database integrity (All databses). That's good
2) Shrink datbases (All user databases),. Free space: 10%
3) Rebuild Indexes ("original amount of free space")
4) Update Statistics (All Databses). Full scan
Barkingdog
December 12, 2011 at 1:46 pm
Take the shrink out, it's massively fragmenting your indexes right before rebuilding them (and growing the DB in the process). Talk about a waste of effort.
Make sure the stats update is column statistics only.
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
December 12, 2011 at 2:46 pm
I am also removing the "Rebuild Index" option of the maintenance plan. I'll read Ola's artice to see if\when I need to run his script. (I have a feeling that indexing should be done only as needed and not as part of a scheduled maintenance plan).
Barkingdog
December 12, 2011 at 6:47 pm
Barkingdog (12/12/2011)
I am also removing the "Rebuild Index" option of the maintenance plan. I'll read Ola's artice to see if\when I need to run his script. (I have a feeling that indexing should be done only as needed and not as part of a scheduled maintenance plan).Barkingdog
Not really - what you should be scheduling and running is a process that identifies indexes that need to be rebuilt. Ola's scripts will do that for you - and you then schedule it to run every night. This way, every night a different set of indexes will be selected and rebuilt.
Of course, if you have tables that fragment quickly - they will be selected every night. In that case, you may want to consider adjusting the fill factor to avoid the index from fragmented that quickly. Note - there is a trade-off here, if you decrease the fill factor it will grow the index larger and could take a few milliseconds longer to insert the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 13, 2011 at 1:55 am
Barkingdog (12/12/2011)
(I have a feeling that indexing should be done only as needed and not as part of a scheduled maintenance plan).
Indexing (creating or modifying indexes) should be done as needed. Reindexing is part of any good maintenance routine.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply