• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

TSQL script to identify which DB's are active in last 60 days, in SQL server 2005 instance

Hello there,

Please advise TSQL script to identify which DB's are active in last 60 days, in SQL server 2005 instance?

Thanks and Regards
0
goprasad
Asked:
goprasad
3 Solutions
 
goprasadAuthor Commented:
Please advise.
0
 
goprasadAuthor Commented:
Please advise
0
 
SharathData EngineerCommented:
What do you mean by active?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
goprasadAuthor Commented:
To know if there has been any activity in DB level in last 60 days, so that we determine how many dB's are being actually used.
0
 
sarabhaiCommented:
this query may help for you

SELECT object_id, index_id, user_seeks, user_scans, user_lookups ,last_user_seek
FROM sys.dm_db_index_usage_stats
ORDER BY object_id, index_id
0
 
Scott PletcherSenior DBACommented:
No such query is possible; that information is not available anywhere in SQL Server.
0
 
goprasadAuthor Commented:
In that case, based on the last mdf modified date, can we assume that the db is in use?

Please advise.
0
 
goprasadAuthor Commented:
Please advise.
0
 
Scott PletcherSenior DBACommented:
No.  SQL will update that date when the db is closed, possibly just updating the header block in the db, not really data.

As I stated before, there is really no way "to identify which DB's are active in last 60 days".

It would be HUGE overhead to keep up with that history, and the vast majority of users would never need it, and when they did only extremely rarely, so there's just no reason for SQL Server to generate and retain that kind of info.
0
 
goprasadAuthor Commented:
So what's the solution? Do you think, by changing the mode to Single User and generate call to Service Desk is the way to find out?

Please advise.
0
 
Scott PletcherSenior DBACommented:
You could set the db(s) OFFLINE and see if any errors are generated.

You could run a trace that captures database names for all SQL procs and queries.
0
 
goprasadAuthor Commented:
How to run a trace using SQL profiler that captures all the transactions for all the databases in the instance.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now