1

I wrote a stored procedure to loop through my databases in a cursor, restore the most recent backup, run checkdb, drop the restored database, then move on to the next one. I have found that during the process, there are several databases restored at any given time, which uses too much disk space for this to work on my larger databases. When the procedure completes, all the databases are dropped and it does not leave anything behind. I have checked and double checked that I have steps inside the cursor to drop the database, but it does not help.

I have this code inside the cursor:

SET @DropCommand = 'IF DB_ID(''' + @CursorRestoreName + ''') IS NOT NULL
BEGIN
    ALTER DATABASE [' + @CursorRestoreName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [' + @CursorRestoreName + '];
END';

EXEC sp_executesql @DropCommand

I added a PRINT @DropCommand statement as well to verify this code block is being executed, and it is.

Since that wasn't working, I added another cursor inside my first cursor to go through the full list of databases to attempt the drop command, to clean up previous restores. This still has the same effect: multiple database restores exist on the server at a given time during the process, then at the end all have been dropped.

Do you have any recommendations that I could implement so that the restored database is actually dropped before moving on to the next restore? Or perhaps suggestions for troubleshooting this?

  • 3
    Change the SET SINGLE_USER WITH ROLLBACK IMMEDIATE to SET OFFLINE WITH ROLLBACK IMMEDIATE. No need to add any wait. This will work 100%. If you have single user mode and if a background process like sql agent thread connects to your db, your drop will fail or you will encounter blocking. – Kin Shah Sep 12 at 17:28
  • @KinShah it usually works, but there are times where your deadlock priority prevents it, so I always SET DEADLOCK PRIORITY 10 first too. – scsimon Sep 12 at 18:49
3

What's probably happening is that another session is connecting to the database after you set it to SINGLE_USER. Always connect to a database before you set it to SINGLE_USER, or you can't guarantee that you will be the single user. But you can't drop a database you're connected to, so SINGLE_USER is just not the right state.

While setting the database OFFLINE will work, be aware that dropping an OFFLINE database does not delete the database files. Then you won't be able to RESTORE because the database files are hanging around. And you don't have rights to delete database files, by default, and shouldn't really need them.

RESTRICTED_USER is probably the best state to put a database ahead of dropping it. eg

RESTRICTED_USER

Allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database. RESTRICTED_USER doesn't limit their number. Disconnect all connections to the database using the timeframe specified by the ALTER DATABASE statement's termination clause. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017

use master
alter database foo set restricted_user with rollback immediate
drop database foo
  • I will try using RESTRICTED_USER instead of SINGLE_USER to see if it helps. I don't think the issue is that someone else is stealing the single connection, though. The database still drops, it just waits a long time to do so. So the drop command is getting sent. – SQLDoug Sep 12 at 21:17
  • I tested this out and the databases are still sticking around until the end of the procedure. They are not being dropped before the next database restores. – SQLDoug Sep 12 at 21:27
  • Can you add a complete repro to your question? – David Browne - Microsoft Sep 12 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.