March 28, 2009 at 12:33 am
Hi All,
Just joined this company as DBA and need to do spring cleaning on about 50 servers.
Databases are a mix of SQL 2000 & 2005
I need to find out when a particular database was last accessed for both SQL 2000 & 2005
Nobody has any idea about the applications/processes accessing the databases. Some db might be used once a month or even once a year, so running a trace is not an option.
Is there any other way I can find when a database was last accessed both for sql 2000 & 2005
(Putting each db offline and waiting for people to shout is the last option :-))
March 28, 2009 at 12:42 am
For SQL Server 2005, You can try this. This will give you any select, inserts, updates done on tables with datetime.
select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from sys.dm_db_index_usage_stats i
JOIN sys.tables t ON (t.object_id = i.object_id)
where database_id = db_id()
Note: If you restart SQL Server, all these will be reset to NULL.
also this is per database. Can be modified easily to execute for all databases.
~ IM.
March 28, 2009 at 12:58 am
thanks sayfriend
any idea how to do the same thing with sql 2000?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply