Transaction log backup jobs intermittently failing
jkl6967
Posts: 3
I have serveral SQL Server installations all with a single instance and multiple databases. SQL Backup V4 is installed on each server and is configured to send error info on failures. It doesn't happen all the time but when a tran log backup fails I get an error similar to this:
6/5/2007 4:08:24 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
6/5/2007 4:08:26 PM: SQL error 3013: BACKUP LOG is terminating abnormally.
6/5/2007 4:08:26 PM: SQL error 3201: Cannot open backup device 'SQLBACKUP_215FEF52-8D8A-4D30-9B27-EC0FE2C1337F'. Operating system error 0x80070002(The system cannot find the file specified.).
6/5/2007 4:08:26 PM:
6/5/2007 4:08:27 PM: Memory profile
6/5/2007 4:08:27 PM: Type Maximum Minimum Average Blk count Total
6/5/2007 4:08:27 PM:
6/5/2007 4:08:27 PM: Commit 2476400640 4096 382748 42267 16177643520
6/5/2007 4:08:27 PM: Reserve 1576992768 4096 221275 38295 8473735168
6/5/2007 4:08:27 PM: Free 6856078852096 4096 7653962982 1146 8771441577984
6/5/2007 4:08:27 PM: Private 2476400640 4096 307477 79584 24470323200
6/5/2007 4:08:27 PM: Mapped 3948544 4096 178702 70 12509184
6/5/2007 4:08:27 PM: Image 32976896 4096 185623 908 168546304
6/5/2007 4:08:27 PM:
6/5/2007 4:08:27 PM: Warning 300: Backup failed. Retry attempt: 1
6/5/2007 4:08:29 PM: BACKUP LOG [HCHB_ALACARE] TO DISK = 'F:\backups\HCHB_ALACARE\HCHB_ALACARE_LOUPSQL003_(local)_LOG_20070605_1608.sqb' WITH NAME = 'Database (HCHB_ALACARE), 6/5/2007 4:08:05 PM', DESCRIPTION = 'Backup on 6/5/2007 4:08:05 PM Server: LOUPSQL003 Database: HCHB_ALACARE', INIT, MAILTO_ONERROR = 'hchb_sqladmins@hchb.local', COMPRESSION = 3, THREADS = 1
6/5/2007 4:08:41 PM: Backup data size : 160.688 MB
6/5/2007 4:08:41 PM: Compressed data size: 41.336 MB
6/5/2007 4:08:41 PM: Compression rate : 74.28%
Processed 20349 pages for database 'HCHB_ALACARE', file 'HCHB_TSCH_log' on file 1.
BACKUP LOG successfully processed 20349 pages in 10.249 seconds (16.264 MB/sec).
Any insight would be appreciated
6/5/2007 4:08:24 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
6/5/2007 4:08:26 PM: SQL error 3013: BACKUP LOG is terminating abnormally.
6/5/2007 4:08:26 PM: SQL error 3201: Cannot open backup device 'SQLBACKUP_215FEF52-8D8A-4D30-9B27-EC0FE2C1337F'. Operating system error 0x80070002(The system cannot find the file specified.).
6/5/2007 4:08:26 PM:
6/5/2007 4:08:27 PM: Memory profile
6/5/2007 4:08:27 PM: Type Maximum Minimum Average Blk count Total
6/5/2007 4:08:27 PM:
6/5/2007 4:08:27 PM: Commit 2476400640 4096 382748 42267 16177643520
6/5/2007 4:08:27 PM: Reserve 1576992768 4096 221275 38295 8473735168
6/5/2007 4:08:27 PM: Free 6856078852096 4096 7653962982 1146 8771441577984
6/5/2007 4:08:27 PM: Private 2476400640 4096 307477 79584 24470323200
6/5/2007 4:08:27 PM: Mapped 3948544 4096 178702 70 12509184
6/5/2007 4:08:27 PM: Image 32976896 4096 185623 908 168546304
6/5/2007 4:08:27 PM:
6/5/2007 4:08:27 PM: Warning 300: Backup failed. Retry attempt: 1
6/5/2007 4:08:29 PM: BACKUP LOG [HCHB_ALACARE] TO DISK = 'F:\backups\HCHB_ALACARE\HCHB_ALACARE_LOUPSQL003_(local)_LOG_20070605_1608.sqb' WITH NAME = 'Database (HCHB_ALACARE), 6/5/2007 4:08:05 PM', DESCRIPTION = 'Backup on 6/5/2007 4:08:05 PM Server: LOUPSQL003 Database: HCHB_ALACARE', INIT, MAILTO_ONERROR = 'hchb_sqladmins@hchb.local', COMPRESSION = 3, THREADS = 1
6/5/2007 4:08:41 PM: Backup data size : 160.688 MB
6/5/2007 4:08:41 PM: Compressed data size: 41.336 MB
6/5/2007 4:08:41 PM: Compression rate : 74.28%
Processed 20349 pages for database 'HCHB_ALACARE', file 'HCHB_TSCH_log' on file 1.
BACKUP LOG successfully processed 20349 pages in 10.249 seconds (16.264 MB/sec).
Any insight would be appreciated
Comments
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
6/5/2007 4:08:05 PM: Backing up XXX (transaction log) to:
F:\backups\XXX\XXX_XXX_(local)_LOG_20070605_1608.sqb
6/5/2007 4:08:05 PM: BACKUP LOG [XXX] TO DISK = 'F:\backups\XXX\XXX_(local)_LOG_20070605_1608.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', MAILTO_ONERROR = 'XXX@XXX', COMPRESSION = 3, THREADS = 1
Thanks
4:08:05 - started backup
4:08:24 - SQL Backup returned with error 1010
4:08:26 - SQL Server reported error 3201
When SQL Backup performs a backup, it sends a BACKUP command to SQL Server. This probably happened at around 4:08:09. There is a timeout of 15 seconds, before SQL Backup returns an error - in this case, at 4:08:24. 2 seconds later, SQL Server was looking for the SQL Backup endpoint, but could not find it.
Two possibilities:
- the server was under a heavy load at that time, causing SQL Server to take more than 15 seconds before it could respond to the backup request issued by SQL Backup
- there was another backup of the same database which blocked this backup. The earlier backup then completed between 4:08:24 and 4:08:26, by which time the SQL Backup endpoint no longer exists.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8