Post reply

Need to Find When Database was Last Accessed

  • March 28, 2009 at 12:33 am

    #155476

    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

    #967833

    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

    #967837

    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