Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

single user mode - list connections

Posted on 2007-11-30
2
Medium Priority
?
1,056 Views
Last Modified: 2010-08-05
I am trying to move the location of my 'msdb' database.  I stopped the Agent service, Analysis service, reporting service and Fulltext service for this instance.

I started up my default instance of sql server 2005 instance in single user mode using the following:
net stop mssqlserver
net start mssqlserver /c /m /T3608

I then type SQLCMD at the prompt and get the following:
Msg 18461, Level 14, State 1, Server XXXXX, Line 1
Login failed for user 'yyyy\xxxx'. Reason: Server is in single user mode. Only one administrator can connect at this time.

I assume that something is connecting to this instance automatically.  How can i list / determine what it is?

thanks
0
Comment
Question by:jlingg
2 Comments
 
LVL 1

Accepted Solution

by:
nbraasch earned 2000 total points
ID: 20384099
Look under your processes under current activity, process info in EM.  You will see what is using msdb there.

Here is how you can kill all connections - I use it all the time for moving dbs.

Call it by installing it in master, then run  EXEC USP_KILLPROCESS "msdb"

CREATE procedure usp_killprocess @dbname varchar(128) as
set nocount on
set quoted_identifier off
declare @kill_id int
declare @query varchar(320)
declare killprocess_cursor cursor for
select a.spid from sysprocesses a join
sysdatabases b on a.dbid=b.dbid where b.name=@dbname
open killprocess_cursor
fetch next from killprocess_cursor into @kill_id
while(@@fetch_status =0)
begin
set @query = 'kill ' + convert(varchar,@kill_id)
exec (@query)
fetch next from killprocess_cursor into @kill_id
end
close killprocess_cursor
deallocate killprocess_cursor
GO
0
 
LVL 1

Author Closing Comment

by:jlingg
ID: 31411954
I appreciate the procedure to kill connections.  Very helpful.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question