March 4, 2009 at 10:13 am
Hey Guys. Our 2005 Database will intermitently lock up. I understand the profiler can help me figure out what is causing it. Can you guys point me in the right direction to set it up to do this? I also understand it can create some huge logs if it isint setup selectively. Any help would be much appreciated.
March 4, 2009 at 10:23 am
I would start by looking at activity monitor when the system is locked up.
Check for blocking transactions and see what host / application / statement is causing the issue.
This is usually caused by an open transaction not being closed or a very long running transaction.
March 4, 2009 at 10:26 am
you can also use activity monitor to see blocked transactions, or Performance dashboard to get live reports of locks and blocks 🙂
March 4, 2009 at 10:34 am
Do you know if it is blocking or locking. First thing I would do is to set up the start up poarameters with -t 1204. This will write the Dead lock chain if any into the error log. Second would be to check what is blocking. Blocking should not matter since it is usually for a very period of time and should clear up pretty soon.
My 2 cents
-Roy
March 4, 2009 at 12:10 pm
Good old fashioned sp_who2 could help here too.
You don't want to run Profiler on your production system. What you want to do is set up a server side trace. It's basically Profiler without the GUI, but there is actually a slight internal difference between the two that makes Profiler less efficient. Anyway, go to Books Online and look up sp_trace_create. That'll get you started. There are several articles on here that show to do it too.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 4, 2009 at 2:02 pm
Thanks for the tips guys. I am looking at those tools now.
ANother question. Can someone explain the difference between a Lock and a Block? Are you guys suggesting it is likely one of the two?
March 4, 2009 at 3:08 pm
kellyrmartin (3/4/2009)
Thanks for the tips guys. I am looking at those tools now.ANother question. Can someone explain the difference between a Lock and a Block? Are you guys suggesting it is likely one of the two?
A lock, and there are several kinds, is basically a session holding on to a part of the datase, a page on an index or a row on a table. A block is when one session is holding a lock and another session wants it. The second session is considered to be blocked. It's a normal part of processing, nothing to worry about unless it gets excessive.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 4, 2009 at 4:07 pm
Ok. So if I am understanding correctly is that a process is likely holding a Lock on some resources that alot of people access. SO I could probably use activity monitor to isolate which exact process is causing the lock, and then from that process ID narrow down which app/piece of code is causing the problem? Once I have that information would it be ok to kill the stalled process instead of asking everyone to logout and log back in? At which point I would begin to research the issue further.
Thanks for all your help.
March 4, 2009 at 4:28 pm
Yeah basically. There are other tools you could use to help out. When you're really trying to figure out what's causing the server to slow down (or stop), this is a great way to go about it:
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply