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.