Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This is a duplicate of the question I asked on stackoverflow, but I was advised, that someone here could have a better idea what is happening.

I have a sporadic problem, when upgrading SQL Server in a single-user mode, using .NET SqlConnection, some other application somehow logs into the database, while the SQL code is being executed, and kicks my process out. SqlConnection is not closed or disposed in any way. But some other application somehow ends up connected to the database and that kicks my connection out.

When I run sp_who, I could see that a process that took control of the database is Command="TASK MANAGER".

Anyone could tell me what is this process, what is it's purpose, and how in the world it could get into a database, which is in single-user mode, and there is an active connection?

share|improve this question
    
Do you shut down SQL Agent during this process? The tasks named "TASK MANAGER" (with low spid numbers) are internal processes. – Jon Seigel Jul 11 '12 at 16:45
    
@JonSeigel no, I didn't. Are you saying SQL Agent is an actual process that runs TASK MANAGER? – galets Jul 11 '12 at 16:51
    
I'm not 100% sure. What I do know is that SQL Agent connects to the instance when it's running, and this can prevent you from logging in while the server is in single-user mode. I had that problem yesterday, and stopping SQL Agent fixed it. – Jon Seigel Jul 11 '12 at 16:58
2  
Just to be clear, this isn't the same Task Manager that you use in Windows to view processes and system performance. – Aaron Bertrand Jul 11 '12 at 17:22

Had the same issue today, If you have not put your AUTO_UPDATE_STATISTICS ASYNC OFF, you will not be able to enter your database, you can solve this by putting your database offline. Important to know is that you need to set your deadlock priority to high, otherwise you will be deadlocked out of the command. Use following commands to get out of SINGLE user mode

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET OFFLINE WITH ROLLBACK IMMEDIATE

Followed by

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET ONLINE WITH ROLLBACK IMMEDIATE

Followed by

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
share|improve this answer

Stop the "system_health" extended events trace. It will be listed under

SQL Server Management Studio
-> [ServerName]
-> Management
-> Extended Events
-> Right-Click on 'System_health'
-> Hit Stop Session

Once the blocker are fixed, restart the session.

share|improve this answer
    
Although the other answer explains why this happens, this one explains how to resolve it. – Boris Callens Feb 10 at 15:31
up vote 4 down vote accepted

I think the mystery finally got resolved:

Before you set the 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.

share|improve this answer

You have to disable SQL Agent 'before' starting single use mode. As the agent accout will grab the single user access. Remeber the single user is not you it the first user/process to connect.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.