• Aaron Bertrand has a post on last accessed tracking with a couple of different ways, as well as having posted a script that i've modified in the past a tiny bit from there:

    http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

    with that script, if you ran it in a job and stuffed the results into an audit table you'd create, running that job say every five minutes, you can find, within five minute increments, when a database is not accessed.

    since it's hitting a DMV, it's only since when the server last restarted.

    this is my modification that tracks when the last table was read or updated:

    ;;WITH myCTE AS

    (

    SELECT

    DB_NAME(database_id) AS TheDatabase,

    OBJECT_NAME(object_id,database_id) As TheTableName,

    last_user_seek,

    last_user_scan,

    last_user_lookup,

    last_user_update

    FROM sys.dm_db_index_usage_stats

    )

    SELECT

    ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),

    x.TheDatabase,

    x.TheTableName,

    MAX(x.last_read) AS last_read,

    MAX(x.last_write) AS last_write

    FROM

    (

    SELECT TheDatabase,TheTableName,last_user_seek AS last_read, NULL AS last_write FROM myCTE

    UNION ALL

    SELECT TheDatabase,TheTableName,last_user_scan, NULL FROM myCTE

    UNION ALL

    SELECT TheDatabase,TheTableName,last_user_lookup, NULL FROM myCTE

    UNION ALL

    SELECT TheDatabase,TheTableName,NULL, last_user_update FROM myCTE

    ) AS x

    GROUP BY TheDatabase,TheTableName

    ORDER BY TheDatabase,TheTableName

    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!