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?

  • 1
    You could stop doing both and just update statistics. – Erik Darling Nov 8 '17 at 18:53
  • Again, I'm no DBA so would kindly ask for more explanation. This DB is written to every minute, 24/7/365. The source remembers when it last wrote to the DB, so if no writes are happening the source holds the data and then writes when it can, so no data are lost. However, when an index frags above 90% or so I get timeout errors, because it takes > 1 Min to write to the DB. Will updating statistics get rid of the timeout errors? – Thomas Covenant Nov 8 '17 at 19:09
  • @ThomasCovenant I would say that being the case, the less you're doing manually the better. All the more reason to turn on Auto Update Statistics, and Auto Growth. – pim Nov 8 '17 at 19:14
  • I am going to try turning on statistics and auto update statistics. As I mentioned in my other comment, I cannot auto grow this log. I'm against the HDD limit on these older systems. Thanks again! I'll let you know how it works out. When I turn on the statistics and auto update, will I still need to re-org the indices on a regular basis? – Thomas Covenant Nov 8 '17 at 19:17
  • That's a great question. My answer will be the same as it is to most programming situations, try and see. I reckon a lot, or at least some of the gain you're getting from the index reorg IS that the stats are being refreshed. My advice, setup an agent job to rebuild the indexes daily, during a time you KNOW the database is under little to no load. – pim Nov 8 '17 at 19:22

Perhaps AutoGrowth on the .ldf will help you here?

According to Brent Ozar, who is a legend in this space, set your FileGrowth setting for the .ldf to 128mb.

As per Erik's suggestion, I would also make sure that you have Auto Update Statistics turned on.

  • 1
    To add - they recommend sizing your transaction log equal to 2x the size of your largest index. – rvsc48 Nov 8 '17 at 19:13
  • Thanks for the answer! Unfortunately these systems were put together using 500GB drives and the limit of trans log size is about 150GB. I can't autogrow them without running out of HDD space, which makes things worse. Our newer systems are using dual 1TB drives, with 1TB dedicated to SQL. So this isn't an issue. But the older systems are limited. – Thomas Covenant Nov 8 '17 at 19:15
  • @ThomasCovenant What a conundrum! Is it possible to put the database into simple recovery mode? If I'm not mistaken it reduces writes to the log. – pim Nov 8 '17 at 19:18
  • 2X largest index. Yeah. Is there any way to automatically reorg only one index at a time? – Thomas Covenant Nov 8 '17 at 19:19
  • 1
    Awesome. I didn't even think of doing this as a job instead of as a maintenance plan. Yes. I think one at a time, with a break to backup trans log will do the trick, running as a SQL job instead of a maintenance plan. Ha! Simple. Thanks very, very much to all of you! – Thomas Covenant Nov 8 '17 at 20:07

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.