SQL getting locked up - Important please help!
-
Hi,
In the past week, we had to reboot sql server to make it come out of the locked state. Sql error log only shows regular job running.
Application log does not give any clear indication. Is possible can you help and advise how to properly troubleshoot this issue.
Sp who2 or any script which can be run to find the root cause. I know there can be many reasons but trying to narrow the problem.Thank you!
-
Here's my first suggestion:
grab a copy of Adam Mechanic's sp_whoisactive and put it in the master database:
http://whoisactive.com/downloads/running a command like this manually will give you some results of what is running, and potentially blocking other processes.
pay attention to things running for a long time, that is blocking other processes or is using lots of CPU or memory.
the results are very rich and go a long way to pointing out what to look at.
EXECUTE sp_whoisactive
EXECUTE sp_whoisactive @get_full_inner_text = 1,@get_outer_command = 1,@get_plans=1,@show_sleeping_spids=0
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
You can look to the Extended Events system_health session for some information. There is an event on long running queries that will be in there and might supply you with information to troubleshoot this situation after the fact. Look for the wait_info event.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - sizal0234 - Thursday, January 10, 2019 2:24 PM
Been through this. During the apparent "locked state", have SSMS already open so that you are actually "in" the server because you won't be able to establish a new connection during the "locked state". Run SP_WHO2 and look for connections that are in a "Killed/Rollback" state. Count the number of spids in such a state. If there are any in such a condition, post back and I'll tell you what the next step is.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I appreciate everyone's help as always. I ran couple of performance reports and found memory pressure. After a little bit of digging the culprit was the OS memory. It is like running fast ferrari( optimized sql server) on slow and bumpy road. OS memory needs to be added and also change in page file. But, it has been a great amount of learning and thank you everyone for the post. Now, after upgrading the OS memory and couple of other changes, then it will be again a testing phase to ensure we did not miss the culprit. Thank you all and have a great weekend!
- sizal0234 - Friday, January 11, 2019 2:05 PM
Like I said... been through this. We (I say "we" but I recommended that it wouldn't actually work even though it did look like simple memory pressure, which turned out to be a symptom of the real cause) added 128GB to our server and dedicated half of that plus what we previously had (28GB) to the operating system. It didn't help at all for the problem we were having whose symptoms were the "Killed/Rollback" thing I previously spoke of and that you should still look for if it happens to you again. We also jumped from 36 to 48 logical CPUs (24 core and, again, no my idea but had to let them prove it to themselves) and all it did was make things far worse because the extra CPUs allowed for many more processes that ended up in a Killed/Rollback state.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply