Help newbie - SQL Backup 4.0.0.113

gsc_ghx · December 4, 2007 4:48PM

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...

petey · December 4, 2007 9:00PM

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.

gsc_ghx · December 4, 2007 9:43PM

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

petey · December 4, 2007 10:36PM

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.

Help newbie - SQL Backup 4.0.0.113

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk