October 12, 2010 at 10:06 am
Hi,
Yesterday and today, users complained that they couldnt connect to one of the servers.
i was able to connect and found that SPID 15 was blocking itself. user was dbo. nothing found using dbcc inputbuffer(15).
Then the block got cleared itself in abt 5 mins.
I need to track this deadlock graph in error logs. Will trace flag 1204 do or do i need to enable 1222 too? is 1222 available in sql 2000?
Do i need to start services with -T parameters or dbcc traceon will suffice?
October 12, 2010 at 10:50 am
There is a KB article about it - http://support.microsoft.com/kb/906344/en-us
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 12, 2010 at 11:04 am
Thanks for the articke Adi. It was helpful.
If i remember correctly, waittype column had some hexadecimal characters (5-6 characters) and lastwaittype had either PAGEIOLATCH or PAGEIOLOCK (one of these).
KB says this is normal for 2000 sp4. How do i explain this to users :crazy: since this happened twice in 2 days and they were unable to login for good 10 minutes.
Will this be possible for me to trap the information in error logs so that we can provide users with more information and explaination?
October 12, 2010 at 3:25 pm
I've found that 90% of the time a spid 'blocks itself'... it's waiting on Disk IO, which is what PAGEIOLOCK means.
Tell them the disks are slow, and take a look into better physical disk organization, better server memory management, or figure out why you've started encountering higher memory pressure.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 13, 2010 at 5:00 am
Thanks Craig. As of now i will have to wait for the issue to re-occur to gather more information.
What i found from the error logs is sql server was just restarted when this issue happened. all the databases were not fully recovered when they tried connecting. may be this could be an issue too.
October 13, 2010 at 10:23 am
Definately an issue. None of the data was cache'd in memory yet, the server was busy using the disks, and your CPU was probably spiked to 100%.
I wouldn't worry about the above recommendation I made unless it happens during day to day normal use. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply