SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 DB Last Used Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PavanKK
Starting Member

India
32 Posts

Posted - 09/20/2010 :  04:41:26  Show Profile  Reply with Quote
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

Switzerland
746 Posts

Posted - 09/20/2010 :  07:11:54  Show Profile  Reply with Quote
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

USA
1 Posts

Posted - 07/18/2011 :  18:34:31  Show Profile  Reply with Quote
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 - 01/11/2013 :  09:26:25  Show Profile  Reply with Quote
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 - 01/11/2013 :  09:31:56  Show Profile  Reply with Quote
I think this query is giving the current date time when i run it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000