Sql-server – CHECKPOINT stealing connection in SINGLE_USER mode

sql-server-2008

I have a console application that was written to automatically run several long running scripts. Before running the scripts, it will take a database backup by using the BACKUP DATABASE command. It will then place the database in single user mode:

ALTER DATABASE databaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

The application will then begin running the sql scripts. This part all works great. The problem that I am having is if a script fails, we are doing a restore from the backup taken before beginning the scripts. The reason for this is because a transaction rollback will take far longer than simply doing a restore.

I've tried a few different ways:

-works but periodically gets its connections stolen

USE master RESTORE DATABASE databaseName FROM DISK=N'fileLocation'

-doesn't work, database already in use

EXEC master..sp_executesql N'RESTORE DATABASE databaseName FROM DISK=N''fileLocation'''

I've also tried using the .ChangeDatabase() method of the SqlConnection object (VB.NET). This behaves much the same as the first script above in that it periodically will get it's connection stolen.

In code I am taking pains to ensure I never drop/close my connection and watching the Activity Monitor, it appears that the CHECKPOINT process is what is stealing my connection.

The errors I'm seeing are these:

Database 'databaseName' is already open and can only have one user at a time.

ALTER DATABASE failed because a lock could not be placed on database 'databaseName'. Try again later.

Cannot open database "databaseName" requested by the login. The login failed.

Is there a way to keep CHECKPOINT from stealing my connection?

Best Answer

Your script is failing on the ALTER DATABASE not on the RESTORE. Try setting it to RESTRICTED_USER first:

EXEC('
USE master;
ALTER DATABASE '+@db_name+' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
USE '+@db_name+';
ALTER DATABASE '+@db_name+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE master;
DROP DATABASE '+@db_name+';
');

This script so far has never failed me.

There are two differences to your script. The main difference is that setting the database to RESTRICTED_USER first allows the script to get executed even if the database is in SINGLE_USER to start with.

The second is that it drops the database instead of restoring it. You could try to replace the DROP with your restore directly, but that might open a new "gap" for something else to sneak in. As you are restoring from a file and not from the database-backup-history, dropping the database first should not be a problem for you. Just execute the restore after the database is gone.

Related Question