MASTER, MSDB and TEMPDB cannot be set into single user mode. For this you need to start the SQL SERVER into single user mode.
A query that initiates an update of out-of-date statistics must wait for those statistics to be updated before compiling and returning a result set. This can cause unpredictable query response times and may cause applications that have aggressive time-outs to fail.
In SQL Server 2005, the database option AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to ON, queries do not wait for the statistics to be updated before compiling. Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan. Queries that start after the updated statistics are ready will use those statistics. This may cause the recompilation of cached plans that depend on the older statistics version. Updating asynchronous statistics cannot take place if any data definition language (DDL) statements, such as CREATE, ALTER, and DROP statements, occur in the same explicit user transaction.
Before you set a database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. If the option is set to ON, perform the following tasks:
1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.
Alter database AdventureWorks set AUTO_UPDATE_STATISTICS_ASYNC OFF
2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.
select * from
sys.dm_exec_background_job_queue
3. If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.
This example shows how to terminate the statistics update associated with a job where the job_id = 53.
KILL STATS JOB 53;
GO
1.use master
go
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'AdventureWorks'
go
2.kill 51
3.sp_dboption 'testdb01', 'single user', 'TRUE'
or
USE master;
GO
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
PS:
the termination option WITH ROLLBACK IMMEDIATE in the first ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the AdventureWorks sample database will be immediately disconnected.
