Solved

single_user mode - database locked

Posted on 2006-11-13
27
750 Views
Last Modified: 2008-02-01
Please help...

I set my database to single_user mode to restore the database this morning.  The db was successfully set to single_user mode, but I cannot restore the database and it shows that there is one connection open to the database.  I ran sp_who and it shows my login, but I've closed query analyzer and I don't see where I have a connection still open????

Can anyone help?  I can't do anything now to the database because it is in single_user mode.

Thanks for any help.
0
Comment
Question by:-Dman100-
  • 10
  • 10
  • 5
  • +2
27 Comments
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
You probably have it open yourself with Enterprise Manager. Close Enterprise Manager, then re-open, right-click the database and select restore.
0
 
LVL 20

Expert Comment

by:Sirees
Comment Utility
If someone else using the database, while you are changing to single-user mode then you will not be able to get to that database.

If re-opening the EM does not work,

you can switch it back to Multiuser mode using the command ALTER DATABASE <database> SET MULTI_USER

Then change the options to "Members of db_owner, dbcreator, or sysadmin" and restore the database.

0
 

Author Comment

by:-Dman100-
Comment Utility
thanks for replying to my post.

I tried both methods.  I completely closed SQL Server and re-opened.  Still couldn't restore the database (single connection to the db)

Tried re-setting to multi_user mode...wouldn't let me. (single connection to the db)

I even tried stopping and re-starting SQL Server.  Still no dice?

I'm lost?  I've done this countless times before and never had a problem.
0
 
LVL 11

Expert Comment

by:regbes
Comment Utility
Hi -Dman100-,

go to the server physicly

disable the network

in EM stop SQL

Start SQL

put DB in multi user

0
 
LVL 11

Expert Comment

by:regbes
Comment Utility
-Dman100-,


oops forgot to say

finaly re enable network
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
run sp_who2, and see what application is connecting to that database
0
 

Author Comment

by:-Dman100-
Comment Utility
Hi regbes,

The server is located remotely, which I cannot access physically.  I can call one of our IT guys and have them do it.

One question?  How do I disable the network?

this server does serve other applications, so I'm assuming this will cause a temporary disruption for those applications if we disable the network, stop and restart?

I know how to stop and restart the server, but didn't see anything for disabling the network.
0
 

Author Comment

by:-Dman100-
Comment Utility
I ran 'sp_who2'

the program name that is connecting to the db is .NET SqlClient Data Provider

That is related to the class file I built to generate a database driven menu system.
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
Then stop the application that is using that class. That is holding an open connection to the databse.
0
 
LVL 11

Expert Comment

by:regbes
Comment Utility
-Dman100-,
> How do I disable the network?
in your control panel -> network connections right click  and select disable once done right click enable
0
 
LVL 11

Expert Comment

by:regbes
Comment Utility
-Dman100-,
> so I'm assuming this will cause a temporary disruption for those
> applications if we disable the network, stop and restart?
 Yes :(
0
 
LVL 21

Expert Comment

by:mastoo
Comment Utility
Why not just kill the connection from Enterprise Manager?
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
or query analyser
to kill a process run
KILL spid
in query analyser (of course, a half-decent client application will simply reconnect, so consider shutting the application down if this doesn;t work)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:-Dman100-
Comment Utility
I've stopped the application using the class.

I tried killing the process by running KILL 66 (which is the id of the process).  It did remove that process, but immediately attached a new id (54) to the process that is holding the connection, which doesn't make sense because I stopped the freaking application?

I tried killing that ID but it doesn't seem to kill the process?

Could something be held in cache?

they don't want to disable the network at least not during the business hours, which I understand.  I'm fumbuzzled?
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
Can you see the HostName of the application? This will at least identify the server.
Can you see the Login of the application? This might help you identify the user.
Also, well-written client software will simply reconnect (hence the new spid).

This will not be held in cache.
0
 
LVL 11

Expert Comment

by:regbes
Comment Utility
-Dman100-,

what user is connecting ? disable that login then kill the process this should stop it connecting again.

you can now put it back to multi user then re enable the login
0
 

Author Comment

by:-Dman100-
Comment Utility
Can you see the HostName of the application? Yes, it is the server where my application resides, which I have currently stopped.

Can you see the Login of the application? Yes, it is my login name.

Also, well-written client software will simply reconnect (hence the new spid).  But, would a new id get generated even when I have the application stopped?

The process references a stored procedure that uses polling and triggers : dbo.AspNet_SqlCachePollingStoredProcedure
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
That looks like an ASP.NET connection. Can you stop the web service?
If you are using impersonation on the web server, and you have an active session, this will reconnect as your login name. The application is definitely still running.
0
 

Author Comment

by:-Dman100-
Comment Utility
Hmmm, well I have been rummaging around trying to find the web service to shut down, but have been unsuccessful.

It looks like the stored procedure dbo.AspNet_SqlCachePollingStoredProcedure is using some triggers that are holding the connection.  If I right,  I need to disable the trigger, but I can't get to it because I'm in single_user mode <ugh>.

So,iIs there a way I can simply drop the database altogether and then just re-create the db and restore it?

Since it is in single_user mode, I haven't been able to drop the database.  Is there a way I can force the db to drop?

0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
Well, you good always restart the server reported in the HostName (or simply stop IIS on that server). I suspect that would make you unpopular though.

Alternatively, you can try detaching the database (there is an option on detach to terminate existing connections when you do this) and then restore the database from backup.
0
 

Author Comment

by:-Dman100-
Comment Utility
You don't happen to know the option to terminate the existing connections when detaching?  I was looking but don't see anything?  I tried to do a simple detach and of course it won't let me because of the connection.
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
What version of SQL Server? 2000 or 2005?
0
 

Author Comment

by:-Dman100-
Comment Utility
SQL Server 2000
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
Comment Utility
After you select the database and select detach, you should be prompted with a small form - it also shows you the number of current connections. You can click the Clear button to terminate these.
0
 

Author Comment

by:-Dman100-
Comment Utility
Okay, that indeed did detach the database.  So, is it stored as a text file on the server?  I can now just re-create my database and then restore from my backup?
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
The .mdf and.ldf files are still there (binary, not text).

You don't have to recreate - you can simply restore from the backup as the same database name.
0
 

Author Comment

by:-Dman100-
Comment Utility
Thanks Nightman...that finally allowed me to resolve the problem.  I ended up having to delete the .mdf and .ldf files and then recreating the database.

For some reason, when I was trying to restore the backup as the same database name, it kept locking up on me?

So, I finally just deleted the .mdf and .ldf files, recreated the db and restored.

Geez, what a mess.

I've got to isolate the cause that is holding that one open connection.

Thanks again for your help!
Regards,
-D-
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now