last time when DB was touched

• ramadesai108

  SSChampion

  Points: 10609

  More actions

  December 28, 2010 at 7:50 am

  #97789

  Hi,

  I am trying to figure out when was the last time when databases were touched on a particular instance. The following works on SQL 2005 but not on 2000. What do I need to do?

  SET ANSI_WARNINGS OFF;

  SET NOCOUNT ON;

  GO

  exec sp_msforeachdb 'USE [?];

  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

  [db] = db_name(),

  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;'

  Thanks,

• ramadesai108

  SSChampion

  Points: 10609

  More actions

  December 29, 2010 at 8:20 am

  #1267208

  I found a solution which is not perfect but doable:

  SELECT Name, LastWaitType, Crdate,

  CASE WHEN Last_Batch > Login_Time

  THEN Last_Batch ELSE Login_Time END AS [LastTouched]

  FROM SysDatabases o

  LEFT JOIN SysProcesses s

  ON s.dbid = o.dbid

  WHERE Name NOT LIKE 'master'

  AND Name NOT LIKE 'msdb'

  AND Name NOT LIKE 'tempdb'

  ORDER BY Name