Post reply

How to find if a database has been access/used since last month.

  • July 6, 2015 at 2:58 pm

    #319635

    Hi expert,

    Is there anyway I can find or make a report if a databases has been accessed/used since last month?

  • July 6, 2015 at 3:26 pm

    #1810586

    First thing that comes to mind is a server-side trace that tracks against the database(s) you are interested in, catching rpc:completed and batch completed events. An audit or extended event could probably do it as well.

    Joie Andrew
    "Since 1982"

  • July 6, 2015 at 3:48 pm

    #1810588

    Joie's suggestion is the safest way, if you're planning on using this information to drop/offline a database. Just make sure to monitor the database for an appropriate period of time (keeping in mind that the DB might be used for monthly or even annual reports).

    You can also get some idea of this by checking the last_user_* columns in sys.dm_db_index_usage_stats. Those stats do get reset on instance restart or database offlining/detaching, so checking them is not a foolproof method of checking most recent access.

    They will generally give you a good idea of how recently a database is used, but I wouldn't drop/offline a database just because those stats showed it's been a while since the last user seek/scan/lookup/update.

    A query like the following will show you the most recent user access date in that DMV for each database:

    WITH LastAccessedDates AS (

    SELECT

    database_id,

    LastAccessed=(SELECT MAX(AccessDate) FROM (VALUES (last_user_seek),(last_user_scan),(last_user_lookup),(last_user_update)) AS AccessDates(AccessDate))

    FROM sys.dm_db_index_usage_stats)

    SELECT

    DatabaseName=DB_NAME(database_id),

    LastAccessed=MAX(LastAccessed)

    FROM LastAccessedDates

    GROUP BY database_id

    ORDER BY LastAccessed ASC

    Cheers!

  • July 6, 2015 at 5:16 pm

    #1810598

    There's no really good way to do that because it's so difficult. A db could be referenced by a linked server or by a 3-part name contained in dynamic SQL.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • July 7, 2015 at 5:16 am

    #1810669

    Thanks guys!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply