last_user_update in sys.dm_db_index_usage_stats

• clement.store

  Old Hand

  Points: 309

  More actions

  March 6, 2011 at 8:35 pm

  #150045

  Dear all,

  I have restored a database from server B to server A.

  When I modified a record in a table in server B, I didnt notice a DATE/ TIME update to the last_user_update column in sys.dm_db_index_usage_stats. However, I did notice the last_user_update field has been updated to 'NULL' instead of nothing showing from select * . Would u guys have any idea how to get the update date into the last_user_update field? Did I forget to enable an index or sth?

  Working on SQL server 2005.

  thanks

  Clement

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 7, 2011 at 1:22 am

  #1295197

  Did you update a column that was part of the index that you're looking at?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• clement.store

  Old Hand

  Points: 309

  More actions

  March 7, 2011 at 8:05 pm

  #1295679

  Thanks for ur reply. I might have mistaken sth. I think got it now.

  Besides thanks for u guys hint last time on - sync 2 DBs without primary keys a few months back. I notice u changed ur starwar portrait. I tried a couple of solution including, transactional replication, mirroring etc.. Finally it turned out that making use of the last_user_update and executing a custom-made script to do an automatic update in periods seems a viable solution as I need both servers to be UP for most of the time.

  Cheers

  Clement

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  April 13, 2015 at 9:16 am

  #1790554

  YEs , It's fixed. Thanks Gila...

  select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate

  from

  (

  SELECT [Database Name] = DB_NAME(database_id),

  [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

  WHEN Type_Desc = 'LOG' THEN 'Log File(s)'

  ELSE Type_Desc END,

  [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

  FROM sys.master_files

  GROUP BY GROUPING SETS

  (

  (DB_NAME(database_id), Type_Desc),

  (DB_NAME(database_id))

  )

  --ORDER BY DB_NAME(database_id), Type_Desc DESC

  ) as a

  left join

  (select max(last_user_update) as LastUserUpdate,[Database Name] = DB_NAME(database_id) from sys.dm_db_index_usage_stats

  group by database_id ) as b on a.[Database Name]= b.[Database Name]

  Thanks.