Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 DB Last Used Date

Author  Topic 

PavanKK
Starting Member

32 Posts

Posted - 2010-09-20 : 04:41:26
Hi Forum,

As we are on the way to cleaning Production server, we need to find out the last usage date of each database. sothat we can clean the databases which are not used for a long time. Is there any way to find the last usage date of the database.

thanks for the help.


KK

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-20 : 07:11:54
try this:

select
stat.database_id as database_id
,db.name as database_name
,max(stat.last_user_scan) as last_user_scan
from sys.dm_db_index_usage_stats as stat
join sys.databases as db
on db.database_id = stat.database_id
group by stat.database_id,db.name
order by db.name asc
Go to Top of Page

Annetho
Starting Member

1 Post

Posted - 2011-07-18 : 18:34:31
This is super helpful. Thank you very much!


quote:
Originally posted by slimt_slimt

try this:

select
stat.database_id as database_id
,db.name as database_name
,max(stat.last_user_scan) as last_user_scan
from sys.dm_db_index_usage_stats as stat
join sys.databases as db
on db.database_id = stat.database_id
group by stat.database_id,db.name
order by db.name asc


Go to Top of Page

supersql
Yak Posting Veteran

99 Posts

Posted - 2013-01-11 : 09:26:25
I dont think this script would work in all cases, if you have backup job or some other job runing on a weekly basis this query would show you latest date when the database was backed up for all the databases in the server.
Go to Top of Page

supersql
Yak Posting Veteran

99 Posts

Posted - 2013-01-11 : 09:31:56
I think this query is giving the current date time when i run it.
Go to Top of Page
   

- Advertisement -