It's emergency situation. Select from locked rows

Question

It's emergency situation. Select from locked rows

MotivateMan1394

Hall of Fame

Points: 3876
More actions
May 25, 2015 at 12:46 am

#300956

Hi
Some records in a table are locked.
How to find and unlocked them?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

sys.dm_tran_locks, identify the session holding the locks, figure out what that session is doing and what the consequences of killing the session will be. If acceptable, kill the session.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

I'd also recommend a serious followup after killing the session as to what was wrong with the code/session. Killing sessions can take an incredible toll on the server thanks to rollbacks and some of the rollbacks can go into the "zero % completed" mode forever and still consume resources.

--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)

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 3

Do be cautious. If something has been locking a resource because it just didn't have a commit on the transaction, chances are the rollback will be fast. But if it's just been doing tons of work for hours, rollback could be as long or even longer, and the locks won't clear. Be sure before you kill the process.

"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

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 4

Thank you for your replies

It done, But It was strange. The problem was From network team. They changed some Thing and after that the problem solved!!!

and they didn't tell any thing to us about that .

there are 2 questions :

1- Can I diagnos(find) the source of problem from dmv's or ... ? Can I help the team to lead them to the source of problem?

(For example in this case the source was one thing on network)

2- this morning we had another bad Problem on the DB server. cpu was 100 % for about 2 hours.

(any proccesses wasn't Lock however the speed was low and we had a lot of the temprory locks, But they gone after seconds. )

the site almost had stoped and we had to restet server. (the last solution)

Can I find in this situation, what processes of query / queries are the cause of problem and use this huge persent of cpu?

OR what is the source of this cpu usage?

thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Unless you had some monitoring in place at the time of those events, you probably can't figure out the cause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 6

MotivateMan1394 (5/31/2015)
Thank you for your replies
It done, But It was strange. The problem was From network team. They changed some Thing and after that the problem solved!!!
and they didn't tell any thing to us about that .
there are 2 questions :
1- Can I diagnos(find) the source of problem from dmv's or ... ? Can I help the team to lead them to the source of problem?
(For example in this case the source was one thing on network)
2- this morning we had another bad Problem on the DB server. cpu was 100 % for about 2 hours.
(any proccesses wasn't Lock however the speed was low and we had a lot of the temprory locks, But they gone after seconds. )
the site almost had stoped and we had to restet server. (the last solution)
Can I find in this situation, what processes of query / queries are the cause of problem and use this huge persent of cpu?
OR what is the source of this cpu usage?
thank you

You could have gotten some hints to what the problem was before "resetting" the server by opening the Object Explorer in SSMS, right clicking on the instance, selecting "Reports", and then following your nose on the "performance" related reports. Chances are that if something drove CPU to 100% for 2 hours and it was within SQL Server, it would show up as the #1 CPU resource hog (maybe #1 Read hog, as well) and it would show you the SQL that was responsible. It won't tell you who did it but it will likely tell you what it was that caused it if it was something having to do with T-SQL code or some of the internal code.

--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)