How to check if a database is being used?????

Question

Post reply

How to check if a database is being used?????

Angelindiego

SSCertifiable

Points: 6048
More actions
May 3, 2010 at 3:58 pm

#386840

Hi all...not even sure how to phrase the question, but here goes!! I have an application that uses a ton of dbs. I have a list of several, that I am not sure are being used and I want to find out if that is a true statement. Is there a script or tool or ?? that can tell me if a database is being used anymore?? Properties?? I dunno....any suggestions anyone??? Thank you in advance, as always!! 😛
Thank you!!,
Angelindiego

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Leo.Miller SSChampion Points: 13051 More actions · Answer 1

Profiler!

Run a trace, filter on the database ID (SQL 2000 or less) or on the DB Name (SQL 2005 up).

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Quick eyeball check sp_who2

you could also check the default trace, and see if there has been any activity.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Angelindiego SSCertifiable Points: 6048 More actions · Answer 3

thank you all!!!! Trying all ideas!!

Thank you!!,

Angelindiego

krypto69 SSChampion Points: 13604 More actions · Answer 4

I agree with the above steps...

but one other option..

you could always check ODBC on the client side...(if it's an MDAC based app)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

You are welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

barsuk SSChampion Points: 12280 More actions · Answer 6

Set your Database(s) to 'AUTO CLOSE' and then check ErrorLog to see which ones are being used

tony.turner Right there with Babe Points: 783 More actions · Answer 7

Try DMV sys.dm_db_index_usage_stats. If there are no rows for a particular database_id, then the database has not been used since the database last came online

The advantage of the above approach, as opposed to SQL Profiler trace, is that it is a low impact approach

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

tony.turner (5/7/2010)
Try DMV sys.dm_db_index_usage_stats. If there are no rows for a particular database_id, then the database has not been used since the database last came online
The advantage of the above approach, as opposed to SQL Profiler trace, is that it is a low impact approach

This DMV stats will be there from "sql server last reboot" only.

Angelindiego SSCertifiable Points: 6048 More actions · Answer 9

Thank you all for the ideas, I am trying them all!!!!!!!!!!

Thank you!!,

Angelindiego

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 10

Hi Leo

can you explain how to actually do a trace on sql server 2005

Thanks

Jim

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Angelindiego (5/7/2010)
Thank you all for the ideas, I am trying them all!!!!!!!!!!

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 12

Hi Jason

thanks

unfortyunately, working at an AFB, that site is blocked

i will have to read it from home

Jim

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

JC-3113 (5/7/2010)
Hi Leo
can you explain how to actually do a trace on sql server 2005
Thanks
Jim

Here is a document that goes through how to do that.

http://msdn.microsoft.com/en-us/library/ms187929.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 14

JC-3113

SSCrazy Eights

Points: 8371

May 7, 2010 at 12:14 pm

#1164362

Thanks

will take a look at it

Jim