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
,type,first_lsn
,last_lsn
,checkpoint_lsn
,database_backup_lsn
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.
Cognos Online Training - http://www.21cssindia.com/courses/cognos-online-training-27.html
ReplyDeleteIntroduction 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. contact@21cssindia.com