?
Solved

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

Posted on 2008-11-15
1
Medium Priority
?
1,040 Views
Last Modified: 2012-05-05
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
Comment
Question by:nyphalanx
1 Comment
 
LVL 2

Accepted Solution

by:
SQLShark earned 2000 total points
ID: 22967980
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question