Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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_scanfrom sys.dm_db_index_usage_stats as statjoin sys.databases as dbon db.database_id = stat.database_idgroup by stat.database_id,db.nameorder by db.name asc
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_scanfrom sys.dm_db_index_usage_stats as statjoin sys.databases as dbon db.database_id = stat.database_idgroup by stat.database_id,db.nameorder by db.name asc
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.
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.