How do I avoid a deadlock while setting read committed snapshot off in SQL Server?

1k views Asked by At

For a testing scenario, I need to set read committed snapshot off on an SQL server instance. After the test I need to revert the changes. I use an IF statement to determine which action to perform. Here is the script that I use:

USE [master]
GO

IF 'Mycondition'
BEGIN
    ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE [MyDB] SET READ_COMMITTED_SNAPSHOT OFF WITH NO_WAIT
    ALTER DATABASE [MyDB] SET MULTI_USER WITH NO_WAIT
END
ELSE
BEGIN
    ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE [MyDB] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
    ALTER DATABASE [MyDB] SET MULTI_USER WITH NO_WAIT
END
GO

However, upon running this script, there seem to be deadlocks intermittently. Sometimes the operation gets through, while some other times, a deadlock occurs and my database is stuck in single user mode. Is there anyway I can make sure that does not happen? I considered removing the WITH NO_WAIT clause but that didn't seem to help.

1

There are 1 answers

0
Anoop R Desai On BEST ANSWER

Looks like I had missed something when I had removed the WITH NO_WAIT clause. It works fine when I remove it.