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

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Who is using a database

Author  Topic 

Sean_B
Posting Yak Master

111 Posts

Posted - 2010-11-11 : 11:18:49
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

76 Posts

Posted - 2010-11-11 : 12:09:25
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
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-12 : 04:42:53
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
Go to Top of Page

Sean_B
Posting Yak Master

111 Posts

Posted - 2010-11-12 : 09:09:02
Thanks for your help guys.

Sean
Go to Top of Page

vaddesuresh
Starting Member

5 Posts

Posted - 2010-11-15 : 01:38:56
SELECT * FROM master.dbo.sysprocesses
WHERE dbid = DB_ID('Database')
or
exec sp_who
Go to Top of Page
   

- Advertisement -