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.
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."
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?
Good info guys. Much appreciated.
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;
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
@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.
Tables without indexes will not be displayed by the script.
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
Can you please assist me to find this SQL 2000? Many thanks in advance.
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!
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;
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.
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
whats the difference between a scan and seek please?
Excellent article, but I have a question. If I'm backing up databases, should I consider backups as a reading of the database?
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.
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!
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
A very simple solution, ask to system views :
http://www.xoowiki.com/Article/SQL-Server/activite-sur-une-base-de-donnees-491.aspx
There are perfect the first one for a database 's able information the second one for all the database
Chris: Maybe. What version of SQL Server are you using?
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?
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;
Excellent Post. This is what i am looking for…Keep it up.
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
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!!!
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.
Any clue how to do the same for SQL 2000
Any clue how to do the same for SQL 2000
This is excellent, thank you so much. Is there a way to run a script to run on all databases
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.
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
[email protected]
Hugo: MS suggests using BY PUBLIC to audit all queries.
Jerry: thanks, good point, I forgot that this function was introduced in SP2.
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.
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…
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