Exit Code 5240 after clearing old backup history

rick.sheeleyrick.sheeley Posts: 32
edited March 1, 2007 11:12PM in SQL Backup Previous Versions
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.
Eric (Rick) Sheeley, Sr. SQL/Oracle DBA
Sacramento, CA Cell: 602.540.6750
"Those are my principles, and if you don't like them... well, I have others." - Groucho Marx

Comments

  • peteypetey Posts: 2,358 New member
    Could you pls restart the SQL Backup Agent service? Do you also encounter the error if you run a backup manually via Query Analyzer?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
    Eric (Rick) Sheeley, Sr. SQL/Oracle DBA
    Sacramento, CA Cell: 602.540.6750
    "Those are my principles, and if you don't like them... well, I have others." - Groucho Marx
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Peter, did you get the dumps?
    Eric (Rick) Sheeley, Sr. SQL/Oracle DBA
    Sacramento, CA Cell: 602.540.6750
    "Those are my principles, and if you don't like them... well, I have others." - Groucho Marx
  • peteypetey Posts: 2,358 New member
    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'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • SUre.I'll try it and let you know.
    Eric (Rick) Sheeley, Sr. SQL/Oracle DBA
    Sacramento, CA Cell: 602.540.6750
    "Those are my principles, and if you don't like them... well, I have others." - Groucho Marx
Sign In or Register to comment.