SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Who is using a database		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sean_B
Posting Yak Master

United Kingdom
111 Posts
Posted - 11/11/2010 :  11:18:49  Show Profile  Reply with Quote
Hi,

I'm closing down a database and thought I could tell who was still using it using a trace, that was filtered on the database name.

However I find that if you are executing commands from a different database, then you can't filter the trace in this way. Also if someone is accessing tables in the database via a view from another database I can't see any information in the trace relating to my database.
Does anyone have an alternative to using the profiler for this activity, I'm looking at using DMVs, but I want something I can leave on for a period of time that will capture all activity in a specific database.

I noticed several other threads along this line but none that have an answer (so far).

Thanks

Sean

DaleTurley
Yak Posting Veteran

United Kingdom
76 Posts
Posted - 11/11/2010 :  12:09:25  Show Profile  Reply with Quote
Not sure if it answers your question properly.

SELECT * FROM master.dbo.sysprocesses
WHERE dbid = DB_ID('Database')

You can get login names, application names and spid from this "table"
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2040 Posts
Posted - 11/12/2010 :  04:42:53  Show Profile  Reply with Quote
exec sp_who will give the information for all processes



-----------

I used to think I wasn't a morning person but it never got better in the afternoon
Edited by - elwoos on 11/12/2010 04:44:50
Go to Top of Page

Sean_B
Posting Yak Master

United Kingdom
111 Posts
Posted - 11/12/2010 :  09:09:02  Show Profile  Reply with Quote
Thanks for your help guys.

Sean
Go to Top of Page

vaddesuresh
Starting Member

India
5 Posts
Posted - 11/15/2010 :  01:38:56  Show Profile  Reply with Quote
SELECT * FROM master.dbo.sysprocesses
WHERE dbid = DB_ID('Database')
or
exec sp_who
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000