February 27, 2008 at 10:03 am
My company has given me a SQL 2000 Server to support. YA!
The previous person that supported this SQL Server did no documentation to what databases connected to what application. Nor did he document what database are no longer used...
So we have a bunch of databases taking up resources that can be possibly archieved.
Is there a way to identify what database are still used / connected to?
Possibly by a timeframe? Meaning, can I query to see what databases have been connected in the last month, 3 months, 6 months..etc?
Thanks,
TeeKay
February 27, 2008 at 10:14 am
I am in a similar position, at a new company, and we are attempting to consolidate servers. I have identified that (from asking) 3 databases on a particular server are being used. Nobody seems to know about the other 10. I'm running Profiler to capture logins and plan on letting in run for a couple of weeks. If I don't see anyone accessing the other databases, I plan on shutting down the SQL service and see who complains. No complaints in a reasonable time, I'll backup the databases and shutdown the server. Luckily, this is a dev box. Curious to see what other suggestions come up as I will eventually need to do the same thing for a production box.
-- You can't be late until you show up.
February 27, 2008 at 10:33 am
If you did not audit, I do not think you can.
You can set the database options to AUTOCLOSE. Then you will know when a database is connected from SQL logs. The side effect is that it will affect performance.
February 28, 2008 at 5:28 pm
Query the sysdatabases to find which databases is currenly running with users. You can also find the database not being used by tracing the activity.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply