I have an hourly maintenance plan the runs a T-Log backup. Some of my databases may have every hour represented in the file system, while others have some hours "missing" or even none at all. Is it possible that the maintenance plan won't write a T-Log backup if there have been no transactions? If this is not the case, do I have a problem that doesn't seem to be called out in the error log? I'm concerned with some DBs that are marked as full recovery.

  • SQL lets you write multiple "files" into the same physical file. Make sure that is not the case for these databases. – Jonathan Fite Mar 15 '16 at 20:16
  • @JonathanFite interesting suggestion, but it would seem that it's not writing to the same file since it picks a new datetime to put in each file name, such as "2016_03_15_145126_4878847" – Tim Lehner Mar 15 '16 at 22:07

Review the backupset and related tables in msdb as the source for if a successful backup was taken. If you see gaps in when a scheduled backup was to have occurred, your job either failed or never ran.

Every backup command generates an internal checkpoint, which in turn, moves the log sequence number (lsn). Review the first_lsn and last_lsn pattern with every log backup in the backupset table. Likewise, a file will always be generated if your job backs up to a time-stamped filename (default behavior of maintenance plans). You can verify this behavior by manually/interactively running a log backup command multiple times on a database without an transactions between runs.

| improve this answer | |

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.