Vikram Kamath

February 10, 2007

SQL Server 2005 SP2…… When???

Filed under: SQL Server — Vikram @ 20:45

It has been almost a year since SQL Server 2005 Service Pack 1 (SP1) was released. And now many of us are waiting for the release of Service Pack 2 (SP2).

We found an issue with SQL Server 2005 SP1 last week. It started with the following error in the application which was running seamlessly for every other operation except for one operation, when it was running on SQL server 2005 SP1.

Fatal error 3624 occurred at Feb 5 2007  2:34PM. Note the error and time, and contact your system administrator.

A severe error occurred on the current command.  The results, if any, should be discarded.

Location:  IndexRowScanner.cpp:370
Expression:  m_sizeOfBuffer > currentOffset + colLen
SPID:   54
Process ID:  2712

In the beginning we thought it may be something in our application. But when I checked the process ID in the task manager it turned out to be SQL server 2005 process. The SPID was the application server’s connection to the database.

Immediately we turned to the wonderful SQL Profiler, and this is what we saw:

SQL Server Assertion: File: <IndexRowScanner.cpp>, line=370 Failed Assertion = ‘m_sizeOfBuffer > currentOffset + colLen’. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check
the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

Warning: Fatal error 3624 occurred at Feb 5 2007  3:00PM. Note the error and time, and contact your system administrator.

2007-02-05 15:00:23.04 spid54      Error: 3624, Severity: 20, State: 1.
2007-02-05 15:00:23.04 spid54      A system assertion check has failed. Check the SQL Server error log for details

We investigated further to find out the point of failure. We found that there were certain SQL statements that were being executed against the database because of which SQL server was throwing the assertion error. The common things between these statements were the table being used in the statements and the fields. To be more specific it was to do with all the bit fields in the table. Some of the statements were updating the bit fields, and in some statements the bit fields were part of the where clause. Even a simple statement like SELECT MY_BIT_FIELD FROM MY_TABLE was failing with the same assertion error.

We then went into the MSDN forums and learnt that there were issues with bit fields that have been fixed as part of the SP2. So, I installed the SQL server 2005 SP2 Community Technology Preview (CTP). And to our luck our problem was resolved.

Now the question was, when will SQL server 2005 SP2 RTM be available? I learnt from Jim that it is expected in the first quarter of this year. This means it should be available anytime in the next three months. I hope Microsoft will release it as scheduled. For more information about the same Jim pointed me to SQL Server Manageability Team Blog.

Hope this helps you.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: