November 24, 2008 at 4:00 am
Morning,
I've searched the forums and I have knowledge that you can use sp_who and also the sys.dm_exec_sessions Dynamic Management Views to check existing connections on the server on which you run the code. But...how can you check where DBs have not been connected to in a fair while i.e. >= 1 month, or someother parameter? Is this possible using T-SQL or DMVs? Or do I need to combine several sys tables to find out the information on when it was last connected to?
I'm relatively new to this position and would like to detach DBs that are no longer being used and haven't been used in a while.
Many thanks,
Dan
November 24, 2008 at 4:57 am
There is no such way to determine last access to a database. You can set up a profiler trace that logs all connections to a database. When nothing is logged for a period of time, you know the database is not used (during that time).
You can also take the databases offline (or detach) and wait for the phone to ring... :w00t:
November 24, 2008 at 5:08 am
Thanks for the response Hanshi. I did wonder if I was looking for a 'catch-all' when in fact there isn't any. I'm suprised that SQL doesn't keep an archive of usage statistics but then I suppose it could get very big very quickly?
Not sure i'd like to go down the route of detaching and waiting for the phone to ring! Ha ha.
Thanks again
November 24, 2008 at 5:18 am
If you chaneg the db option to make it "AutoClose" then you will find messages in the SQl Server log file every tim ethe db get used. This may give a way of "alost detaching" it that means it it is still available when someone wants it - but you get an easy way of seeing it has been used.
Mike John
November 24, 2008 at 9:10 am
I would create a trace on the database, see what's connecting to it (if anything) and track that for a while. If no activity except you checking in on it, it's not in use.
That's also a good way to check what applications are connecting to a database, if you aren't sure. For example, if you're taking over an environment where documentation was not kept or was inadequate, and there are a lot of "Joe in Sales has an Access file that connects to some of these databases, and Sal in Accounting uses Crystal Reports to pull data out of some of them, but we're not sure who else is doing that kind of thing", traces on the databases can tell you exactly what's connecting when and doing what.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 24, 2008 at 9:52 am
GSquared (11/24/2008)
I would create a trace on the database, see what's connecting to it (if anything) and track that for a while. If no activity except you checking in on it, it's not in use.That's also a good way to check what applications are connecting to a database, if you aren't sure. For example, if you're taking over an environment where documentation was not kept or was inadequate, and there are a lot of "Joe in Sales has an Access file that connects to some of these databases, and Sal in Accounting uses Crystal Reports to pull data out of some of them, but we're not sure who else is doing that kind of thing", traces on the databases can tell you exactly what's connecting when and doing what.
I'm happy in regards to what Applications are connecting to the DBs, as this is also shown in the Activity monitor. I'm confident about Applications/Connections to DBs when they are active, what I meant was someway to find out which DBs were not active/being used.
Thanks again for the insight into tracing however.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply