Indeed! it is simple with the following command:
a. Alter Database Out set Single_User
b. Alter Database Out set Multi_User
If we don't mention any termination clause like above it will run until the statements get completed.
Suppose there are n numbers of users connected to the Database and you executed the above command it will take hell lot of time to complete.
So rather, you can force disconnect the users to put the Database in Single User mode you have to fire the below command:
Alter Database out set Single_user with Rollback After 30 -- After 30 Seconds it will cancel and Rollback the Query
Alter Database out set Single_user with Rollback Immediate -- It will immediately cancel and Rollback the Query
Alter Database out set Single_user with No_Wait -- If there is any incomplete transaction No_Wait will Error
But again it might get horror if the Databases is in Single User and you cannot access the Database because only one connection can be made at a time and just think that connection is taken by the system i.e. SQL Server.
In this situation you are locked out, reason you cannot access the Database. Like you can see in the snapshot the Database Out is used by the system i.e. it is used by the Background process.
If you try to bring back the Database again in Multi User mode system will throw the following error:
After loads of struggle we were back to square one, that our Database was not getting back to Multi User mode. Seems it was like a deadlock between System SPID with Out Database. So we enabled the trace flag and checked the Error Log file. So following snapshot confirms that there was an deadlock:
DBCC TRACEON (1204,1222,-1)
After random tries we tried the following command and it saved us. We have to set the deadlock priority high and then execute the Multi User mode query like below:
Set Deadlock_Priority High
Alter Database Out Set Multi_User
So what this will do is it will set the Dead Lock priority High and Alter the Database to Multi User mode.
Guys do share the feedback about this article and of course about the blog too.
Want to start learning SQL Server Clustering?? Check here the three part series on SQL Server Clustering.
Do you know MS SQL Server 2016 is ready to launch?? Check here the two part series on New Features of SQL Server 2016.
Keep Learning and Enjoy Learning!!!