Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    32

    Unanswered: Checking Active DBs

    We are in the process of cleaning up some of our SQL Servers by dropping DBs that have been inactive for a while; what is the best way to find out which DBs are currently active or when was the last time a DB was been accessed accessed and/or modified?

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    A couple of things come to mind, including Profiler. But you can just pipe the output from the following query into a table once a day for a couple of weeks, and then left outer join it from sys.databases. Whichever isn't there will be your guy:
    Code:
    select dbName, LastAccessed = max(LastAccessed) from (
    select dbName = db_name(database_id), LastAccessed = max(last_user_seek) from sys.dm_db_index_usage_stats group by db_name(database_id) union all
    select db_name(database_id), max(last_user_scan) from sys.dm_db_index_usage_stats group by db_name(database_id) union all
    select db_name(database_id), max(last_user_lookup) from sys.dm_db_index_usage_stats group by db_name(database_id) union all
    select db_name(database_id), max(last_user_update) from sys.dm_db_index_usage_stats group by db_name(database_id) union all
    select db_name(database_id), max(last_system_seek) from sys.dm_db_index_usage_stats group by db_name(database_id) union all
    select db_name(database_id), max(last_system_scan) from sys.dm_db_index_usage_stats group by db_name(database_id) union all
    select db_name(database_id), max(last_system_lookup) from sys.dm_db_index_usage_stats group by db_name(database_id) union all
    select db_name(database_id), max(last_system_update) from sys.dm_db_index_usage_stats group by db_name(database_id)
    ) x
    group by dbName
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •