none
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.


    NJPA

    Thursday, January 16, 2020 7:50 PM

Answers

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 d.name 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
    GROUP BY d.name

    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 https://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/ 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?

    AutoAudit

    Auditing with SQL Profiler

    Hope this could help you.

    Best Regards,

    Amelia


    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 MSDNFSF@microsoft.com.

    • 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.

    NJPA

    Monday, January 20, 2020 4:34 PM