October 7, 2003 at 12:24 pm
Hello,
Here is an error I'm receiving while trying to restore a Database:
"Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally."
This is a test database and all EM and QA sessions on the developers computers are not running. Also, there are not any background processes running -- that I know of.
Could IIS have a hold of the DB even though no Users are logged into the Web Application that uses this DB?
Also, is there a way to find out who/what is using the DB and force them/it off?
Thanks in advance for your time,
Steve DiDomenico
Nashua, NH
October 7, 2003 at 12:26 pm
Run sp_who2 to see who are using that datbase.
October 7, 2003 at 1:03 pm
You need to put the database into SINGLE_USER mode if it already exists.
ALTER DATABASE db_name SINGLE_USER WITH ROLLBACK IMMEDIATE
WITH ROLLBACK IMMEDIATE, immediately rolls back any on-going transactions and kick's everyone out except for the user running the command.
-SQLBill
October 14, 2003 at 2:22 pm
Is there a way to force off a User or Kill a process?
On the day I posted this question I received two responses.
1)I tried sp_who2. This tells me who was on the SQL Server, and I was the only one.
2)Before putting the database into Single_user mode I wanted to make sure I could put it back into Multi_user mode. While I was looking for the command to put the database back into multi_user mode I tied the Restore one more time and it worked. I'm not sure what caused the database to free up.
October 15, 2003 at 8:05 am
quote:
1)I tried sp_who2. This tells me who was on the SQL Server, and I was the only one.
You need to switch to any databases, for example master, before you run sp_who2 to see wehether any users are using your database. Kill them and start the restoration. You really don't have to place your database in single user mode as long as there are no users accessing it before you start the restoration.
October 15, 2003 at 11:02 am
The KILL command. Find the user(s) on the database (sp_who2 as already suggested). That will give you the SPID. Then run:
KILL <spid>
If you want to kill SPID 53, you would run:
KILL 53
Refer to the Books OnLine, use the Index tab and enter KILL.
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply