May 7, 2009 at 3:41 am
Hi Guys
One of my scheduled integrity checks on a server, which runs at 03:00 every Sunday fails because some process somewhere is in the server and it can't get exclusive access to the databases.
We're not a 24/7 operation so there should be nothing happening at this point.
So, any suggestions on what I can do to see what process is happening, or who/what is logged in at that time.
Once again, sorry if this is an obvious question but I've been thrown in at the deep end with some veeerrry rusty DBA knowledge (10 years since I did this for a living, and no refresher training)
Cheers
Sam
May 7, 2009 at 4:42 am
You can use profiler. Just kick it off and look for what process is doing what at 3am.
Either that or sit up and run a sp_who2 just before the process kicks in.
May 7, 2009 at 6:08 am
Profiler would be my first choice.
Second wouldn't be sp_who2. It'd be sys.dm_exec_requests. With that you can link to other dmv's to get query text, execution plans... whatever you need. But, prepare a few queries in advance 3AM on a Sunday is no time to start writing them. :w00t:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2009 at 6:15 am
Do you have any jobs running at the same time at 3 AM? Or is these any delay with any job like rebuilding indexes or any operation that takes exclusive access of the database?Is this a part of maintenance plan that you are running integrity checks?
May 7, 2009 at 6:20 am
If you want to see what processes are happening try this SSIS package, or use the scripts in the post to run a SQL job every few minutes to gather information in a table.
http://www.simple-talk.com/sql/database-administration/using-ssis-to-monitor-sql-server-databases-/
May 7, 2009 at 6:43 am
Thanks guys, some really useful stuff here.
Much as I absolutely love my job I don't think I'll be in the office this Sunday!
Krishna Potlakayala (5/7/2009)
Do you have any jobs running at the same time at 3 AM? Or is these any delay with any job like rebuilding indexes.......
Good point. I'll have a dig around other scheduled jobs and maintenance plans etc to see if there are any "crashes".
Cheers
Sam
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply