Transaction log backup jobs intermittently failing

jkl6967

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

petey

Could you pls post the first few lines of the log file? I'm interested to know when the backup started. Thanks.

jkl6967

Sure, here ya go. Let me know if there's anything else you need:

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

petey

Thanks for the details. Your situation:

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.