August 1, 2008 at 12:03 pm
hey guys is there any system tables (column flags) that would indicate that a database is still in use?
August 1, 2008 at 12:19 pm
None that I am aware of although there may be something in the DMV's.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 12:22 pm
i was looking at system table sysperfinfo column 'active transaction'.
can this be used?
August 1, 2008 at 12:23 pm
No that I know of. You can set auto-close on databases you suspect are not being used and they'll close and log a message in the error log if they are opened.
August 1, 2008 at 12:53 pm
ok i just did what u said setting autoclose on, looking at the log i noticed a lot of "starting up database dbname" would this indicate that users or applications are still accessing the databases?
thanks
August 1, 2008 at 1:16 pm
Denby (8/1/2008)
ok i just did what u said setting autoclose on, looking at the log i noticed a lot of "starting up database dbname" would this indicate that users or applications are still accessing the databases?thanks
Someone is accessing them, either via SQL (backups or the like) or a custom app.
-- You can't be late until you show up.
August 1, 2008 at 1:20 pm
How about sp_who2?
August 1, 2008 at 1:32 pm
You could try this:
select ISNULL(s.login_name,s.nt_domain+'\'+s.nt_user_name) as Login_Nm,
DB_NAME(r.database_id) as Database_Nm,
r.status
from sys.dm_exec_requests r join
sys.dm_exec_sessions s on
r.session_id = s.session_id
Warning: This was lightly tested on my laptop's SQL 2K5 Dev instance. You may want to test it a bit before you deploy it.
I believe all of the databases returned are actively engaged by a session. You could modify the T-SQL to exclude system databases.
I hope it helps,
Ken
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply