Periodic failure in tranlog backup
satterw
Posts: 17
4.2.0.425 professional, SQL 2000 Enterprise + SP4
We are using log shipping with good success. We do have one issue where tran log backups occasionally fail on one database with the following error:
SQL Backup log file
6/9/2006 5:00:01 PM: Backing up DDS_SDC (transaction log) to:
l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060609_170001.sqb
6/9/2006 5:00:01 PM: BACKUP LOG [DDS_SDC] TO DISK = 'l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060609_170001.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', PASSWORD = 'XXXXXXXXXX', KEYSIZE = 256, ERASEFILES_ATSTART = 7, MAILTO_ONERROR = 'xxx@yyy.com', COPYTO = '\\target_server\logs', COMPRESSION = 3, THREADS = 1
6/9/2006 5:00:02 PM: Deleting old backup file: l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060602_160001.sqb
6/9/2006 5:00:02 PM: Backup data size : 704.000 KB
6/9/2006 5:00:02 PM: Compressed data size: 19.000 KB
6/9/2006 5:00:02 PM: Compression rate : 97.30%
Processed 11 pages for database 'DDS_SDC', file 'DDS_SDC_Log' on file 1.
BACKUP LOG successfully processed 11 pages in 0.023 seconds (3.695 MB/sec).
6/9/2006 5:00:03 PM: Error 775: Failed to open file to write LSN data. Another process might be locking this file: (l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060609_170001.sqb)
6/9/2006 5:00:05 PM: Copied l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060609_170001.sqb to \\target_server\log\LOG__DDS_SDC_20060609_170001.sqb.
When this happens, we LSN seems to be unreadable from SQL Backup, and our only resolution is to restore from a full backup and start from there. There is no virus software on the server that might be locking the file. This occurs once every 2 or so weeks. Didn't know if this is something you might be able to help with or not. Thanks.
We are using log shipping with good success. We do have one issue where tran log backups occasionally fail on one database with the following error:
SQL Backup log file
6/9/2006 5:00:01 PM: Backing up DDS_SDC (transaction log) to:
l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060609_170001.sqb
6/9/2006 5:00:01 PM: BACKUP LOG [DDS_SDC] TO DISK = 'l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060609_170001.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', PASSWORD = 'XXXXXXXXXX', KEYSIZE = 256, ERASEFILES_ATSTART = 7, MAILTO_ONERROR = 'xxx@yyy.com', COPYTO = '\\target_server\logs', COMPRESSION = 3, THREADS = 1
6/9/2006 5:00:02 PM: Deleting old backup file: l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060602_160001.sqb
6/9/2006 5:00:02 PM: Backup data size : 704.000 KB
6/9/2006 5:00:02 PM: Compressed data size: 19.000 KB
6/9/2006 5:00:02 PM: Compression rate : 97.30%
Processed 11 pages for database 'DDS_SDC', file 'DDS_SDC_Log' on file 1.
BACKUP LOG successfully processed 11 pages in 0.023 seconds (3.695 MB/sec).
6/9/2006 5:00:03 PM: Error 775: Failed to open file to write LSN data. Another process might be locking this file: (l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060609_170001.sqb)
6/9/2006 5:00:05 PM: Copied l:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LOG__DDS_SDC_20060609_170001.sqb to \\target_server\log\LOG__DDS_SDC_20060609_170001.sqb.
When this happens, we LSN seems to be unreadable from SQL Backup, and our only resolution is to restore from a full backup and start from there. There is no virus software on the server that might be locking the file. This occurs once every 2 or so weeks. Didn't know if this is something you might be able to help with or not. Thanks.
Comments
Please make sure that the I:\ backup location and the log shipping share aren't actually the same location.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
In version 4.5, SQL Backup will make 4 attempts to open the backup file to write the LSN data, over a period of 15 seconds. This would address your issue if the process that locked your backup file only does so momentarily.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8