Checking for blocked queries (SQL Header Block)

Sometimes an SQL query may become stuck in the SQL server which may require manual intervention to free up. Typically, this might present as Time Out or related errors. 

If a server is under resourced for the number of users and activity in Eclipse the SQL server may process queries slowly - also presenting as Time Out or other errors with Eclipse software. However, these queries will often self-resolve after a period of time. It is still recommended to address the shortfall in resources to maximise performance and usability of Eclipse.

Checking for stuck SQL queries

For administrators with access to the SQL server, stuck queries also called a "header block" can be diagnosed from SQL Server Management Studio (SSMS).

1. Open SQL Server Management Studio (SSMS).
2. When prompted connect to the appropriate database engine (either using 'sa' user or the Windows local administrator).
3. Right click on the connected SQL Server and click Activity Monitor.
   
4. Open the Processes section, then click to sort by the Header Blocker column.
5. If processes are found to be stuck, this column will contain extra. Please note, the process that is causing the blocking is indicated in the Blocked By column.
   
6. Once identified, you can right click on the offending process and click Kill Process. This should only be done as a last resort and Eclipse Support should be informed (ideally capturing the related query information before killing the process - see below).
   

Further Investigation

Killing a blocked process may temporarily resolve an issue, but further investigate should be taken - especially if the issue is recurring.

Before killing a blocked process, you can run the following command to obtain the details of query.

1. Open a new query window (make sure the appropriate database is selected).
2. Run the following (where # is the process number):
   

   dbcc inputbuffer(#)

3. Right click and copy the query (under EventInfo column).