SQL Query to find the Last Activity Details on a Database

At times there may be need to find when was the last activity done on a database. This also might help you to clean up old unused databases on a SQL Server Instance.
I found out below query with slight modifications of mine.
You can Modify the Order By Criteria as per you convinience.
USE [YOURDATABASENAME]
GO
SELECT
      T.NAME
      ,USER_SEEKS
      ,USER_SCANS
      ,USER_LOOKUPS
      ,USER_UPDATES
      ,LAST_USER_SEEK
      ,LAST_USER_SCAN
      ,LAST_USER_LOOKUP
      ,LAST_USER_UPDATE
FROM
      SYS.DM_DB_INDEX_USAGE_STATS I JOIN
      SYS.TABLES T ON (T.OBJECT_ID = I.OBJECT_ID)
WHERE
      DATABASE_ID = DB_ID(‘YOURDATABASENAME’)
ORDER BY LAST_USER_SEEK DESC
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s