3

I've recently taken over a SQL Server database that didn't have any transaction log backup routine running for at least 2 years. The first log backup was very large (3.8 gb on a 1.14 gb database) as you would predict, however subsequent log backups are still larger than I would have expected (somewhere in the region of 3 mb).

I also restored the database to a QA and ran several sequential log backups without there being any activity on the database and the log file backups were still ~3 mb.

What would account for the size and consistency of the transaction log backups with nominal activity on the database itself?

EDIT:

Here is the script we're using to run the TLOG backups, this is being executed every 10 minutes:

BACKUP LOG [mydatabase] 
TO  DISK = @fullpath 
WITH  RETAINDAYS = 2
    , NOFORMAT
    , NOINIT
    , NAME = @bckName
    , SKIP
    , REWIND
    , NOUNLOAD
    , STATS = 10;
  • What process are you using to take the backup? T-SQL BACKUP LOG... or some gui-based method? – Max Vernon Feb 23 '15 at 16:26
  • 1
    What is your definition of "nominal" activity? How frequently are your transaction log backups? The size would be due to that amount of logged operations that need to be backed up. – Thomas Stringer Feb 23 '15 at 16:35
  • @MaxVernon please see update above. This is being executed from an SQL Agent job every 10 minutes. – QFDev Feb 23 '15 at 16:40
  • @ThomasStringer, I would say no more that 20-50 commands (INSERTS, UPDATES, DELETES) on the db within 10 minutes. The TLOGs are getting backed up every 10 minutes using the script provided in the last edit on my post. Even with no activity on a QA we're seeing 3 mb log file backups. – QFDev Feb 23 '15 at 16:46
  • 1
    Did you noticed you have NOINIT in backup log command and this would append the log file to previous one. I guess this could be what causing unexpected growth – Shanky Feb 23 '15 at 16:55
2

Having spent some time looking at this in more detail it would appear that the unusually large transaction log backups are due to excessive internal fragmentation of the transaction log itself. This is due to unrestricted autogrowth of the transaction log over a long period of time.

The following excerpt from a SQLSkills.com article more precisely defines the problem

Often, when transaction logs are not pre-allocated and/or when there's been a lot of autogrowths, the transaction log can become internally fragmented. Internally your transaction logs are broken down into smaller more granular chunks called VLFs (Virtual Log Files). The size and number of VLFs you'll have depends largely on the size that the chunk is when it's added to you transaction log. If you add a new chunk to the transaction log which is 20MB (through autogrowth or through manual growth) then the number of VLFs that are added is 4. If you add a chunk which is greater than 64MB but less than or equal to 1GB, you'll add 8 VLFs. If you add more than 1GB then you'll add 16VLFs. In general, most transaction logs will only have 20 or 30 VLFs – even 50 could be reasonable depending on the total size of the transaction log. However, in many cases what happens is that excessive autogrowths can cause an excessive number of VLFs to be added – sometimes resulting in hundreds of VLFs. Having an excessive number of VLFs can negatively impact all transaction log related activities and you may even see degradation in performance when transaction log backups occur. To see how many VLFs you have solely look at the number of rows returned by DBCC LOGINFO. The number of rows returned equals the number of VLFs your transaction log file has. If you have more than 50, I would recommend fixing it and adjusting your autogrowth so that it doesn't occur as fequently. To get rid of all of the execessive VLFs, follow these easy steps to shrink off the fragmented chunk and add a new, clean chunk to your transaction log.

On this particular database it turns out we had 3011 VLFs, some degree over the recommended limit of 50 :)

We are now working through the steps in the above guide to reduce the number of internal VLFs. The QA database has been shrunk and the start size set to 2.5 gb, we now have 23 internal VLFs. So the resulting TLOG backup is in the region of 128 kb.

  • The first thing I do on any server I take over is change the model database properties to grow the database and log files in 64 or 128MB chunks instead of the default 10%. Those default settings have been around for 15 years or more, and are quite ridiculous in my opinion. – datagod May 22 '15 at 13:49

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.