I am using the following SQL server DMV to find out when a table was last updated.
USE DB_NAME
GO
SELECT
[database_name] = DB_NAME(DB_ID())
, [table_name] = tbl.[name]
, ius.last_user_update
, ius.user_updates
, ius.last_user_seek
, ius.last_user_scan
, ius.last_user_lookup
, ius.user_seeks
, ius.user_scans
, ius.user_lookups
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables tbl
ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID() -- current database
ORDER BY tbl.[name]
There is 1 table that has 2 indexes on it, and the table has been refreshed by our daily ETL. The DMV above shows 2 rows for that particular table, but both of them contains NULL in the last_user_update column. We have not done any server restart/clearing the DMV cache. The same table is in our Dev/Prod environment. Curiously, the day before in Dev, the DMV showed last_user_update value, but not anymore. The DMV in Prod has been showing nulls for this table. The same DMV works for other tables correctly.
Does anyone know how we can get the last_user_update date for this particular table? Thanks!
Edit: Adding DMV query result below:
database_name table_name last_user_update user_updates last_user_seek last_user_scan last_user_lookup user_seeks user_scans user_lookups
DB_NAME Emp NULL 0 2021-04-27 07:10:46.820 NULL NULL 1 0 0
DB_NAME Emp NULL 0 2021-04-27 07:33:54.480 2021-04-27 07:10:38.063 NULL 4 3 0
Edit 2: ETL for these tables:
- Drop index for the table
- Extract data from application database, insert those data into the table in question
- Truncate whitespace of the table in question.
- Add the index back to the table in question.
We are dropping the index because this is a data warehouse, we are re-capturing the data daily.
Edit 3: Found out what the issue is. Adding a primary key constraint actually deletes the DMV data for last_update_date. Dropping an index / recreating the index will not.
Thank you everyone for looking into this issue :)