Category: Administration

Priyanka Chouhan talks about identifying and handling blocked processes:

In order to maintain data integrity within the database, locks are used on resources like tables, rows, pages etc. by any process that wishes to use them. This is done to ensure multiple process don’t alter the same resources at one time leading to data inconsistency. When a process wishes to lock a resource, it sends a request to the server and the server grants it. However, when a process requests lock on a resource that has already been locked by another process, the request is denied. The requesting process is thus placed on “hold” until the resource it is requesting for isn’t released. In this situation, the requesting process is called a blocked process, and such a process could put a halt on other subsequent processes and activities scheduled on the server.

Thus identifying a blocked process and releasing it requires a DBA team to check the application database blocking. Additionally, here are some other techniques that may be used to find out which processes are creating a block on the server:

My favorite method, not mentioned, is Adam Machanic’s sp_whoisactive.

Sean McCown goes into fixing one example of the “Cannot Generate SSPI Context” error:

Now, this was just a quick tutorial on how to manage SPNs.  This hole can go pretty deep.  Here’s a decent link on MSDN for troubleshooting SPNs.  I don’t think I like their troubleshooting because they don’t really do a good job of showing you the commands, but it’s a good explanation of the problem, what an SPN is, etc.  If I remember correctly it’ll also help you choose the right SPN.

This is a classic example of a bad Microsoft error.  In this case, it’s bad because there are multiple root causes for the same error and because the message itself is unhelpful.