October 22, 2008 at 4:30 pm
Before dropping a database, I would want to know when this database
was last updated.
Is there any way we could check this.
M&M
October 22, 2008 at 6:56 pm
Hi,
I have the EXACT same question.
I hope we get an answer :>
Hopefully regular backups dont count, but only actual data modification\reads from non system tables.
PS. My database is an old 2000 database that lives on a 2005 server.
October 23, 2008 at 1:18 am
I'm using a script like this to track table usage:
SELECT sysobjects.name AS TableName
, CASE when indexes.name is null and type_desc = 'HEAP' THEN 'HEAP'
ELSE indexes.name END AS IndexName
, Type_Desc
, user_seeks
, user_scans
, user_lookups
, user_updates
FROM MyDatabase.sys.indexes
LEFT JOIN sys.dm_db_index_usage_stats
ON indexes.object_id = dm_db_index_usage_stats.object_id
AND indexes.index_id = dm_db_index_usage_stats.index_id
LEFT JOIN MyDatabase.dbo.sysobjects ON dm_db_index_usage_stats.Object_ID = sysobjects.id
WHERE Database_ID = db_id('MyDatabase')
ORDER BY sysobjects.name, indexes.name
You can tweak it to suit your needs. I look for values in the seek/scan/lookup/update columns to tell if a table or index is being used, and how it is being accessed. I run the query on a schedule and store the results so I can see usage trends over time. Note that these values are reset when the service restarts, and this is only available in 2K5 (not sure if it will work for you if your 2K database on a 2K5 box is set to 2K compatibility...)
Good Luck!
Chad
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply