How to determine which databases are being used on SQL Server 2000

Question

I have a SQL Server 2000 box that houses several databases, some of which are probably no longer in use. I'd like to clean things up by first taking them offline, and then later removing them all together. The problem is that I don't know how to tell which of these are still being actively used (outside sources may or may not be connecting to them, using them, etc.)

Is there a way to tell the time of the last activity on each database? I know that SQL Server keeps records of some things in sys tables, but I'm not sure what exactly is stored there. If what I need cannot be found there, is there something I can set up to track usage from this point forward? Ideally, I'd like to be able to see usage "up to this point in time", but "from this point forward" would be better than nothing. Thanks.

Answer 1

As far as I know, there's nothing built in to SQL Server to give you a "last used" date/time. Going forward, you could track usage by running a query like this on a regular basis.

select db_name(dbid), count(*) 
    from master..sysprocesses 
    group by db_name(dbid)
    order by db_name(dbid)

Answer 2

Try turning on auditing for logging into SqlServer. Based on user accounts used, you can see if that database is used or not. The auditing can be found here:

EnterpriseManager -> Right click database server -> Properties 
-> Security -> Audit Level -> set to All

This will fill up the logs that you can see under /Management/SqlServerLogs and that log is typically also saved in here C:\Program Files\Microsoft SQL Server\MSSQL\log\, so you could parse & search through it.