So I'm tasked with looking at this old database server to see if any of the databases are in use so we can shut it down. I have a few databases with unidentified usages, if I can somehow see a log or connection history I could check the IP and see the source of the application that is using it. Any ideas?
Asked
Active
Viewed 1,775 times
3 Answers
1
SQL2005 by default only logs failed connections (bad username, etc) and not successful ones. You can use the activity monitor to see any currently active connections, but that's not retro-active.
What we usually do is switch the databases over to READ-ONLY/RESTRICTED ACCESS mode (only DBAs can access the database), and see who starts complaining :)
![](../../users/profiles/530.webp)
BradC
- 2,200
- 4
- 25
- 35
0
This will not show you who is querying the database, but this script should show you which databases have been accessed since the last time the server was rebooted:
SELECT db_name(database_id) as DBName,
max(last_user_seek) as last_seek,
max(last_user_scan) as last_scan,
max(last_user_lookup) as last_lookup,
max(last_user_update) as last_update
FROM sys.dm_db_index_usage_stats
Group By db_name(database_id)
Order by db_name(database_id)
(SQL 2005+ only)
Run a SQL trace filtered by DatabaseID to identify the hostname and username of who is actually doing the querying.
![](../../users/profiles/530.webp)
BradC
- 2,200
- 4
- 25
- 35
-1
This post might get you pointed in the right direction!!
-
I'm not following the relation between these questions, pun intended. (Yes, I checked the answers over there, no dice.) – MetaGuru Jun 28 '10 at 17:45