snippetsqlMajor
How can I tell if a SQL Server database is still being used?
Viewed 0 times
cansqlusedbeingtelldatabasehowserverstill
Problem
We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.
How can I tell if they are still being used by users or a web application?
I found a forum thread which had a T-SQL query you could run to retrieve the last query date. It seems to work but I want to know if this information is valid enough to drop databases. Is it?
If you have alternative methods that would help as well.
How can I tell if they are still being used by users or a web application?
I found a forum thread which had a T-SQL query you could run to retrieve the last query date. It seems to work but I want to know if this information is valid enough to drop databases. Is it?
If you have alternative methods that would help as well.
Solution
You would have to be concerned with items that have been purged from the cache and that you have missed, or for databases that have infrequent usage.
Rather than dropping the databases out of hand put them either OFFLINE to prevent access without dropping them or in RESTRICTED_USER mode to limit access. Doing this you can leave them in that state for a month or two to check and see if there is occasional usage.
You could also look to use a server side profiler trace filtering on that database.
Rather than dropping the databases out of hand put them either OFFLINE to prevent access without dropping them or in RESTRICTED_USER mode to limit access. Doing this you can leave them in that state for a month or two to check and see if there is occasional usage.
You could also look to use a server side profiler trace filtering on that database.
Context
StackExchange Database Administrators Q#2050, answer score: 33
Revisions (0)
No revisions yet.