SQL Server: Single User Mode Basics

Taking DB into SINGLE USER mode:

For some maintenance situations, it is useful to obtain exclusive access to a SQL Server database. This can be achieved by setting the database's user access into single user mode, which permits only one connection to be made to the database at any time.

Limitations and Restrictions

  • 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.
  • The database remains in single-user mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

Prerequisites

  • Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. For more information, see ALTER DATABASE SET Options (Transact-SQL).

Check the AUTO_UPDATE_STATISTICS condition

exec sp_helpdb dbname


Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled

 
ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF


Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsFullTextEnabled



Finally to set a database to single-user mode
USE master;
GO

ALTER DATABASE dbname
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE dbname
SET READ_ONLY;
GO

ALTER DATABASE dbname
SET MULTI_USER;
GO

This example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of the 'dbname' database to READ_ONLY and returns access to the database to all users.The termination option WITH ROLLBACK IMMEDIATE is specified in the first ALTER DATABASE statement. This will cause all incomplete transactions to be rolled back and any other connections to the AdventureWorks2012 database to be immediately disconnected.

Test Case Scenario:
 http://www.mssqltips.com/sqlservertip/1247/kill-sql-server-process-ids-spids/


Source:

http://msdn.microsoft.com/en-us/library/ms345598.aspx
http://www.mssqltips.com/sqlservertip/1193/autoupdatestatisticsasync-sql-server-database-configuration/


Comments

Post a Comment

Popular posts from this blog

Restoring Master, Model and MSDB from Netbackup

Restoring System Databases got two parts: Taking the instance in single user mode and Restoring the Master Database If you need to rebuild the system databases on top of this you can use the below command: "C:\Program Files\Microsoft SQL Server\ 100 \Setup Bootstrap\ SQLServer2008R2 \setup.exe" /ACTION=REBUILDDATABASE /INSTANCENAME=SQL008 /SQLSYSADMINACCOUNTS=domain\ServiceUserName /SAPWD="yourpasswordOfSAuser" /SQLCOLLATION="Latin1_General_CI_AS" here 100 is for SQL Server 2008R2. It can vary case by case. How to take instance to single user mode: Two situation can happen: 1. Standalone Instance  2. Failover Cluster Instance 1. Standalone Instance: Go to SQL Config Manager SQL Service Properties -> Advanced Properties -> Startup Parameters put -m; in the Startup Parameters Apply and Okay - it will ask to restart the SQL Service to take effect. Stop and Start the service from Config Manager or you can stop and serv...
Read more

Oracle Job Operations

As a DBA, often I need to deal with Client's Jobs. Sometimes the jobs got hault, needs to break etc etc. Here are some useful commands for this sort of operations. ## Job Owner Schema can do this execute dbms_job.remove(444); execute dbms_job.broken(444, true); ## If Sys User needs to remove the job exec SYS.DBMS_IJOB.REMOVE(444); ## to find which jobs are running SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what FROM dba_jobs_running a, dba_jobs b, v$session c WHERE a.job = b.job AND a.sid = c.sid order by a.this_date ##let say we have only sid and serial and we need to kill the process from both OS and database. So first we need to find out the SPID. this query for any RAC database select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM, S.INST_ID, S.OSUSER, S.STATUS from gv$process p, gv$session s where P.ADDR=S.PADDR and S.SID=888 and s.serial#=9293; ## kill the job al...
Read more