December 19, 2012 at 12:21 pm
I would like to find out some databases not used any more on a testing server.
My network person asked to look at those mdf and ldf files that showing the older modified date.
But I don't think that will be accurate, because these databases are still in backup every day.
Does backup process write things to the databases when it is done?
But I do find some mdf files that have modified date is in older dates even they are included in backup maintenance plan everyday.
Thanks,
December 19, 2012 at 3:27 pm
I wouldn't look at the Date Modified of the .mdf or .ndf files.
You might try something like this that will show you when the tables in the database were last read from or written to:
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,2;
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
December 19, 2012 at 4:01 pm
The index stats are cleared out when the server is restarted, so if you have done that, this isn't accurate.
The index stats are a good place to start, however. What I might do is look for databases with no recent index stat changes and then take them offline. Might cause your phone to right, but it will help you easily determine which ones aren't being used and you can bring them online very quickly.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 19, 2012 at 8:16 pm
J Good's code most of the time is useful, because the service often won't restart, but in order to insurance, we need to monitor the server if there is a client request.
The index statistics will not be updated by default if the table had not enough data insert/update/delete. So I think this way is not good.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply