Wednesday, September 21, 2011

Does A Log Backup Failure Break The Chain?

We recently ran into a situation where the file share we were using to store full database backups along with transaction log backups ran out of space.  When the transaction log tried to backup we received the following error:

SQL Server Alert System: 'Severity 016' occurred on <Server>

DESCRIPTION:    BackupIoRequest::ReportIoError: write failure on backup device '<filepath>\<filename>.trn'. Operating system error 112 (There is not enough space on the disk.).

Our first thought was that since the transaction log backup failed, our backup chain was broken. So once we cleared space on the file share, we ran a full backup on each of the affected databases.

After some research regarding transaction log LSN's and the Backupset table in the MSDB database, I quickly relearned that the chain had never been broken. Since the Tlog backup failed to complete successfully, no record was written in the Backupset table and the inactive portion of the log was never truncated. You can verify this by looking at the First_LSN and the Last_LSN column in the MSDB..BackupSet table using the following query.

SELECT  backup_start_date
  FROM  msdb..backupset
WHERE   database_name = '<DBName>'   
       and type = 'L'  
ORDER BY backup_start_date

In order for the chain to be unbroken, the First_LSN must equal the previous Tlog backup's Last_LSN. In this scenario, log backups are taken once an hour and failed starting at 11 PM until space was cleared on the file share and the next Tlog backup ran at 6 AM. Once space was cleared, the transaction log backup could finally complete and the inactive portion of the log could be truncated.  We can verify this by looking at the Last_LSN on the 10 PM Tlog backup and correlating that to the First_LSN on the next day's 6 AM Tlog backup.

So even though the space error occurred and the Tlog backup failed, the transactions in the log were preserved until such time that a successful transaction log backup could be completed.

1 comment:

  1. Cognos Online Training -
    Introduction to Dataware Housing (DWH)
    Report Studio - Type of Report Templates - Creating list, Cross Tab, Chart, repeater, Maps & Other Reports - Report templates, formatting Reports – fonts, style, header, footer, boarder…etc - Page Break - Query Calculations & Layout Calculations - Layout component reference - Generating and Creating Prompt Page - Setting Variables - Conditional Formatting - Types of Prompts - Cascading Prompt ,Generate Prompt - Static choice - Token Prompt - Drill through - Master Details - Conditional Block - HTML Items & Hyperlinks Bookmark Report - Setting Burst Options - Schedulining of Reports - Drill up & Drill down,Tree prompt - Render Variable - Dynamic column Sorting - Reports Run with Options (separate course not included inthis package) - Around 5 Realtime reports - Analysis Studio - Give the Detail Description of the This Studio and Explain - the Some of Reports - Event Studio - Employees to learn at their own pace and maintain control of learning “where, when and how” with boundless access 24/7by 21st Century Software Solutions.