Dear Friends,
Click here to check how to start the SQL Server without TempDB Database. Let's learn what to do or how simple it can be to change the Database Mode from Multi User to Single User Mode or vice-versa?
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:
Another possibility you can try would be detaching the Database. But when I tried detaching the Database, SQL Server will first kill the connections to the Database. Rather I should frame it as SQL Server will kill only the User connect and not the system connection.
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)
If we try to Alter the Database to put it in Multi User mode. We will get a deadlock and since our Alter Statement is having low priority it will fail with the error message:
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
Go
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!!!
Indeed! it is simple with the following command:
a. Alter Database Out set Single_User
b. Alter Database Out set Multi_UserIf 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:
Another possibility you can try would be detaching the Database. But when I tried detaching the Database, SQL Server will first kill the connections to the Database. Rather I should frame it as SQL Server will kill only the User connect and not the system connection.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)
 |
| Deadlock Graph |
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
Go
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!!!
