January 21, 2014 at 8:34 am
I used below query but i have to query each and every database in the instance to get the information.
SELECT max(last_user_update) last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'YOUR_DBNAME_HERE')
But the above query is returning null for many of the databases.
Can anyone please let me know why its resulting null.
Also i would appreciate if only one query is provided to find all the last modified date of all the databases in one SQL instance.
January 21, 2014 at 8:38 am
That DMV only tracks data since the last time SQL was started. If a DB has had no data modifications since the last time the server was started, that query will return null for that database.
SQL doesn't track modifications across server restarts, the best you'll get is when it was modified since the last time SQL started.
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
January 21, 2014 at 8:42 am
So what is the way to find when the databases are last modified in a SQL instance.
January 21, 2014 at 8:44 am
SQL doesn't track modifications across restarts of the service. If you need to track that, you'll have to put some custom monitoring in place going forward.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply