January 1, 2009 at 2:54 pm
Does anyone have a script that checks an sql database for unused/inactive databases? We have alot of sql databases on several servers that we would like to get rid off if they are not being used.
January 1, 2009 at 10:03 pm
I am not sure if any information_schema maintain the last modified data for each database.
However, you can verify the timestamp on the following files of each database and sort it out.
.ldf and .mdf file of each database.
Please let me know if this help
January 1, 2009 at 10:31 pm
The script below should do the trick, but watch out for reindex jobs etc as they may show as user activity. I would still recommend setting up a server side trace for at least a few weeks if not a full month to check for user activity.
DECLARE@min_datedatetime
SELECT@min_date = GETDATE() - 2 --number of days to look back
SELECTDB_NAME(ius.database_id) AS database_name
FROMsys.dm_db_index_usage_stats ius
INNER JOIN (
SELECTdatabase_id,
COUNT(*) AS indexes
FROMsys.dm_db_index_usage_stats
GROUP BYdatabase_id
) dt
ON ius.database_id = dt.database_id
WHERE(ius.last_user_seek IS NULL OR ius.last_user_seek > @min_date)
AND (ius.last_user_scan IS NULL OR ius.last_user_scan > @min_date)
AND (ius.last_user_lookup IS NULL OR ius.last_user_lookup > @min_date)
AND (ius.last_user_update IS NULL OR ius.last_user_update > @min_date)
GROUP BYius.database_id,
dt.indexes
HAVINGCOUNT(*) = dt.indexes
January 2, 2009 at 2:35 am
Thanks alot David. That seems to do the trick for the 2005 servers. How can I get it to work on sql 2000?
January 2, 2009 at 8:36 am
The dmv's do not exist in SQL 2000 so there is not a real quick way that I have run across. Your best bet is to use profiler, removing databases from the trace as they turn up being used until you are not capturing any events in your trace. It is a more time consuming approach but you can do things like filter out maintenance jobs so it offers more flexibility.
January 2, 2009 at 8:44 am
A much quicker way if you can get away with it... 🙂
1) Send an e-mail out to everyone who has a good knowledge of your environment with a list of DBs you aren't sure about, telling them you are trying to clean up and are going to delete those databases unless you hear otherwise.
2) Wait a few days.
3) BACKUP those databases, and either detatch them or take them offline.
4) Wait for phone to ring. If one of the DBs you detatched was being used, you'll find out in short order! :w00t:
It's a dirty trick, but it works! 😛
The Redneck DBA
January 2, 2009 at 8:58 am
Here is another method for SQL 200 databases.
Set them to auto-close, then check the errorlog !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 2, 2009 at 9:04 am
We have a job that runs every couple of minutes on all servers and populates table with the following information: LoginName, HostName, DatabaseName, CreateDT, LastModifiedDT, etc.
From this we can see which databases are no longer being used.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply