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
330 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
12 Comments
 

Author Comment

by:goprasad
ID: 39629335
Please advise.
0
 

Author Comment

by:goprasad
ID: 39629381
Please advise
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39629434
What do you mean by active?
0
 

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 167 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 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 333 total points
ID: 39632403
No such query is possible; that information is not available anywhere in SQL Server.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 333 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 69

Expert Comment

by:ScottPletcher
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL JOIN 6 37
while loop in html mail format 5 32
BULK LOGGED - log full 9 15
select over clause 1 0
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now