1

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?

MetaGuru
  • 896
  • 6
  • 22
  • 36

3 Answers3

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 :)

BradC
  • 2,220
  • 4
  • 27
  • 36
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.

BradC
  • 2,220
  • 4
  • 27
  • 36
-1

This post might get you pointed in the right direction!!

Find out which Database uses how much RAM

Community
  • 1
Danbo
  • 34
  • 1
  • 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