1

can we get an output for list of databases which were not modified for last two months in a server and based on that we have to delete all of them.

I have tried below is the query: --identify when a user database was last accessed--

SELECT DatabaseName
      ,MAX(LastAccessDate) DatabaseLastAccessedOn
FROM    (
           SELECT  DB_NAME(database_id) DatabaseName
                 ,last_user_seek
                 ,last_user_scan
                 ,last_user_lookup
                 ,last_user_update
                   FROM sys.dm_db_index_usage_stats
       ) AS Pvt
       UNPIVOT
        (
           LastAccessDate FOR last_user_access IN  (
               last_user_seek  
              ,last_user_scan  
              ,last_user_lookup  
              ,last_user_update
           )  
       ) AS Unpvt
GROUP BY
       DatabaseName
HAVING DatabaseName NOT IN ('master' ,'tempdb' ,'model' ,'msdb')
ORDER BY
       2

Need a query to find DBs which were not accessed/modified for the last 2 months and delete them. i have SQL express edition.

  • Would anyone be reading those databases? Finding last modify date is simple, last read date is tricky. – vonPryz Oct 5 '18 at 5:11
  • not a problem reading the Db, we just need to get a list of all Databases and delete them based on ageing. i have used to get a list with SELECT DatabaseName, MAX(LastAccessDate) DatabaseLastAccessedOn FROM (SELECT DB_NAME(database_id) DatabaseName , last_user_seek , last_user_scan , last_user_lookup , last_user_update FROM sys.dm_db_index_usage_stats) AS Pvt UNPIVOT (LastAccessDate FOR last_user_access IN (last_user_seek , last_user_scan , last_user_lookup , last_user_update) ) AS Unpvt GROUP BY DatabaseName – vicky Oct 5 '18 at 5:34
  • 2
    Hi Vicky, Welcome to the site. Please add additional information in your question by clicking the edit link instead of adding them in comments. Also your requirements are not very clear, can you try to provide as much information as possible? – Tom V Oct 5 '18 at 7:44
2

can we get an output for list of databases which were not modified for last two months in a server and based on that we have to delete all of them.

Regardless of what you may find on the internet, the only way to be 100% sure is to run an audit/trace/etc. against the servers and inspect the traffic that each database is receiving as some system processes will update the databases and will need to be filtered out before continuing (which the audit/trace/etc. should be able to filter itself).

Possible Options:

If you don't need to be absolutely 100% sure, then feel free to choose any of the other methods on the internet such as the index usage DMVs, pages in the buffer pool, last accessed date, etc.

| improve this answer | |
  • Yeap, those are the options ultimately then , thought of some automation expertise help. – vicky Oct 5 '18 at 14:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.