Sql-server – Transaction Log file size for Index reorg

indexlogsql server

I have a DB that is 100GB. It's fixed this way on purpose. It won't exceed this. I have two indices. One with a single value. One with three values.
I'm not a DBA. I'm a guy who's been tasked with working on SQL DB's part time on occasion. They were created by someone else and I'm on the steep part of the learning curve.
I have the transaction log set to 150GB. It works great until I automatically reorganize the indices. At this point I always get a transaction log full. Even if I set it to back up the log, then reorganize.
If I manually reorg it's fine, because I can do each index separately and I BU the trans log first.
So how big do I need the trans log to be, based on the above info, in order to be able to set up a maint task to reorg? Will it be any better if I do a rebuild instead?

Best Answer

You mention that if you run the index rebuild process manually, and take a transaction log backup in between each index rebuild, then you don't have a problem.

As has been noted, you can create a script to rebuild each index, taking a transaction log backup in between each rebuild.

Alternately, you could increase the frequency of your schedule transaction log backups while the rebuild process is running. For example, if you normally perform tlog backups once an hour, do it every ten minutes for the duration of the maintenance job.

There are multiple ways to do this:

  • Schedule a secondary tlog backup job for the expected duration of the index rebuild.

    For instance, if your regular tlog backup runs at 1AM, 2AM, 3AM, etc., and the rebuild job runs at 2:30 AM and usually takes no more than 3 hours, schedule a secondary t-log backup starting at 2:35 AM, running every 10 minutes, and ending at 5:35 AM.

  • Have a disabled secondary schedule on your t-log job that you enable at the start of the rebuild job, and disable at the end. In this case (with the same theoretical frequencies as in the example above), this schedule would be set to run the job every 10 minutes starting as 12:05 AM. You should be able to use sp_update_schedule to enable and disable the job schedule. I would make sure the schedule had a unique name, and use that in the stored procedure (as that could remain the same across multiple servers).

    If the schedule was named "TLog Additional Runs", You would use:

    EXECUTE sp_update_schedule @name = N'TLog Additional Runs', @enabled = 1;

    to turn the schedule on, and:

    EXECUTE sp_update_schedule @name = N'TLog Additional Runs', @enabled = 0;

    to turn it off.

Related Question