Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-11-06
12
Medium Priority
?
341 Views
Last Modified: 2014-01-05
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
Comment
Question by:goprasad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 

Author Comment

by:goprasad
ID: 39629335
Please advise.
0
 

Author Comment

by:goprasad
ID: 39629381
Please advise
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39629434
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!

 

Author Comment

by:goprasad
ID: 39629489
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
 
LVL 9

Accepted Solution

by:
sarabhai earned 668 total points
ID: 39629784
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1332 total points
ID: 39632403
No such query is possible; that information is not available anywhere in SQL Server.
0
 

Author Comment

by:goprasad
ID: 39632611
In that case, based on the last mdf modified date, can we assume that the db is in use?

Please advise.
0
 

Author Comment

by:goprasad
ID: 39632630
Please advise.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1332 total points
ID: 39633464
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
 

Author Comment

by:goprasad
ID: 39635071
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39636594
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
 

Author Comment

by:goprasad
ID: 39640796
How to run a trace using SQL profiler that captures all the transactions for all the databases in the instance.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question