3

I have a C# scheduled task which runs every night and includes a kill command to drop an existing database so that it can be recreated. It randomly generates this error about once a week.

Kill database failed. User does not have permission to alter database 'Foo', the database does not exist, or the database is not in a state that allows access checks. Kill database failed.

The C# used is as follows.

srv.KillAllProcesses;
srv.KillDatabase;

So I am already trying to kill all of the processes. Are there any other checks or commands I could do before the kill command to ensure it happens successfully? It can't be a user permission problem as the operation works most of the time so what could be causing the "not in a state that allows access checks"?

Improve this question
2
  • When the weekly job fails, is the database in question still there or has it been dropped? If it's still there, does a subsequent run of the job successfully drop the database? Can you put Try/Catch/Retry logic in the C# process? Is there anything in the Sql Server error logs around the time of the failure?
    – Scott Hodgin - Retired
    Commented Aug 16, 2017 at 9:00
  • The database is still there, and yes unfortunately a re-run is successful so we can't find what is causing the problem overnight. We have tried some try/catch/retry logic. Currently this is done every 5 seconds 5 times. The SQL server logs just show the kills completing successfully and the database going into single user mode.
    – Hoody
    Commented Aug 16, 2017 at 9:25

1 Answer 1

Reset to default
7

When you execute srv.KillAllProcesses; SMO will just launch a KILL <spid> for every process connected to the database.

Once those kill commands are executed, some transactions may still be rolling back. I'm not sure what may be going on behind the scenes that puts your database in the state returning your error message when transactions are still rolling back but in any case that call isn't needed.

Calling only srv.KillDatabase; will execute a ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; before attempting to drop the database using DROP DATABASE, effectively killing/rolling back any active transactions.

It's the ALTER DATABASE statement that is returning the error you receive.

This can be verified by looking at a profiler trace or extended event target.

Have a look at Set a Database to Single-user Mode

If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

There is no reason to kill the processed first and you should probably remove that line of code

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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