Home Dashboard Directory Help

Maintain Connection after SET SINGLE_USER by Chuck_Wessel


 as Not Reproducible Help for as Not Reproducible

Sign in
to vote
Type: Suggestion
ID: 767231
Opened: 10/12/2012 11:20:39 AM
Access Restriction: Public


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.
Sign in to post a workaround.