Find unused databases or when last used in a instance?

• @SQLFRNDZ

  SSCrazy Eights

  Points: 9095

  More actions

  March 30, 2011 at 12:12 pm

  #235969

  Find unused databases in a instance or when last used or accessed?

  I'm on SQL SERVER 2008 R2 64bit -enterprize

  I need to find when the databse is last accessed.

  Any help?

  Thanks

  [font="Tahoma"]
  --SQLFRNDZ[/url]
  [/font]

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  March 31, 2011 at 1:41 am

  #1305826

  You could check index access statistics for the tables in the database:

  DECLARE @accessStats TABLE (

  db_name sysname,

  last_access datetime

  )

  INSERT @accessStats

  EXEC sp_msForEachDb '

  SELECT ''?'', MAX(last_access) AS last_access

  FROM (

  SELECT DB_NAME() AS db_name,

  MAX(last_user_seek) AS last_user_seek,

  MAX(last_user_scan) AS last_user_scan,

  MAX(last_user_lookup) AS last_user_lookup,

  MAX(last_user_update) AS last_user_update

  FROM [?].sys.dm_db_index_usage_stats AS s

  ) AS src

  UNPIVOT (

  last_access

  FOR access_type IN (

  last_user_seek,

  last_user_scan,

  last_user_lookup,

  last_user_update)

  ) as u

  GROUP BY db_name

  '

  SELECT *

  FROM @accessStats

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 31, 2011 at 6:51 am

  #1305931

  Nice usage of a pivot there Gianluca, I really need to get PIVOT into my scripting usage a bit more.

  I built something similar without the pivot, yours makes it very clean;

  only thing i did different now is i added the server reboot date, so you have some reference for how long it's been since the database was accessed.:

  SELECT

  stat.*,

  ServerRestarted.ServerRebootedOn

  FROM @accessStats stat

  CROSS JOIN

  (SELECT

  CREATE_DATE As ServerRebootedOn

  FROM sys.databases

  WHERE name='tempdb') ServerRestarted

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  June 3, 2015 at 12:47 pm

  #1802776

  Nice One

  Thanks.

• francesco.mantovani

  SSC Eights!

  Points: 840

  More actions

  May 30, 2021 at 8:55 pm

  #3890001

  Try this, if the last_access column is null then no reads or writes have occurred:

  WITH cte AS (
  SELECT database_id, dt, op 
  FROM sys.dm_db_index_usage_stats
  UNPIVOT (dt for op in (last_user_lookup, last_user_scan, last_user_seek, last_user_update)) b)
  SELECT d.name DB, MAX(c.dt) last_access, MAX(i.sqlserver_start_time) sqlserver_start_time, GETUTCDATE() captured
  FROM sys.databases d 
  LEFT JOIN cte c ON d.database_id=c.database_id
  CROSS JOIN sys.dm_os_sys_info i
  WHERE d.database_id>4
  GROUP BY d.name 
  ORDER BY d.name;