How to determine which databases are used on SQL Server 2000

Question

I have a SQL Server 2000 field that hosts several databases, some of which are probably no longer in use. I would like to clean things by first unplugging them and then deleting them all together. The problem is that I do not know how to determine which ones are still actively used (external sources may or may not connect to them using them, etc.).

Is there any way to tell the time of the last action in each database? I know that SQL Server keeps records of some things in sys tables, but I'm not sure what is stored there. If what I need cannot be found there, is there something I can configure to track usage from now on? Ideally, I would like to see the use of “up to this point,” but “from now on” would be better than nothing. Thanks.

Answer 1

Try enabling auditing to log into SqlServer. Based on user accounts, you can see if this database is used or not. Audit can be found here:

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

This will fill in the logs that you can see in the /Management/SqlServerLogs , and this log is usually also saved here C:\Program Files\Microsoft SQL Server\MSSQL\log\ , so you can analyze and search through it.

Answer 2

As far as I know, there is nothing in SQL Server to give you the “last used” date / time. In the future, you can track usage by regularly performing such a request.

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