• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1061
  • Last Modified:

Why does randomly a call to sp_dropdevice cause a SQL Server error?

I have a SQL Server Agent job that runs every 20 minutes and it's used for a custom log shipping solution. One of the job steps is responsible for checking if we already have a backup device and if we do drop it. It then creates a new backup device.

What is happening is that randomly throughout the day the drop device stored procedure call is causing the following error.

Message for the database fundmgmtdev from RBDPGPSQL1 to CHIBGPSQL1. Executed as user: RBDPGPSQL1\datarite. Logical device 'fundmgmtdev_logDump' already exists. [SQLSTATE 42000] (Error 15026)  Device dropped. [SQLSTATE 01000] (Error 15463).  The step failed.

Keep in mind that every time the job runs, we always drop the device and recreate it but for some reason a handful of times throughout the day sp_dropdevice causes a SQL Server error and our job step fails. Another weird thing is that the job step continues to execute all of the way until the end (I know because I have messages that are written to the table) so it's not until the job step completes that the error message is shown and causes the job to fail.
0
nyphalanx
Asked:
nyphalanx
1 Solution
 
SQLSharkCommented:
See the following knowledge base article.  This is precisely what you are describing.

sp_dropdevice gives redundant and incorrect information:

http://support.microsoft.com/kb/93340/en-us

The job is actually completing correctly, because the information being reported by sp_dropdevice is incorrect.  The SQL Server Agent reports a failure because sp_dropdevice incorrectly reports a failure, but your home made job log tells the real story.  The job is completing correctly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now