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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

See the following knowledge base article.  This is precisely what you are describing.

sp_dropdevice gives redundant and incorrect information:


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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.