Tag Archives | Troubleshooter

Best explanation I’ve seen of SQL 2000 SP4 self blocking

I stumbled upon the best explanation I’ve seen of the self-blocking that we sometimes see in SQL Server 2000 SP4. I’ve used Sherlock and other tools in the past and always been dumbfounded by what I was seeing. There are many folks still stuck with SQL 2000 with no option to upgrade every server, so this still provides some help when we see strange things.

I’m copying the post with a link going back to it as this blog does serve as my repository for something if I need to go back and look at it. If anyone who is more proficient at internet manners (Brent Ozar (Blog|Twitter)), please let me know how I could do this better.

The following is the post and link which I claim no credit for aside from finding it and reposting it:

What you are seeing is not related to locks, rather it is related to the
latch waits you see in sysprocesses.  In SP4 we are able to determine the
blocking spid for latch waits when the blocking spid holds the latch in
exclusive or update mode.  In these cases the blocking spid information is
populated into the “blocked” field in sysprocesses.
So why is the spid blocked by itself?  Well, that has to do with how latches
are used for IO operations.  When an IO is issued for page, a latch is held
on the page.  Depending on the IO operation, the latch mode acquired is
shared (SH) or exclusive (EX).  The latch for the IO is acquired by the
thread that issues the IO.  Since all SQL Server IO operations are
asynchronous, if the spid that issued the IO wants to wait for the IO to
complete it will attempt to acquire another latch on the same page after
issuing the IO.  The first latch is released when the IO completes.  This
release allows the second latch request to be granted.
Here’s an example of how this works:
1. Spid 55 wants to read page P1 which does not exist in the buffer pool.
2. Spid 55 acquires an EX latch on page P1 — this marks spid 55 as owning
the latch.  The latch is in an in memory data structure, not the physical
page itself.  Since the page does not yet exist in memory the mode is EX in
order to force other spids that may also want to access the page to wait for
the IO to complete and also to prevent them from issueing a second IO
operation for the same page.
3. Spid 55 issues the IO request to read P1 from disk.
4. Since Spid 55 wants to read the page, it must wait for the IO to
complete.  It does this by attempting to acquire another latch (in this case
a share (SH)) latch on the page.  Since the latch is already held in EX, the
SH request is blocked and the spid is suspended.
5. Upon completion of the IO the EX latch on the page is released.
6. The release of the EX latch grants the SH latch to spid 55.
7. Spid 55 can now read the page.

For the duration between steps 4 (the SH latch acquire) and step 5 (EX latch
release) sysprocesses will indicate that spid 55 is blocked by itself with a
wait type of PAGEIOLATCH_XX (where XX can be SH, UP, or EX) as an indication
that it is waiting for the completion of an IO that it itself issued.


Santeri (Santtu) Voutilainen

http://groups.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en&pli=1

Comments { 0 } Posted on May 10, 2011 in SQL, SQL Server 2000, SQLServerPedia Syndication

Install SQL Server from network

Whenever it comes time to install SQL Server to a server it’s usually not a big deal to walk back to the server with the disc or share my drive across the network. I ran into a different situation this weekend when I needed to install several versions on a server while on a vpn connection from home. I can’t switch the discs when needed because I’m over 30  minutes away so I needed to come up with a better way.

The problem arises when I have to deal with a .iso file. I needed a way to get the contents out of the iso and into a folder that I could use for a network install. After a small amount of searching I found that I had a few options. The option from Microsoft (Virtual CD Rom) didn’t play nice with the server permissions. I finally got ISOBuster to work for my needs.

Once I extracted the .iso file to a folder for each of the cd/dvds then it was a very easy process. I’m sure there are multiple ways to do this, but given my situation of being remote and only having the .iso files the solution I found seemed to do the trick.

Comments { 0 } Posted on March 8, 2009 in SQL Server 2000, SQL Server 2005

SqlSecurityHandler is invoked

I had an admin come to me this morning regarding a SQL Server box that had suddenly gone down. The SQL Service and SQL Agent where both stopped. When I looked at the SQL Server error log I noticed the following error: “SqlSecurityHandler is invoked. Potential buffer overrun detected – server is terminating.” I went to the trusty google box and didn’t find a ton of useful info, but I did finally come across a KB from Microsoft.

This SQL Server is handling a third-party database whose developers have decided it’s ok to use “SELECT *” in many of their queries. They have also opted to not use stored procedures alone. Both of which I think are bad ideas. They have deadlocks that pop up all of the time, and I see many holes in their reasoning but there’s not a ton I can do to change any of that. They’ve never tested under the load that they are getting with us, so I believe they are learning the hard way that their product might not be as scalable as they thought.

Anyway, I’ve run many a trace against the box through perfmon, profiler and DBCC TRACEON without incidence, but it turns out that it’s possible to crash the server when the following criteria are met:

“The SQL Server process may end unexpectedly when you turn on trace flag -T1204 and a profiler trace is capturing the Lock:DeadLock Chain event in SQL Server 2000 SP4”. We were able to determine that the vendor had been running a profiler trace capturing that event during the time that this happened.  The server was patched up to 2040, but obviously this hotfix is supposed to fix this issue. I decided this would be a good time to go ahead and bring it up to hotfix 2273 given the problems that we’ve had. That means I chose to bypass 2050 and go with 2273. That means I chose QFE over GDR. I’ve included a link to an explanation that helped me understand those two items. Normally, it would be recommended that you stay with the GDR unless you need a specific fix above that hotfix. At least that’s the way I understood it.

Cumulative list of SQL Server 2000 hotfixes

GDR vs QFE

Microsoft Security Bulletin MS08-040

Comments { 0 } Posted on February 12, 2009 in SQL Server 2000