When was my SQL Server Databases last accessed/used?

[tnguyen315]

Please help. I have SQL server 2000 and trying to migrate to either 2008 R2 or 2012. However, I have tons of databases in SQL Server 2000.
Is there any sample script that i can use to find out the last accessed date of the SQL Server databases?
I need to know if the databases are in use or if they are old and can be removed.
What is the best way to find out if databases are still active on my SQL Server?
Thanks in advance.
TN

 

[TheBugSlayer]

Nguyen,

SQL Server 2000...that was a long time ago. I did a little research and I believe the way to go is to enable user login auditing then looking into the logs...The latter part would still be a manual process. You could also try to set to database OFFLINE and see who complains, but how long do you wait before you are satisfied is the question. In any case, if you are not certain, DO NOT DELETE the databases: set them offline or detach them.

Should you find a script to do this, you can use the sp_msforeachdb and sp_msforeachtable stored procedures to execute a script on all databases on a server, or all tables in a database. See an example here.

Good luck to you.



MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).