September 22, 2009 at 10:51 am
Hi all,
I am anjan and here is the issue
When ever users Go to Database->Tables->click on tables, it will take some time and gives below error
Lock request time out period exceeded (Microsoft sql server,Error: 1222)
and when i run sp_who2 command i can find some of the process are blocked and when i kill that process it will fine or else i have to restart that sql instance then it will be fine.
I am facing this issue daily and user are not able to work.
Please find attached snapshot and help me to resolve this issue please.
Waiting for ur replies.
September 22, 2009 at 12:39 pm
Find out what the process are that are blocking. You should be able to see the SPID with the sp_who2 command and then find out what the SPID is doing. Also check if you have open transaction.
-Roy
September 22, 2009 at 12:47 pm
Hi,
Yes i can find which process is blocking and the conern SPID also, i will kill that process at that moment it will be fine but this issur occurs after some time.
Can you tell how to increase lock_timeout time or any permanant fix pls...
September 22, 2009 at 1:01 pm
When you see the SPID that is blocking, you look and try to find out what the SPID is doing. Your permanent fix is to find out what is the root cause for the block and solve it. use DBCC command to find out what the SPID is doing and fix that statement that is blocking.
-Roy
September 22, 2009 at 1:21 pm
yes u r right but daily i see many process are getting blocked. Most of them are select queries but after killing that process the it works fine or else some time i have to restart the db instance.
September 22, 2009 at 1:31 pm
Selects use shared lock. Do you have any other Inserts or updates going to that table? Are you opening a transaction and not closing it (Commit or rollback)?
-Roy
September 23, 2009 at 4:42 am
Roy,
Yes i will check about that and any other things i can cross check as a DBA.
September 23, 2009 at 5:25 am
Hi Anjan,
What is the size of that table. This error is generic for many things.
If your table is busy and bulky and has some open transactions.
If you server configuration is not efficient
BTW, did you happen to check the health of your database or the table you are getting errors for?
September 23, 2009 at 7:24 am
Hi,
This is not specific to one table but this error is for all i,e in DB->tables->click on that then we get this error.
Can we increase lock_timeout period , do you how to do this?
September 23, 2009 at 7:28 am
Increasing the lock timeout is not going to help you. You have to find out what is causing this. That is your only solution.
-Roy
September 23, 2009 at 7:46 am
Hire someone who knows SQL Server, probably a contractor, and have them look at your database.
Something is causing the blocks. Till you know what, and solve it, nothing else you do will be of any positive value. Killing SPIDs without fixing what caused them to need to be killed in the first place is NOT a good idea.
Increasing the timeout won't solve your problem. It will be more likely to make it worse.
Get someone who knows their business to look into your database. They'll find the actual problem and help you fix it.
- 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
September 23, 2009 at 8:04 am
Please listen to what Roy has said.
Alternatively you could choose a SQL editor that doesn't suck quite as much! 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2009 at 8:27 am
If I can make a suggestion, next time you have this problem. Take a screenshot of the activity monitor or attach the contents of running sp_who2. So we can try and advise you on what is going on.
As mentioned before, Killing spids (processes) is only a temporary solution and you can impact on performance and cause potential data loss, without knowing what the processes are doing.
The more information you can provide, the more people here can advise you on the course of action you could take.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 23, 2009 at 12:55 pm
I agree with the others - until you find the root cause, and know why it's causing the problem, you cannot fix anything.
What you are doing is like getting a blue screen of death on windows, rebooting, and saying you 'fixed' the problem.
One other possibilty you might think about - were there any changes to any applications that access any of your SQL databases?
If this suddenly appeared after new code was migrated to production, it may be possible to back this out. At the very least, it may help isloate what needs to be looked at further.
Greg E
September 23, 2009 at 9:56 pm
In addition to the above replies, if you can run a trace (or check the default trace in SS - 2005) , save them in .trc file and attache it here we all can try helping you in a much better way.
check if sending trace file is allowed in your organization:-)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply