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

Help newbie - SQL Backup 4.0.0.113

gsc_ghxgsc_ghx Posts: 73
edited December 4, 2007 10:36PM in SQL Backup Previous Versions
There is a full database backup job that is starting to fail on a regular basis. The error is: Log Job History (DBA_Backup_System_Full)

Step ID 1
Server Server_1
Job Name DBA_Backup_System_Full
Step Name DBA_Backup_System_Full
Duration 00:15:02
Sql Severity 16
Sql Message ID 245
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: sqla_svc. ***** Backing Up Database : master ***** [SQLSTATE 01000] (Message 0) -SQL "BACKUP DATABASE [master] TO DISK = '\sqlbkp3\master_FULL_RG.bak'WITH COMPRESSION = 1, INIT, MAXTRANSFERSIZE = 65536, MAXDATABLOCK = 655360" [SQLSTATE 01000] (Message 0) Syntax error converting the varchar value ' ==== backup failed: ' to a column of data type int. [SQLSTATE 22018] (Error 245). The step failed.

I have searched thru Microsoft documentation and cannot find an answer to this issue. We are running Sql Backup 4.0.0.113 on SQL Server 2000. I can manually backup the database but cannot manually run this job. Currently the only fix we have is to failove to the other node - in a sense restarting the service. this issue is starting to crop up regularly and failover is not an ongoing option. Any advice - thank you... :)

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Could you pls post the contents of the SQL Backup log file for the process that failed? The default folder for the logs is <system drive>:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    peter thank you so VERY much for responding. I had two failures here they are: This failed Monday - 12/3 at 8:30pm
    SQL Backup log file
    12/3/2007 8:30:01 PM: Backing up master (full database) to:
    \\server1\sqlbkp3\CATSQL02\master_FULL_RG.bak

    12/3/2007 8:30:01 PM: BACKUP DATABASE [master] TO DISK = '\\server1\sqlbkp3\CATSQL02\master_FULL_RG.bak' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, COMPRESSION = 1

    12/3/2007 8:30:02 PM: Thread 0 error:
    Error 620: Error writing to backup file(s).
    Process terminated unexpectedly. Error code: -2139684860
    Thread 0 warning:
    Warning 210: Error writing to backup file: \\server1\sqlbkp3\CATSQL02\master_FULL_RG.bak
    Warning: System error code: (Insufficient system resources exist to complete the requested service)

    12/3/2007 8:30:02 PM: Server: Msg 3013
    BACKUP DATABASE is terminating abnormally.
    12/3/2007 8:30:02 PM: Server: Msg 3202
    Write on 'SQLBACKUP_C82BA2F7-37C4-49BF-9BB4-256B4DCFC853' failed, status = 112. See the SQL Server error log for more details.
    12/3/2007 8:30:02 PM: Failed to close vdi.

    this failed 12/4/07 at 4:45am
    SQL Backup log file
    12/4/2007 4:45:00 AM: Backing up Admin (full database) to:
    \\server1\sqlbkp3\CATSQL02\Admin_FULL_RG.bak

    12/4/2007 4:45:00 AM: BACKUP DATABASE [Admin] TO DISK = '\\server1\sqlbkp3\CATSQL02\Admin_FULL_RG.bak' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, COMPRESSION = 1

    12/4/2007 4:45:01 AM: Thread 0 error:
    Error 620: Error writing to backup file(s).
    Process terminated unexpectedly. Error code: -2139684860
    Thread 0 warning:
    Warning 210: Error writing to backup file: \\server1\sqlbkp3\CATSQL02\Admin_FULL_RG.bak
    Warning: System error code: (Insufficient system resources exist to complete the requested service)

    12/4/2007 4:45:01 AM: Server: Msg 3013
    BACKUP DATABASE is terminating abnormally.
    12/4/2007 4:45:01 AM: Server: Msg 3202
    Write on 'SQLBACKUP_7176AF0D-A8A8-4CEC-A256-F112B11EC4D3' failed, status = 112. See the SQL Server error log for more details.
    12/4/2007 4:45:01 AM: Failed to close vdi.

    there is more than sufficient disk available - 21gb in fact.

    The sql server log shows: 12/3/07 8:30pm
    Date 12/3/2007 8:30:00 PM
    Log Job History (DBA_Backup_System_Full)

    Step ID 1
    Server CATSQL02
    Job Name DBA_Backup_System_Full
    Step Name DBA_Backup_System_Full
    Duration 00:15:02
    Sql Severity 16
    Sql Message ID 245
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: sqla_svc. ***** Backing Up Database : master ***** [SQLSTATE 01000] (Message 0) -SQL "BACKUP DATABASE [master] TO DISK = '\\server1\sqlbkp3\CATSQL02\master_FULL_RG.bak'WITH COMPRESSION = 1, INIT, MAXTRANSFERSIZE = 65536, MAXDATABLOCK = 655360" [SQLSTATE 01000] (Message 0) Syntax error converting the varchar value ' ==== backup failed: ' to a column of data type int. [SQLSTATE 22018] (Error 245). The step failed.


    And the 12/4/07 4:45am job - same thing
    Date 12/4/2007 4:45:00 AM
    Log Job History (DBA_Backup_User_Full)

    Step ID 2
    Server CATSQL02
    Job Name DBA_Backup_User_Full
    Step Name DBA_Backup_User_Full
    Duration 00:15:02
    Sql Severity 16
    Sql Message ID 245
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: sqla_svc. ***** Backing Up Database : Admin ***** [SQLSTATE 01000] (Message 0) -SQL "BACKUP DATABASE [Admin] TO DISK = '\\server1\sqlbkp3\CATSQL02\Admin_FULL_RG.bak'WITH COMPRESSION = 1, INIT, MAXTRANSFERSIZE = 65536, MAXDATABLOCK = 655360" [SQLSTATE 01000] (Message 0) Syntax error converting the varchar value ' ==== backup failed: ' to a column of data type int. [SQLSTATE 22018] (Error 245). The step failed.


    I am new to this position - as the other SQL DBA left and i have no one that is knowledgable about this process. Thank you so much again.
    :roll:

    g
  • Options
    peteypetey Posts: 2,358 New member
    This is an issue commonly associated with backing up to network shares. Suggestions are as follows:

    - back up to a local drive, then copy the file to the network share. You can do this using the COPYTO option in SQL Backup.

    - reduce the MAXDATABLOCK value. Must be in multiples of 64 Kb, from 64 Kb to 4 MB. Some systems have problems writing large blocks to a network share. Currently, it appears you are using 640 Kb. Try 524288 (512 Kb).

    - check the fragmentation level of the network share, and defrag where necessary.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.