Why is my auto-restore procedure not dropping the databases when I expect it to? 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?
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