Labels

When Table Data Is Last Accessed And Modified in SQL Server





Here’s Quick script to find when table data is last modified or accessed

/*************************************************/
SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan  ,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')
/*************************************************/

--Note: This will return null if sql server restarted and after that data is not accessed

           Demo:
/**** Create New Database ****/
Create Database Access
GO
use Access
GO
/**** Create Table ****/
Create Table test (id int , name varchar (100))
GO
/**** Insert New Record ****/
Insert into test values (1,'Prachi')
Insert into test values (2,'Sumit')
GO
/**** After insertion Check Stats ****/
SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan  ,*
FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')

Object_Name   last_user_update                  last_user_scan
test          2015-01-14 08:39:54.100           NULL











Here last user scan is null because we are just inserted data, If we read data then we will get read time also

So now we are going to read data and see if we get last user scan

Select * from access.dbo.test
GO
/**** Checking Stats ****/
SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan  ,*
FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')
So here we just updated table , didnt read



Similarly if we want to see how current indexes are performing on table we can see user scan , user seek or look ups

No comments:
Write comments

Please do not enter spam links

Services

More Services