When was my database / table last accessed?

AaronBertrand — Tue, 06 May 2008 05:14:00 GMT

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.

Search results matching tags 'SQL Server 2008', 'dynamic management views', and 'auditing'

When was my database / table last accessed?