Tell me more ×
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.
up vote 2 down vote favorite

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

2 Answers

up vote 0 down vote

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
up vote 0 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

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.