How to check when Db was last used in order to remove them from server. RRS feed

  • Question

  • I am using SQL Server 2008R2 & i have 50 DB's in this instance.

    my Question is how to Identify , when a DB's was last accessed. Based on the last date of access I can decide which DB's to keep and which have to be deleted.

    I got plenty of Queries in google, but the problem but none accurate, daily sql full backup job & diff back up are executing, so when i execute the query's from google, its showing yesterdays date, i.e the last day when the sql backups are  executed,

    i need a solution apart from this method , so i request you to help me.


    Thursday, January 16, 2020 7:50 PM


All replies

  • Hi NJPA1234,

    For situations like this I usually use a quey that I searched for in a forum a few years ago.

    SELECT BancodeDados,
    last_user_seek = MAX(last_user_seek),
    last_user_scan = MAX(last_user_scan),
    last_user_lookup = MAX(last_user_lookup),
    last_user_update = MAX(last_user_update)
    FROM sys.dm_db_index_usage_stats AS i
    JOIN sys.databases AS d ON i.database_id=d.database_id

    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]

    Thursday, January 16, 2020 8:13 PM
  • Hi NJPA1234,

    Please refer to which might help.

    In addition, you could use SQL Server audit or SQL Server profiler to track it.

    Please refer to the following articles which might help:

    When was my database / table last accessed?


    Auditing with SQL Profiler

    Hope this could help you.

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    • Marked as answer by NJPA1234 Monday, January 20, 2020 4:33 PM
    Friday, January 17, 2020 2:19 AM
  • Thank you  Amelia for the useful links they helped me solve my issue.


    Monday, January 20, 2020 4:34 PM