May 6, 2008 | SQL Server

When was my database / table last accessed?

A frequently asked question that surfaced again today is, "how do I see when my data has been accessed last?"  SQL Server does not track this information for you.  SELECT triggers still do not exist.  Third party tools are expensive and can incur unexpected overhead.  And people continue to be reluctant or unable to constrain table access via stored procedures, which could otherwise perform simple logging.  Even in cases where all table access is via stored procedures, it can be quite cumbersome to modify all the stored procedures to perform logging.

SQL Server 2008 will offer Server Auditing for all actions, and this can be logged to a file, or to the Windows Application or Security Log.  You can do something as narrow as record when a specific login queries AdventureWorks.Person.Address.City, and as wide as recording information about every query against every database on the entire instance.  Here is a quick sample that audits all select queries against Person.Address in the AdventureWorks sample database:

USE master;
GO
CREATE SERVER AUDIT Test_Server_Audit
    TO FILE ( FILEPATH = 'C:\Audits\' );
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = ON);
GO
USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT ON Person.Address BY PUBLIC)
    WITH (STATE = ON);
GO
 
SELECT *
    FROM Person.Address;
GO
 
SELECT * 
    FROM fn_get_audit_file('C:\Audits\*', NULL, NULL);
GO
 
USE AdventureWorks;
GO
ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

For those of us who don't want to wait for SQL Server 2008 and cannot use stored procedures to log select activity, there is another answer: the DMV sys.dm_db_index_usage_stats, introduced in SQL Server 2005.  By showing the last read and write to a table, this DMV allows us to answer the questions we couldn't before:

  • when was database x accessed last?
  • when was table y accessed last?

We can answer the question about access to a database simply by aggregating the data in the DMV to the database level:

USE AdventureWorks;
GO
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
 
WITH agg AS
(
    SELECT 
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT last_user_seek, NULL FROM agg
    UNION ALL
    SELECT last_user_scan, NULL FROM agg
    UNION ALL
    SELECT last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);

Switching focus to each table is accomplished by adding the object name to the GROUP BY (and as Jerry pointed out, this will require SP2 to use OBJECT_SCHEMA_NAME(), otherwise you can join against sys.tables and sys.schemas):

USE AdventureWorks;
GO
 
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
 
WITH agg AS
(
    SELECT 
        [object_id],
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    [Schema] = OBJECT_SCHEMA_NAME([object_id]),
    [Table_Or_View] = OBJECT_NAME([object_id]),
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT [object_id], last_user_seek, NULL FROM agg
    UNION ALL
    SELECT [object_id], last_user_scan, NULL FROM agg
    UNION ALL
    SELECT [object_id], last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id])
ORDER BY 1,2;

One word of note is that sometimes an UPDATE can look like a simultaneous read and write.  For example:

USE AdventureWorks;
GO
UPDATE Person.Address SET City = City + ";
GO
SELECT *
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID() 
    AND index_id = 1
    AND [object_id] = OBJECT_ID('Person.Address');
GO

See that for index_id 1, last_user_scan and last_user_update are identical and fairly recent.

Another note is that unless a view is indexed, you cannot reliably track access to a view — instead the references to the underlying tables are updated in the DMV.

UPDATE – Mike C# and dave ballantyne brought up a great point that applies to all DMVs: the values do not survive a SQL Server restart, or detach/attach, or even Auto-Close. So, if you restart your server and then want to see when something was last accessed, all objects will either be NULL or very recent. One way to work around this is to create a SQL Server Agent job that polls the DMV periodically, and stores a snapshot of the data. This way you can have a running history of "last access" and maybe roll it up once per day (or whatever granularity is suitable).

Even when SQL Server 2008 is released, auditing of some kind will be required if you want more information, such as a history of who ran which queries.  And if you are looking for more details about information that has been added, updated or deleted, you are going to want to look into the Change Tracking and/or Change Data Capture features.  But in the meantime, this DMV provides a quicker and much lighter-weight approach to at least determining when your data was accessed last.

36 comments on this post

    • Hugo Kornelis - May 6, 2008, 10:17 AM

      Hi Aaron,
      I probably could fire up my VM and just check myself, but I'm a bit short on time so I'll just ask.
      You write: "Here is a quick sample that audits ALL select queries against Person.Address" (emphasis added by me). But the code contains "ADD (SELECT ON Person.Address BY dbo)" which, unless the syntax is less intuitive that it should be, implies that only select queries by dbo will be audited. Am I right, or is this indeed some weird syntax rule that I should study before using this feature?
      Best, Hugo

    • AaronBertrand - May 6, 2008, 3:06 PM

      Hugo, the documentation is a rev behind and kind of light on this, and it is the only syntax I could get to work.  The current version of the doc says that you can say "BY principal [,…n]" but I couldn't figure out how to make that work (and there doesn't seem to be a catch-all, like ALL or *).  I considered dropping the auditing code sample altogether, but I was so happy that after struggling with it for 20 minutes I finally got it to work.  Being late at night, I resigned to just leaving that part as is and re-visit it.  I am hoping when I see newer documentation I will learn how you can avoid having to specify every single server principal you want to audit… on some systems that will be quite prohibitive.  (On my test system, of course, this was sufficient.)  For now I will update the article…

    • jerryhung - May 7, 2008, 5:46 PM

      Note: you need SP2 installed for the 2nd code
      otherwise you'll get this
      'OBJECT_SCHEMA_NAME' is not a recognized built-in function name.

    • AaronBertrand - May 7, 2008, 9:39 PM

      Hugo: MS suggests using BY PUBLIC to audit all queries.
      Jerry: thanks, good point, I forgot that this function was introduced in SP2.

    • Stress - June 2, 2008, 10:01 PM

      Hi. I have seen lots of examples on the 2005 index usages DMV, but one thing has eluded me : it only seems shows access stats for the dbo. How could I get the actual stats as run by the SQL user using the index? (web app, different users assigned for admin/user access)
      Any help would be much appreciated.
      /Stress
      stress@gmail.com

    • AaronBertrand - June 29, 2008, 9:40 PM

      Stress, I think it will show stats for all users (not just dbo), but it does not differentiate, and lumps them all into the same bucket.  The DMV would have to be a completely different structure to show access stats from different users.

    • Ramesh - April 22, 2009, 1:13 PM

      This is excellent, thank you so much. Is there a way to run a script to run on all databases

    • Rafael - May 28, 2009, 12:29 AM

      Any clue how to do the same for SQL 2000

    • Rafael - May 28, 2009, 12:30 AM

      Any clue how to do the same for SQL 2000

    • Darlene - January 6, 2010, 9:28 PM

      HI I have a requiremnet to take a database use audit across 400 servers 6000 databases.  Typically when we have to do gather info for all servers we run a generic statement from our one monitoring repository server (using 2008) to connect to server and retreive the info we need. The get db access info is something I need to implement here across all servers. Do you kknow of any product or code that will retrieve server name, app name, dbname, last access date?  and place it in an excel spreadsheet monthly? so we can monitor it over time.

    • Mark - March 10, 2010, 7:38 PM

      This has saved me hours of work, I need to determine which tables amongst hundreds that an app is using so I can move them to another schema. Just restart the server and run through the app and the SQL produces the list! Thanks!!!

    • Sahil - March 30, 2010, 10:37 PM

      Hi,
      How do i confirm when someone has accessed SQL 2000 databases.  I need to take offline 80 databases and not sure whether they are still being used.
      So far i know we can run Security Audit trace but i really wanted to avoid this since all these databases are residing on production servers.
      Thanks in Adv.
      Sahil

    • Mohan - June 25, 2010, 11:43 PM

      Excellent Post. This is what i am looking for…Keep it up.

    • Sanjay - June 30, 2010, 7:03 AM

      Made some changes to the query to get the last updated details for all databases at one go along with dbnames.
      WITH agg AS
      (
         SELECT
             max(last_user_seek) last_user_seek,
             max(last_user_scan) last_user_scan,
             max(last_user_lookup) last_user_lookup,
             max(last_user_update) last_user_update,
             sd.name dbname
         FROM
             sys.dm_db_index_usage_stats, master..sysdatabases sd
         WHERE
           database_id = sd.dbid  group by sd.name
      )
      SELECT
         dbname,
         last_read = MAX(last_read),
         last_write = MAX(last_write)
      FROM
      (
         SELECT dbname, last_user_seek, NULL FROM agg
         UNION ALL
         SELECT dbname, last_user_scan, NULL FROM agg
         UNION ALL
         SELECT dbname, last_user_lookup, NULL FROM agg
         UNION ALL
         SELECT dbname, NULL, last_user_update FROM agg
      ) AS x (dbname, last_read, last_write)
      GROUP BY
         dbname
      ORDER BY 2;

    • Chris - July 8, 2010, 5:12 PM

      I can't see sys.dm_db_index_usage_stats. If I try to execute
      select * from sys.dm_db_index_usage_stats I get the following error:
      Invalid object name 'sys.dm_db_index_usage_stats'.
      Is this a permissions issue?

    • AaronBertrand - July 8, 2010, 5:34 PM

      Chris: Maybe.  What version of SQL Server are you using?

    • Mark - December 7, 2010, 7:24 PM

      There are perfect the first one for a database 's able information the second one for all the database

    • sacha79 - March 16, 2011, 11:59 PM
    • Kimberly L. Tripp - April 12, 2011, 4:14 PM

      Hey there Aaron – I just entered a connect item for this. Vote early, vote often: Connect item: 659846.
      http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/659846/database-last-accessed-time
      Cheers,
      kt

    • Chris Wood - September 22, 2011, 11:59 AM

      Hi Aaron,
      I'm trying to capture any tables that have never been accessed (since the last time the dmv's where reset anyway).
      Is it a safe assumption that anything that is not produced in this list has never been accessed? as they are not appearing in my query results. I have a number of tables with no indexes or keys so not sure if they will show up, dont want to remove any staging tables used in a overnight process!

    • AaronBertrand - September 22, 2011, 3:27 PM

      Chris, primary keys or indexes are not required to show up in this list. The table doesn't even have to have any rows – the DMV will contain a row if you've updated, inserted, deleted or selected even if the table ultimately was not changed.
      That said, I still would *not* rely on this DMV to automate cleaning up "never accessed" tables. You should use it to produce a list but then you should still be examining the list to make sure there aren't any false positives.

    • Alfgomled - June 29, 2012, 1:56 PM

      Excellent article, but I have a question. If I'm backing up  databases, should I consider backups as a reading of the database?

    • john tyler - August 22, 2012, 9:27 AM

      whats the difference between a scan and seek please?

    • Jenn - August 22, 2012, 3:31 PM

      I'm getting the following errors:
      Msg 33072, Level 16, State 1, Line 1
      The audit log file path is invalid.
      Msg 15151, Level 16, State 1, Line 1
      Cannot alter the audit 'Test_Server_Audit', because it does not exist or you do not have permission.
      Msg 33073, Level 16, State 1, Line 1
      Cannot find audit 'Test_Server_Audit' or you do not have the required permissions.
      Can you possibly assist?  Thank you

    • Anand - April 10, 2013, 5:41 PM

      Word of Caution:
      I am sure all of reading this know this already.
      DMVs set back to Zero on restart of SQL Server. So the query only true since the last restart of SQL.
      Immeidately after a SQL Instance restart if you run the query it will return 0 rows.

    • Michael Whiteley - May 28, 2013, 6:05 PM

      I find that the query , mis-interprets the data, as some operations run externally from the DB , access the system objects.
      Below query, Cruedly excludes system objects , so that sysindex / sysobjects etc. checks are ignored. This has given me more accurate results.
      ;WITH agg AS
      (
        SELECT
            max(last_user_seek) last_user_seek,
            max(last_user_scan) last_user_scan,
            max(last_user_lookup) last_user_lookup,
            max(last_user_update) last_user_update,
            sd.name dbname
        FROM
            sys.dm_db_index_usage_stats i
            JOIN  master..sysdatabases sd on database_id = sd.dbid
            where  i.object_id > (select max(object_id) from sys.objects)
             group by sd.name
      )
      SELECT
        dbname,
        last_read = MAX(last_read),
        last_write = MAX(last_write)
      ,(select create_date from sys.databases where name='tempdb') as LastServerRestart
      FROM
      (
        SELECT dbname, last_user_seek, NULL FROM agg
        UNION ALL
        SELECT dbname, last_user_scan, NULL FROM agg
        UNION ALL
        SELECT dbname, last_user_lookup, NULL FROM agg
        UNION ALL
        SELECT dbname, NULL, last_user_update FROM agg
      ) AS x (dbname, last_read, last_write)
      GROUP BY
        dbname
      ORDER BY 2;

    • Eilish - January 6, 2014, 1:33 PM

      Hi, thanks for this – very useful!
      Just wondering if there is anyway to include the user_id to see who last wrote to the tables?
      Thanks!

    • Sahul - March 25, 2014, 6:54 PM

      Can you please assist me to find this SQL 2000? Many thanks in advance.

    • gomyers - January 29, 2015, 12:02 AM

      I made a slight modification to retrieve for all databases, except system databases and exclude system objects in results.  Otherwise the statistics were corrupted by SCOM and other system tools monitoring database size, etc.
      BTW: The CTE runs each time it is reference making this a bit slower that using a temp table.  While this looks more elegant, I like INTO #STATS.  Replace all references to STATS with #STATS
      ; WITH STATS as (
      SELECT D.name, D.database_id
      , max(last_user_seek) as last_user_seek
      , max(last_user_scan) as last_user_scan
      , max(last_user_lookup) as last_user_lookup
      , max(last_user_update) as last_user_update
      FROM sys.databases D
      LEFT OUTER JOIN sys.dm_db_index_usage_stats S on D.database_id = S.database_id
      WHERE D.database_id > 4
       AND S.object_id > 100
      GROUP by D.name, D.database_id
      )
      SELECT * from STATS
      UNION
      SELECT D.name, D.database_id, NULL, NULL, NULL, NULL
       FROM sys.databases D
      WHERE D.database_id > 4
        AND not exists (select * from STATS where name = D.name)
      ORDER by 1

    • Sam - May 25, 2015, 7:08 PM

      Tables without indexes will not be displayed by the script.

    • AaronBertrand - May 26, 2015, 9:07 PM

      @Sam Which script is "the script"? Something from the article, something from the comments, something else? Could you post a repro? I was able to get heaps with no indexes as well as tables with clustered indexes with all of the scripts in the article. I tested the comment from January 28th but stopped there before deciding to ask you to clarify.

    • mmcdonald - September 11, 2015, 7:23 PM

      Nice article and comments…
      Slight tweak to the table usage script (eliminate internal objects)
      ;with agg AS
      (
         SELECT
             s.[object_id],
             last_user_seek,
             last_user_scan,
             last_user_lookup,
             last_user_update
         FROM sys.dm_db_index_usage_stats s
      inner join sys.objects o on s.object_id = o.object_id
         WHERE database_id = DB_ID()
      and o.is_ms_shipped <> 1
      )
      SELECT
         [Schema] = OBJECT_SCHEMA_NAME([object_id]),
         [Table_Or_View] = OBJECT_NAME([object_id]),
         last_read = MAX(last_read),
         last_write = MAX(last_write)
      FROM
      (
         SELECT [object_id], last_user_seek, NULL FROM agg
         UNION ALL
         SELECT [object_id], last_user_scan, NULL FROM agg
         UNION ALL
         SELECT [object_id], last_user_lookup, NULL FROM agg
         UNION ALL
         SELECT [object_id], NULL, last_user_update FROM agg
      ) AS x ([object_id], last_read, last_write)
      GROUP BY
         OBJECT_SCHEMA_NAME([object_id]),
         OBJECT_NAME([object_id])
      ORDER BY 1,2;
      cheers

    • mmcdonald - September 16, 2015, 10:27 PM

      tweak / hybrid (gomyers & Michael Whiteley) to the database usage script
      ;declare @LastRestart datetime
      select @LastRestart = create_date from sys.databases where name = 'tempdb';
      WITH agg as (
      SELECT D.name dbname
      , D.database_id
      , max(last_user_seek) as last_user_seek
      , max(last_user_scan) as last_user_scan
      , max(last_user_lookup) as last_user_lookup
      , max(last_user_update) as last_user_update
      FROM sys.databases D
      LEFT OUTER JOIN sys.dm_db_index_usage_stats S on D.database_id = S.database_id
      AND S.object_id > 100
      WHERE D.database_id > 4
      GROUP by D.name, D.database_id
      )
      SELECT
      dbname
      , last_read = MAX(last_read)
      , last_write = MAX(last_write)
      , @LastRestart as LastServerRestart
      FROM
      (
      SELECT dbname, last_user_seek, NULL FROM agg
      UNION ALL
      SELECT dbname, last_user_scan, NULL FROM agg
      UNION ALL
      SELECT dbname, last_user_lookup, NULL FROM agg
      UNION ALL
      SELECT dbname, NULL, last_user_update FROM agg
      ) AS x (dbname, last_read, last_write)
      GROUP BY dbname
      ORDER BY 2;

    • Rex - July 26, 2016, 4:24 PM

      Good info guys. Much appreciated.

    • Michael - August 25, 2016, 1:40 PM

      hello
      Im more interested, in which user last accessed a table.
      Do i really need audit log to find this?
      Can't i join users to the system view?

    • AaronBertrand - October 6, 2016, 9:31 PM

      Michael, no, because there is no system view that says "this user accessed this table." As I said at the beginning of the article, "SQL Server does not track this information for you."

Comments are closed.