I need to find when all the databases on SQL instance last modified

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • So what is the way to find when the databases are last modified in a SQL instance.

  • 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

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply