Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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

Find out which Database uses how much RAM

share|improve this answer
    
I'm not following the relation between these questions, pun intended. (Yes, I checked the answers over there, no dice.) –  ioSamurai Jun 28 '10 at 17:45

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.