SQL Server 2008 – How to Check Last Modification Time of Any Table

sql-server-2008sql-server-2008-r2

I have around 5 database and I am taking daily backup of all 5 database. I know that out of that 5 database 1 of the database is not in use and we are still taking the backup of that database.

My question is how we can check if the database is in used, like how can I verify when was the last time any Insert,update or delete happening in the database.

I think which I think of I can check the database size for 1 week and If the size of the database is same that means it is not in used. But I want to check when was the last time any DML command run.

Best Answer

This should give you the details about when was the last read/write performed on a specific database.

USE server_name;
GO

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH agg AS
   (
       SELECT 
       last_user_seek,
       last_user_scan,
       last_user_lookup,
       last_user_update
FROM
    sys.dm_db_index_usage_stats
WHERE
    database_id = DB_ID()
        )
           SELECT
             last_read = MAX(last_read),
             last_write = MAX(last_write)
          FROM
      (
SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
      ) AS x (last_read, last_write);

Be aware sys.dm_db_index_usage_stats does not return information about memory-optimized indexes. For information about memory-optimized index use, see sys.dm_db_xtp_index_stats.

Related Question