Method to determine last connection source and date for a SQL Server 2005 Database?

Question

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?

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

Answer 2

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.

Answer 3

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

Find out which Database uses how much RAM