RSS Feed

Lesson Learned: Why is my tlog backup huge at 5AM?


September 16, 2009 by Mike Hillwig

As a DBA, I find myself learning new things all of the time.

I was looking at the transaction log backups of one of my servers recently and saw something incredibly odd. Most of my transaction log backups are in tens of megabytes. But the backup at 5:00 AM was averaging about a gigabyte. This was a head-scratcher.

Were my users really banging on that system at 5:00 AM EST? Keep in mind that about 85% of that system’s users are on the east coast of the US. Those users are mostly still in bed. Sure, Asia is just going to bed and Europe is in prime time. But the numbers just didn’t add up.

When I looked at the tlog backups on a weekend, the 5:00 AM backup was still huge, so that eliminated my user community. This really wasn’t a problem, but I hated having a situation I couldn’t explain. Scratching my head, I e-mailed my support contact at the vendor, wondering if they were doing some type of maintenance. This is an application that stores documents, so it wasn’t unreasonable to expect them to do something to their full-text indexes overnight. The vendor said they don’t do anything overnight to their indexes.

Something in my head snapped. Indexes. Overnight.

I have a SQL job that rebuilds all of the database indexes on that database, and it runs overnight. It used to run right after the midnight backups, but I had recently moved it to run at 4:00 AM. That explained the problem, but it forced another situation.

Did I want to back up an additional 7 GB of data every week by leaving the situation as-is, or did I want to reschedule or change how I rebuild the database indexes. A little bit of experimentation showed that the largest indexes are the content tables that change the most. Those are the ones we want to rebuild anyway, so I ended up leaving the situation alone.

The lesson learned here is that rebuilding your indexes will add to your transaction logs.