Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2052 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  
 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.02 seconds. Powered By: Snitz Forums 2000