RedGate shows success, Event Log shows failure
TAE
Posts: 5
I've done a few searches in this forum, but I was unable to find a matching (or near-to-matching) situation, so I figured I'd take the plunge and post my issue.
I have MSSQL 2005 (SQL Server 9.0.3042) installed on a Windows 2003 Enterprise x64 SP2 server. The RedGate agent version is 5.1.0.2781. Every so often, I'll get an event from the SQLAgent in the Application Event Log stating:
The SQL Job History gives me this information:
However, upon checking the RedGate log, I find the following:
Based on what I'm reading in the RedGate log, everything is fine. I've checked the destination, and the SQB file (noted above) exists. So, I'm wondering why SQL thinks there's a problem when I can't seem to find one. And, to make matters even more interesting, subsequent backups using this same task run without an error.
Any thoughts or advice on this behavior would be appreciated.
Thanks!
-Todd
I have MSSQL 2005 (SQL Server 9.0.3042) installed on a Windows 2003 Enterprise x64 SP2 server. The RedGate agent version is 5.1.0.2781. Every so often, I'll get an event from the SQLAgent in the Application Event Log stating:
SQL Server Scheduled Job '1-SQL Backup Transaction' (0x624ADE72BBB79A41A99FE65B0D5A7222) - Status: Failed - Invoked on: 2009-07-02 08:00:01 - Message: The job failed. The Job was invoked by Schedule 3 (:45 every 15 minutes). The last step to run was step 1 (execute master..sqlbackup).
The SQL Job History gives me this information:
Message
Executed as user: DOMAIN01\SQLAgent. SQL Backup failed with exit code: 790 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.
However, upon checking the RedGate log, I find the following:
7/2/2009 8:08:01 AM: Backing up DB-DB01 (transaction log) on DB01 instance to:
7/2/2009 8:08:01 AM:\DB01\DB-DB01\LOG_DB01_DB-DB01_20090702_080801.sqb
7/2/2009 8:08:01 AM: BACKUP LOG [DB-DB01] TO DISK = 'D:\DB01\<DATABASE>\<AUTO>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', PASSWORD = 'XXXXXXXXXX', KEYSIZE = 256, ERASEFILES = 1, COPYTO = '\\Server01\SqlBackups\DB01\<DATABASE>', COMPRESSION = 1
7/2/2009 8:08:04 AM: Backup data size : 102.188 MB
7/2/2009 8:08:04 AM: Compressed data size: 22.646 MB
7/2/2009 8:08:04 AM: Compression rate : 77.84%
Processed 12572 pages for database 'DB-DB01', file 'DB-DB01_log' on file 1.
BACKUP LOG successfully processed 12572 pages in 1.992 seconds (51.698 MB/sec).
7/2/2009 8:08:06 AM: Deleting old backup file:
7/2/2009 8:08:06 AM: Deleting old backup file:
7/2/2009 8:08:09 AM: Copied
Based on what I'm reading in the RedGate log, everything is fine. I've checked the destination, and the SQB file (noted above) exists. So, I'm wondering why SQL thinks there's a problem when I can't seem to find one. And, to make matters even more interesting, subsequent backups using this same task run without an error.
Any thoughts or advice on this behavior would be appreciated.
Thanks!
-Todd
Comments
Sorry, I don't have an answer to this one. I'd assume that the SQL Backup log that you quote was from a different job. A 790 is a general thread error and should be reflected in the SQL Backup log file.
Aside from that, SQL Backup 5.1 and 5.2 had some issues that caused thread aborts during backup. You may want to consider upgrading your server components to 5.4.
Thanks for the reply! After reading your response and pausing to think for a moment, I fear that I had a moment of "stupid" when I was looking at the log file. For whatever reason, it didn't occur to me that this log file was a log for the *entire* task and I should look at the whole log file when researching a problem, rather than the end.
Sure enough, after looking through the log, I found this entry near the top:
So, your theory about 5.1 having problems with threads aborting appears to be spot-on. I start the process of getting these agents update to the current version.
Again, thanks for your assistance!
-Todd
The issue in question does look similar to this one, in that case an upgrade should fix it.
I've taken a quick peek at the upgrade process from within "SQL Backup" and as the SQL servers are in a multi-node cluster (all active, no standby), I'll be providing the appropriate cluster credentials.
One quick question: Is a reboot required for the upgrade, or is everything handled "hot"?
An upgrade from SQL Backup 5.1 to 5.4 is a no-cost upgrade, even if you do not have support, so no worries there. SQL Backup does not ask for a reboot after the installation is done, but in some rare cases, you may need to restart the SQL Server Service because the server does not free the extended stored procedure dll. On 2005 and 2008 platforms, this happens rarely; it's most common on SQL Server 2000.