Maintain Connection after SET SINGLE_USER - by Chuck_Wessel

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 767231 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 10/12/2012 11:20:39 AM
Access Restriction Public

Description

Good DBA’s prefer to script operations – even “one-time” actions for repeatability (across systems) and to serve as a record of what was performed.

There should be a way for DBA's to script administrative tasks which require SINGLE_USER mode and guarantee that the (single) session isn't immediately stolen by a competing process.  We should have a mechanism for coding a block which places the database into single_user mode and then performs additional actions without the possibility of losing control of the db to another process.
Sign in to post a comment.
Posted by Microsoft on 2/19/2013 at 3:32 PM
Hi, I am resolving this issue for now. If you are still hitting this problem having turning off the async stats update and killing the stats job, please re-open or file a new Connect bug.
Posted by Microsoft on 1/25/2013 at 4:18 PM
Hello, have you tried having your script run "ALTER DATABASE <database_name> SET AUTO_UPDATE_STATISTICS_ASYNC OFF"? This should be the only built-in automated process that will try to grab the connection.

You may also have to use KILL STATS JOB (http://msdn.microsoft.com/en-us/library/ms180016.aspx) and query sys.dm_exec_background_job_queue for any running statistics jobs.