Exit Code 5240 after clearing old backup history

rick.sheeley

We recently cleared the backup history from one of our servers, due to constantly getting "cannot clear history" errors all the time.

Now that we have done so, re-boot the server and cleaned everything up nicely, we are getting many, many of these:

job failed with exitcode: 5240 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.


Executed as user: PHASE2\SQLAdmin. EXEC master..sqlbackup N'-SQL "BACKUP LOGS [P2_MAS500_iapp] TO DISK = '' <AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', ERASEFILES = 404h , COMPRESSION = 1, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT [SQLSTATE 01000] (Message 0) SQL Backup job failed with exitcode: 5240 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.

The server is an HP DL380 with 16GB ram and mulktple processors. SQl Server 2k SP4.

SQLBackup v 4.6X

Please advise.

petey

Could you pls restart the SQL Backup Agent service? Do you also encounter the error if you run a backup manually via Query Analyzer?

rick.sheeley

We restarted the whole server Saturday night. That would include the SQLBackup agent.

We are still getting the 5240 errors, but not every time . I ran the SQl directly and it worked, no errors. But since it doesn't fail all the time, not sure that is significant.


Can I email you the two dumps I made? I have a dump showing the failing jobs and their frequency, plus a second dump of the acutal error messages.

petey

During the times when you encounter the error, is the SQL Server under a heavy load?

Yes, you can send the dumps to my listed email address.

rick.sheeley

Peter, did you get the dumps?

petey

Yes, I did get the logs, thanks. I was actually waiting for your reply as to whether the SQL Server was under a heavy load at the time of the errors.

The error indicates that the extended stored procedure (xproc) failed to receive a reply from the SQL Backup service that the command was received successfully. This can happen if the xproc sent the command to the service, but was never received, or the service received the command but failed to acknowledge within 5 seconds. The first scenario is unlikely as the xproc would know if the command failed to be sent, while the second scenario may happen if the server was under a heavy load.

I have prepared a version of the xproc that uses a timeout of 30 seconds instead of 5 seconds. Could you pls try using this copy and see if it addresses the issue? You can download it from http://www.yohz.com/downloads/xp_sqlbackup.zip.

The xproc is placed in the SQL Server binn folder. You need to run DBCC xp_sqlbackup(FREE) to first unload the DLL from the SQL Server address space, before replacing the file. On SQL Server 2000, this might not work everytime, in which case you can either

- stop the SQL Server service, replace xp_sqlbackup.dll, and restart the service

- copy xp_sqlbackup.dll to the binn folder but to a different name e.g. xp_sqlbackup_patch.dll, remove the reference to the previous file

sp_dropextendedproc sqlbackup

and add the reference to the new file

sp_addextendedproc sqlbackup, 'xp_sqlbackup_patch.dll'

rick.sheeley

SUre.I'll try it and let you know.