Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

How to tell who is using a certain db ?

Hi Experts,

I need to restore a Sandbox db. But during restoring I got an error saying the db is in use by others and can not go any further. The problem is, how to tell who is using a certain db??
Thanks.
0
Castlewood
Asked:
Castlewood
3 Solutions
 
ragnarok89Commented:
run the query

sp_who
0
 
CastlewoodAuthor Commented:
Ok, I got the username who uses this db. The status shows 'sleeping'. So is there anyway to cut off the connection?
0
 
Daniel_PLDB Expert/ArchitectCommented:
Note session spid number (e.g. from sp_who2) and use:
kill <spid number>
0
 
bitrefCommented:
You can monitor the processes using the database and kill them using the Activity Monitor. You can find its icon in the Standard Toolbar of SQL Server Management Studio.
0
 
Daniel_PLDB Expert/ArchitectCommented:
When you are restoring database you can automatically remove activity from your database by altering database state to single user:
USE MASTER
GO
ALTER DATABASE <database name>
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE <database name>
FROM DISK =N'<path to backup file>'
GO

Open in new window

0

Featured Post

Industry Leaders: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now