Sunday, June 3, 2007 1:24 PM tonyrogerson

Determining the last Update to or Select against a table (without a trigger!)

SQL Server 2005 only, in the past we've had to use triggers to keep track of update activity but it has been impossible to determine when a table was last queried.

Enter the Data Management View sys.dm_db_index_usage_stats; this holds a number of useful columns and specific interest is last_user_update, last_user_seek and last_user_scan.

Note: This view is reset when SQL Server is restarted so it can only be relied on since SQL Server was started.

It even works for heaps (no index!).

 

create table test (

    id  int not null primary key,

    blah int )

 

create table heap (

    id  int not null,

    blah int )

go

Now query the table...

select *

from test

go

Check the DMV...

select *

from sys.dm_db_index_usage_stats

where database_id = db_id( 'readpasttest' )

go

As you can see, last_user_scan will be set to now.

Now try an insert...

insert test values( 1, 1 )

go

Check the DMV...

select *

from sys.dm_db_index_usage_stats

where database_id = db_id( 'readpasttest' )

go

You will see that last_user_update is now populated. 

To summarise, if you don't care that the DMV is reset on SQL Server startup then this is a good light weight method of capturing when the table was last accessed or updated; if not then you'll have to use a trigger and a column on the table itself. But, you can't capture when a table is accessed - only updated / inserted to.

 

Filed under:

Comments

# MSSQL 2008: Get last updated record by specific field (Part 2) | Alexia

Pingback from  MSSQL 2008: Get last updated record by specific field (Part 2) | Alexia