1

I'm having an issue with a SQL Server log file which will not maintain a steady amount of space held on a HDD which it has been issued. The drive is ~50GB and the log file is growing to occupy this quite often.

The database is set to FULL recovery mode, with hourly differential backups. I understand that this should allow the log file to truncate and use space already allocated to it?

When checking the status of the log file using DBCC LOGINFO it returns that the status of most VLF's within the files are set to '2' which shows them as being active? We've 537 VLF's in the log file. I don't know why all these would be active at one point using 15GB of space?

I've checked the replication through DBCC OPENTRAN and it reports that there are no actively open transactions on the file.

Is there a method of maintaining this file I may of overlooked?

Is there anywhere I can go to determine exactly what the log file is doing?

Would replication interfere with truncating/shrinking the log file?

At present, it grows around 1.67GB per day. I've ~18 days before having to perform some emergency work on shrinking the file. I'd like it to stay below 25GB if possible so there is plenty of room to grow if this has a sudden need for space.

Colin 't Hart
  • 9,323
  • 15
  • 35
  • 43
Adam W.
  • 53
  • 3
  • 1
    This question belongs on [dba.se] – Kermit Nov 20 '13 at 17:39
  • 3
    errrrr .... you'll still need to take t-log backups even though your doing differential backups. – swasheck Nov 20 '13 at 17:42
  • See, that's what I thought, although doing this method there doesn't appear to of been an issue with the log before now. I don't know if that's because of how it was set up (perhaps bundling a log backup? no idea...) but appeared to generate the error after we had an issue with some replication. –  Nov 20 '13 at 17:47
  • Where was the replication issue? Pub, Dist, Sub? – swasheck Nov 20 '13 at 17:49
  • 2
    If you don't do log backups, don't use FULL recovery model. It's that simple. – Marian Nov 20 '13 at 17:50

1 Answers1

8

No. A differential never truncates the log. Please consider your recovery needs and choose a recovery model that fits those requirements.

There’s still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It NEVER happens.

http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/

I'd recommend easing up on the Differentials and start with scheduling t-log backups ever 15 minutes.

More information:

Once you get your t-logs backed up appropriately then you'll want to leave the shrinking alone. Whatever it grows to (usually within that 15 minutes) is usually the growth profile of what it actually needs. To shrink the log file only to let it grow again will cause unnecessary overhead and may impact performance.

If you want to consolidate your VLFs then you need to find an outage window when you can shrink the log file and then grow it back to it's preferred size using a standard increment (use growth increments of 8000MB)

swasheck
  • 10,665
  • 4
  • 47
  • 88
  • 3
    While link answers are helpful, it's beneficial to point the OP to the specific part of the link, or even include it in your answer. – Kermit Nov 20 '13 at 17:50
  • 2
    Marian - was that a database pun? :)

    Swascheck - awesome. thanks - I didn't actually know that. appreciate the comprehensive link for some reading on the subject. Will test and report back.

     –  Nov 20 '13 at 17:52
  • 1
    @AdamW. - kind of :-). Read that link, that's from the father of the storage engine of SQL Server. – Marian Nov 20 '13 at 17:55
  • Thanks guys! To cement my potential SQL statement here then, and a little of follow up, would I then need to specify to 'DBCC SHRINKFILE' after making the T-Log backup?

    Would follow a '[T-Log backup statment], [shirnkfile statment]'? This would be added into the currently scheduled jobs, run it once a day to maintain a particular size?

     –  Nov 20 '13 at 18:00
  • @AdamW. i updated my answer to reflect this question. – swasheck Nov 20 '13 at 18:06