What are the challenges you face when working across database platforms? Take the survey
Options

RedGate shows success, Event Log shows failure

TAETAE Posts: 5
edited July 7, 2009 9:10AM in SQL Backup Previous Versions
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:
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: D:\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: D:\DB01\DB-DB01\LOG_DB01_DB-DB01_20090701_075428.sqb
7/2/2009 8:08:06 AM: Deleting old backup file: D:\DB01\DB-DB01\LOG_DB01_DB-DB01_20090701_080757.sqb
7/2/2009 8:08:09 AM: Copied D:\DB01\DB-DB01\LOG_DB01_DB-DB01_20090702_080801.sqb to \\Server01\SqlBackups\DB01\DB-DB01\LOG_DB01_DB-01_20090702_080801.sqb.

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

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Todd,

    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.
  • Options
    Hi Brian -

    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:
    7/2/2009 8:02:04 AM: Thread 0 error:
    Error 620: Error writing to backup file(s).
    7/2/2009 8:02:04 AM: Warning 210: Thread 0 warning:
    Warning 210: Error writing to backup file: D:\DB01\DB01_CDATA\LOG_DB01_DB-DB01_CDATA_20090702_080203.sqb
    Warning: System error (The parameter is incorrect)
    Processed 1641 pages for database 'DB-DB01_CDATA', file 'DB01_log' on file 1.
    BACKUP LOG successfully processed 1641 pages in 0.221 seconds (60.809 MB/sec).

    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
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Todd,

    The issue in question does look similar to this one, in that case an upgrade should fix it.
  • Options
    Excellent information. I'm in the process of making sure we're still covered under maintenance as well as providing the necessary documents with which to implement the change/upgrade.

    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"?
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Todd,

    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.
  • Options
    That's great news! Thanks Brian! :D
Sign In or Register to comment.