June 19, 2011 at 7:11 pm
mister.magoo (6/19/2011)
Welsh Corgi (6/19/2011)
sqlfriends (6/19/2011)
So I think the script helps in some degree, but I still cannot decide if the former database is in use or not.Have you tried what I suggested?
You could execute the code provided and move on to the next task. :hehe:
As long as the database is in use when you run it....or am I wrong about that?
When I check to see if a Database is in use I look at what SPID(s) have a connection to the DB.
I look at the Database Name, login and the Application Name and go from there.
If you do not display the Connection, etc you do not have the information to go forward.
Perhaps I'm missing something?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 19, 2011 at 9:22 pm
sqlfriends (6/19/2011)
bitbucket provided the two links are helpful.The first one is broken link, the second one I tried, it turns out it all accessed today.
So I think I may check more into this.
My apologies for that bad link. Here is another good discussion.
June 20, 2011 at 4:55 am
Hi,
i also have come across such challenges in my life when i needed to migrate som Db but was n't sure if it was still in use.
So i scheduled a profile for that specific db name to run after hours and collect info for during the last 7 days of the month and also the first 7 days of the month.
and i manually ran the same trace during business hours so that i can coolect stats for during the day.
To my findings was that there was a service account that used the DB after hours but not everyday only month end there was data insertion and reads.
So the Db is not in use everyday but usually end of the month. From there i had to obviusly find out the purpose of the service account on AD and also the owner of the account.
June 20, 2011 at 5:22 am
Here's another idea... but you need some patience with this.
Put the db in auto_close mode (only that db).
Then wait a couple weeks or months and during that time check the logs or default trace (not 100% sure which one will have that info) to see the startup events. If they are not there then you have a good hint that nothing is using the db. With that "proof" in hand you can have your boss authorize a final backup of the db to a safe place and then delete it from the server.
Make sure to test the backup before deleting.
June 20, 2011 at 9:42 am
For Welsh Corgi, I can use sp_who2 to check currently if the database in use, but not if the database in use in the past month or during the past 3 months.
June 20, 2011 at 9:48 am
The other thing you can do is run a server-side trace filtered for that database ID, and use that to identify processes and commands connecting to it.
This will identify things more specifically than just "who's connected to it right this second" (sp_who, et al), or "is it being connected to at all" (autoclose). You'll be able to look at the commands being issued, and see if they're "the real thing", or just automated tasks like index rebuilds, backups, et al.
- 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
June 20, 2011 at 11:01 am
I found out the link bitbucket-25253 provided is a very helpful article.
I changed it a little bit using mister.magoo's SP_NSForeachDB to query all the databases.
Thanks
June 20, 2011 at 11:30 am
You could just dump the results of SP_WHO2 to a table in a DBA database.
Run that in a job schedule for a week or two. Then search for any hits on your database in question.
It will provide you the hostname, login that was used, program that was used, etc.
June 20, 2011 at 11:34 am
james.morrison 19355 (6/20/2011)
You could just dump the results of SP_WHO2 to a table in a DBA database.Run that in a job schedule for a week or two. Then search for any hits on your database in question.
It will provide you the hostname, login that was used, program that was used, etc.
Server trace will catch everything. You might miss something there unless you poll 120 times / minute.
June 20, 2011 at 11:37 am
james.morrison 19355 (6/20/2011)
You could just dump the results of SP_WHO2 to a table in a DBA database.Run that in a job schedule for a week or two. Then search for any hits on your database in question.
It will provide you the hostname, login that was used, program that was used, etc.
So will a server-side trace, but it will catch everything, and it's a lot easier to set up and a lot lower impact on the server resources.
- 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
June 20, 2011 at 12:52 pm
So when you say server side trace, is that a trace I can setup in SQL profiler and generated the sql script definition and run it as a sql agent job?
June 21, 2011 at 6:43 am
You can get the script for it from Profiler. It'll use sp_trace_create, sp_trace_setevent, and so on. Create the trace in Profiler, then stop it and get the script for it (there are buttons for that), then run it without the Profiler UI on top of it.
It'll need to use the "write to file" option, not tables. Otherwise it'll have a significant performance impact on the server. Writing to trace files has very little impact.
You can then query the files with fn_trace_gettable and fn_trace_getinfo. Or you can open the trace files in Profiler and look through them there. I usually use the functions to query the files, and dump them into a temp table if I need to do any real mining.
- 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
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply