--------------------------------------------------------------------- The information in this article applies to: - Microsoft SQL Server, version 6.5 Service Pack 4 --------------------------------------------------------------------- The following is a list of fixes and other various improvements that have been made in the Microsoft SQL Server version 6.5 Service Pack 4. For more information, contact your primary support provider. Please note that workarounds described in these articles have been provided for your information only. It is not necessary to implement these workarounds if you have the updated software. For more information about each corrected problem in SQL Server version 6.5 Service Pack 4, use the Qxxxxxx number to query for the article in the Microsoft Knowledge Base. ============================================ LIST OF PROBLEMS CORRECTED IN SERVICE PACK 4 ============================================ Q175887 BUG: Msg 1203 & SQL Server May Be Shut Down w/ Large Cursor Row Q175546 BUG: Trace Flag 1211 Does Not Work Properly Q175303 BUG: Database Usage Count Does Not Return to Zero Q175143 BUG: Err 1203 If Deadlock Occurs During Concurrent ORDER BY DESC Q175097 BUG: Non-Optimal Performance for Views Defined with a Subquery Q174512 BUG: A Stored Procedure or Trigger May Cause Error 707 or an AV Q174479 BUG: Lazywriter Access Violation During DUMP Activity Q174221 BUG: Text Col. Update Through Cursor > 1,988 Bytes Gets Err. 702 Q173932 BUG: Heavy Stress on Tempdb w/ Multi-CPU Computer May Cause AVs Q172557 BUG: Lazy Writer Process May Cause an Access Violation Q172549 BUG: T-SQL Cursors Ignore NOCOUNT Query Option & Trace Flag 364 Q171865 BUG: UPDATE with FORCEPLAN ON May Cause Error 806 in Tempdb Q171369 BUG: Delete/Insert on Table with Text/Image May Cause Error 38 Q170508 BUG: Blocking Lock Remains or Server Stops After Heavy Deadlock Q170507 BUG: READTEXT May Fail w/ Msg 7134 If Hard Coding Text Pointer Q170499 BUG: Unhandled AV During Concurrent DUMP DATABASE Commands Q170437 BUG: Multi-Session DBCC OUTPUTBUFFER Under Stress Causes AV Q170436 BUG: Infrequent Access Violation During LOAD TRANSACTION Q170090 BUG: Recovery May Fail w/Error 6902 and Mark Database as Suspect Below are excerpts from each of the articles listed above. For the full text of the articles, search for the article number in the Microsoft Knowledge Base. --------------------------------------------------------------- BUG: Msg 1203 & SQL Server May Be Shut Down w/ Large Cursor Row --------------------------------------------------------------- ARTICLE-ID: Q175887 BUG #: 17175 SYMPTOMS ======== SQL Server may recursively print Msg 1203 and be shut down after a stack overflow exception error. This problem can occur if all of the following conditions are true: - The "LE threshold maximum" configuration option is set so that an individual cursor rowset fetch may cause a lock escalation to occur. - A keyset cursor fetch becomes blocked by another user and subsequently escalates to a table lock after the blocking is resolved. - The table has a unique index or primary key that can be used to build the keyset table for the cursor. Note that if this condition is false, the cursor reverts to an INSENSITIVE cursor, and the problem does not occur. The following is the text of message 1203: Caller of lock manager is incorrectly trying to unlock an unlocked object. spid=10 locktype=6 dbid=6 lockid=645. WORKAROUND ========== To work around the problem, do either of the following: - Use a smaller rowset size for your cursor -or- - Increase the "LE threshold maximum" configuration option to a higher value so that a single cursor fetch rowset will not need to traverse enough pages to escalate to a table lock. For example, if the maximum row size within your tables allows for 1 row per page, and you have the cursor rowset configured to pull back 500 pages at a time, then the "LE threshold maximum" should be larger than 500. If you use the default escalation value of 200 and only have 1 row per page, use a cursor rowset size smaller than 200. ------------------------------------------- BUG: Trace Flag 1211 Does Not Work Properly ------------------------------------------- ARTICLE-ID: Q175546 BUG #: 17001 SYMPTOMS ======== SQL Server 6.5 Service Pack 3 (build 6.5.258) introduced trace flag 1211 to provide additional lock management functionality, such as enabling retry on allocation lock collision. However, if you enable "lock retries" through this trace flag, either at server startup or with the global TRACEON command, you will not see the enhanced behavior. ------------------------------------------------- BUG: Database Usage Count Does Not Return to Zero ------------------------------------------------- ARTICLE-ID: Q175303 BUG #: 17305 SYMPTOMS ======== When all users have logged out of a particular database, the database may still have a usage count of greater than 0. If it does, errors will occur when you attempt to set the database to single user mode, or when you try to drop the database. To see if you are having this problem, perform the steps listed in the MORE INFORMATION section of this article. WORKAROUND ========== To work around this problem, shut down SQL Server and restart it. Doing this brings the usage count back to zero. ---------------------------------------------------------------- BUG: Err 1203 If Deadlock Occurs During Concurrent ORDER BY DESC ---------------------------------------------------------------- ARTICLE-ID: Q175143 BUG #: 17334 (6.5) SYMPTOMS ======== If deadlocks occur during concurrent ORDER BY DESC queries, a 1203 error may occur and you receive the error message: Caller of lock manager is incorrectly trying to unlock an unlocked object. spid=%d locktype=%d dbid=%d lockid=%Id This may eventually destabilize the server and result in stuck locks. After the error 1203, you may see this as a chain of blocked spids that does not clear up even after you kill the spid at the head of the chain. This is a regression starting with SLQ Server version 6.5 Service Pack 2. The problem is not specific to multiprocessor computers. WORKAROUND ========== Use standard techniques to reduce locking contention and deadlocks. This can include shortening transaction path length, using lower transaction isolation levels, eliminating extraneous indexes, or insuring that transactions acquire locks in the same order. Reducing usage of ORDER BY DESC queries may also help. -------------------------------------------------------------- BUG: Non-Optimal Performance for Views Defined with a Subquery -------------------------------------------------------------- ARTICLE-ID: Q175097 BUG #: 16803 SYMPTOMS ======== If you run a query on a view defined with a subquery in its select list, you may experience impaired performance. However, a direct query for the equivalent select with no view (with or without a predicate against the view) yields much faster performance. WORKAROUND ========== To improve performance, do either of the following: - Execute the query on the underlying tables rather than on the view. -or- - Create the view so that it does not contain a subquery for the select list. NOTE: Views created under versions of SQL Server earlier than 6.5 Service Pack 1 with a subquery in the select list will continue to work, even under SQL Server 6.5 Service Pack 1 and later, provided that they are not re-created under these later versions of SQL Server. --------------------------------------------------------------- BUG: A Stored Procedure or Trigger May Cause Error 707 or an AV --------------------------------------------------------------- ARTICLE-ID: Q174512 BUG #: 16929 SYMPTOMS ======== If a stored procedure or trigger is created that does not contain active code (which means it only contains comments and/or DECLARE statements), SQL Server may stop responding. In addition, the following error may appear in the errorlog: Error message 707 severity 20 System error detected during attempt to free memory at address 0x%lx. After the message appears in the errorlog, SQL Server will stop responding and become inaccessible. Instead of the error message, SQL Server may experience a handled access violation, which also causes the server to stop responding. This will appear in the errorlog as the following message, followed by a stack trace: 97/06/18 09:25:21.89 spid293 EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump 97/06/18 09:25:21.91 spid293 ***BEGIN STACK TRACE*** WORKAROUND ========== To avoid this problem, make sure all triggers and stored procedures have some active code other than just DECLARE statements and/or comments. ----------------------------------------------------- BUG: Lazywriter Access Violation During DUMP Activity ----------------------------------------------------- ARTICLE-ID: Q174479 BUG #: 17185 SYMPTOMS ======== During high activity of the Lazywriter process, a DUMP command may result in an access violation (AV) of Lazywriter. The following error appears in the errorlog: kernel lazywriter: Process 2 generated access violation; SQL Server is terminating this process The DUMP command is not affected by this error. WORKAROUND ========== To work around this problem, schedule DUMP activities to occur during lowest activity for SQL Server. ---------------------------------------------------------------- BUG: Text Col. Update Through Cursor > 1,988 Bytes Gets Err. 702 ---------------------------------------------------------------- ARTICLE-ID: Q174221 BUG #: 17192 (sqlbug_65) SYMPTOMS ======== If you are using sp_cursor open to perform updates through a cursor and the sum of columns is greater than 1,988 bytes (or the sum of all columns updated totals more than 1,988 bytes) the following error 702 will occur: Msg 702, Level 20, State 1 Memory request for 2046 bytes exceeds the size of single page of 2044 bytes. The SQL Server is terminating this process. The number of bytes exceeded will change, depending on the size of theupdate. WORKAROUND ========== To work around this problem, either use ANSI cursors or limit the size of the TEXT/IMAGE update being done through the cursor. --------------------------------------------------------------- BUG: Heavy Stress on Tempdb w/ Multi-CPU Computer May Cause AVs --------------------------------------------------------------- ARTICLE-ID: Q173932 BUG #: 17113 SYMPTOMS ======== You may receive "max connection" errors, access violations (AVs) and/or 600- level errors if you are using a multi-processor computer and tempdb is under heavy stress (creating or dropping tables, using SELECT INTO statements, and so on). In the case of "max connection" errors, the server goes into a spin loop and stops responding. The following is an example of an error message you may receive: 97/07/12 03:30:43.53 ods Unable to connect. The maximum number of ''''128'''' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure. The following are some example AVs (taken from Debugsr.exe 6.50.262): 97/07/12 03:06:10.07 spid11 ***BEGIN STACK TRACE*** 97/07/12 03:06:10.18 spid11 0x005DD5BC in debugsr.EXE, getobject() + 0x01EC 97/07/12 03:06:10.34 spid11 0x005A6A34 in debugsr.EXE, droptempo() + 0x0636 97/07/12 03:06:10.40 spid11 0x0051C4A3 in debugsr.EXE, tmp_alldrop() + 0x014A 97/07/12 03:06:10.47 spid11 0x00511281 in debugsr.EXE, s_free() + 0x005A 97/07/12 03:06:10.47 spid11 0x0051118A in debugsr.EXE, s_pop() + 0x00B9 97/07/12 03:06:10.50 spid11 0x0055D492 in debugsr.EXE, execproc() + 0x11EB 97/07/12 03:06:10.55 spid11 0x00559751 in debugsr.EXE, s_execute() + 0x0E81 97/07/12 03:06:10.62 spid11 0x0050E5EC in debugsr.EXE, sequencer() + 0x035C 97/07/12 03:06:10.67 spid11 0x004156C1 in debugsr.EXE, language_exec() + 0x06DD 97/07/12 03:06:10.74 spid11 0x00250FED in opends60.dll 97/07/12 03:06:10.74 spid11 0x0025055B in opends60.dll 97/07/12 03:06:10.74 spid11 0x002414D1 in opends60.dll 97/07/12 03:06:10.74 spid11 0x00241384 in opends60.dll 97/07/12 03:06:10.74 spid11 0x10219D84 in MSVCRT40.dll 97/07/12 03:06:10.74 spid11 0x77F04F2C in KERNEL32.dll 97/07/12 03:06:10.75 spid11 ***END STACK TRACE*** 97/07/12 03:06:11.16 kernel The current contents of process'''' input buffer are ''''exec p1''''. Limited testing has shown this problem only occurs in an extremely narrow scope where both a high performance server and heavy volume activity are involved. ------------------------------------------------------ BUG: Lazy Writer Process May Cause an Access Violation ------------------------------------------------------ ARTICLE-ID: Q172557 BUG #: 17017 SYMPTOMS ======== If tempdb is in RAM, heavy tempdb usage may cause the lazy writer processes to have a handled access violation (AV). This will appear in the SQL Server errorlog as the following message: Lazywriter: Process 2 generated access violation SQL Server is terminating this process This message may occur once or hundreds of times, and can also cause SQL Server to eventually stop responding. WORKAROUND ========== To work around this problem, do either of the following: - Move tempdb out of RAM to a physical disk. -or- - Reduce tempdb usage. --------------------------------------------------------------- BUG: T-SQL Cursors Ignore NOCOUNT Query Option & Trace Flag 364 --------------------------------------------------------------- ARTICLE-ID: Q172549 BUG #: 17091 SYMPTOMS ======== When executing a Transact-SQL cursor, the cursor returns empty result sets (DONE_IN_PROC messages) when NOCOUNT or trace flag 3640 is enabled. This means that if you do 100 fetch operations into local variables, the client must process 100 empty result sets. ----------------------------------------------------------- BUG: UPDATE with FORCEPLAN ON May Cause Error 806 in Tempdb ----------------------------------------------------------- ARTICLE-ID: Q171865 BUG #: 17089 SYMPTOMS ======== If an UPDATE trigger fires and contains an UPDATE that joins with the inserted table, the following error may occur if SET FORCEPLAN is set ON and the sysindexes count for syslogs number of pages is high: Msg 806, Level 21, State 1 Could not find virtual page for logical page in database 'tempdb' WORKAROUND ========== To work around this problem, look for ways to avoid using SET FORCEPLAN ON with UPDATE statements including proper index design. If you truncate the transaction log and/or run DBCC CHECKTABLE(syslogs) to update the sysindexes entries for syslogs, it is possible that the problem will not occur. -------------------------------------------------------------- BUG: Delete/Insert on Table with Text/Image May Cause Error 38 -------------------------------------------------------------- ARTICLE-ID: Q171369 BUG #: 15717, 17010 SYMPTOMS ======== In a user-defined transaction, if a row is deleted and then followed immediately with an INSERT operation to a table containing one or more columns of a text or image datatype, the following error messages may be received by the client application: Msg 5159, Level 16, State 1 OS Error 38(Reached end of file.) on device 'C:\MSSQL\DATA\MASTER.DAT' during bufget The SQL Server is terminating this process. In addition, the following errors may be recorded in the errorlog: - udread: Operating system error 38(Reached end of file.) on device 'C:\SQL60\DATA\MASTER.DAT' (virtpage 0x00ffff00). - mirrorproc: i/o error on primary device 'C:\SQL60\DATA\MASTER.DAT' Buffer 1352970 from database 'SMS' has page number 257 in the page header and page number -256 in the buffer header - udread: Operating system error 6(The handle is invalid.) on device 'C:\SQL60\DATA\MASTER.DAT' (virtpage 0x000005a4). WARNING: Process being freed while holding Dataserver semaphore In SQL Server 6.5, SQL Server stops responding with a 100 percent CPU spin. This behavior was not observed under SQL Server 6.0. WORKAROUND ========== To work around this problem, to not place the DELETE and INSERT operations in the same user-defined transaction. --------------------------------------------------------------- BUG: Blocking Lock Remains or Server Stops After Heavy Deadlock --------------------------------------------------------------- ARTICLE-ID: Q170508 BUG #: 16986, 15716 SYMPTOMS ======== Under heavy deadlock conditions, certain locking patterns may cause blocking locks to remain. This may appear as a spid exiting without lock cleanup. For example a spid holding blocking locks may be KILLed or exit, yet the locks it held may remain and continue to block others. Afterwards sp_who may show the blocking spid as a newly-logged in process that holds no locks in the database, yet is blocking others. This condition may infrequently occur on SQL Server builds 6.00.121 through 6.50.258. The sysprocesses.waittype for the blocked or blocking spids is usually 0x8003 (waiting on exclusive intent lock) or 0x8004 (waiting on shared intent lock). In some cases, a blocking spid may have a 0x8003 or 0x8004 waittype (hence not running, and will continue blocking others), yet not itself be blocked as indicated by sysprocesses.blocked. Less frequently, the server may stop responding following this occurrence. Killing blocked spids under these conditions can cause the debug assertions described below (taken from SQL Server build 6.50.255). SQL Server version 6.0 does not have assertions, but the overall behavior is similar. On SQL Server 6.0, sometimes you will see sysprocesses.blocked having an irrational value such as 4832. The best way to identify this problem on SQL Server 6.5 is to run a debug server, watch for abnormal blocking behavior, and observe whether the following assertions occur: Location: lockmgr.c: 5903 Expression: lo Spid: 11 Description: pss not found on the wait list Location: lockmgr.c: 2317 Expression: pss->pssnext==NULL Spid: 11 For more information on what constitutes normal or abnormal blocking, see the following article in the Microsoft Knowledge Base: ARTICLE-ID: Q162361 TITLE : Understanding and Resolving SQL Server Blocking Problems WORKAROUND ========== To work around this problem, use standard techniques to reduce locking contention and deadlocks. This can include shortening the transaction path length, using lower transaction isolation levels, eliminating extraneous indexes, or ensuring that transactions acquire locks in the same order. -------------------------------------------------------------- BUG: READTEXT May Fail w/ Msg 7134 If Hard Coding Text Pointer -------------------------------------------------------------- ARTICLE-ID: Q170507 BUG #: 17087 SYMPTOMS ======== The READTEXT statement will fail with "Msg 7134, Level 16, State 2" if both of the following conditions are true: - The text pointer provided is hard coded. -and- - The READTEXT statement is the first statement of the batch. WORKAROUND ========== To work around the problem, do either of the following: - Use a local variable to store the text pointer, instead of directly hard coding the text pointer value. -or- - Make sure that the READTEXT statement is not the first statement in the batch. You can consider doing a "dummy" select, such as "select @@version". ---------------------------------------------------------- BUG: Unhandled AV During Concurrent DUMP DATABASE Commands ---------------------------------------------------------- ARTICLE-ID: Q170499 BUG #: 16957 SYMPTOMS ======== An unhandled access violation (AV) with the stack trace shown below may occur very infrequently while two or more concurrent DUMP DATABASE commands are running. Due to the unhandled nature of the AV, the stack trace will only be visible if the Windows NT Server symbols are installed and either of the following conditions are true: - You are running the server under a debugger. -or- - A just-in-time debugger attaches to the SQL process upon the AV. The following is the stack trace: ntdll!RtlUnwind+0xd2 MSVCRT40!global_unwind2+0x18 MSVCRT40!longjmpex+0x18 WORKAROUND ========== Current evidence indicates the problem may occur when an attention event, such as dbcancel(), is received when two or more database dumps are running. Try running the dumps in series instead of in parallel. If this is not sufficient, try to avoid query cancellation during database dump. If this is not possible, put the server in single-user mode before doing a database dump. ----------------------------------------------------------- BUG: Multi-Session DBCC OUTPUTBUFFER Under Stress Causes AV ----------------------------------------------------------- ARTICLE-ID: Q170437 BUG #: 17005, 15719 SYMPTOMS ======== Running frequent DBCC OUTPUTBUFFER commands from more than one concurrent session while the system is under transactional stress may cause a handled access violation (AV). After this AV, the server may stop responding. This problem happens on all versions of SQL Server from 6.00.121 through 6.50.258, but it only happens on: - SMP computers with three or more processors. - Two-processor computers with smp concurrency set to -1. If you are running a debug server (needed for accurate stack traces on SQL Server 6.5), you can identify the problem by one of the following two call stacks from 6.50.255 debug: The following stack is less frequent: debugSR!ubchkalloc+0x22 debugSR!memshrink+0xb8 debugSR!s_pop+0x23e debugSR!s_pop+0xb9 debugSR!execproc+0x11eb debugSR!execrpc+0xd07 The following stack is more frequent: debugSR!ubfree+0x96 debugSR!memfree+0x215 debugSR!procfree+0xc8 debugSR!procrm2+0x17a debugSR!procrm+0xd7 debugSR!s_pop+0x22d debugSR!s_pop+0xb9 debugSR!s_cleanframe+0x2fb debugSR!s_recompile+0x942 debugSR!ex_raise+0x37a debugSR!prepscan_failed+0x23 debugSR!prepscan+0x367 debugSR!startscan+0x377 debugSR!exec_substitute+0x435 debugSR!exec_eop+0x493 debugSR!exec_eop+0x1e2 debugSR!execute+0x2c0 debugSR!s_execute+0xbf8 debugSR!sequencer+0x35c WORKAROUND ========== To work around this problem, do not issue multiple concurrent DBCC OUTPUTBUFFER statements. -------------------------------------------------------- BUG: Infrequent Access Violation During LOAD TRANSACTION -------------------------------------------------------- ARTICLE-ID: Q170436 BUG #: 16946 SYMPTOMS ======== You may infrequently experience a handled access violation (AV) during a LOAD TRANSACTION. If you are running a debug version of SQL Server 6.5 (needed to ensure an accurate stack trace), you can determine that this AV occurs in the ldxact() function. If you use a LOAD DATABASE statement to reload the database and then use LOAD TRANSACTION to reapply the same transaction, the problem usually does not occur again. The following is the stack trace from a 6.50.252 debug server: ldxact() + 0x178C s_execute() + 0x18FC sequencer() + 0x035C language_exec() + 0x06D6 WORKAROUND ========== To work around this problem, reload the database then reapply the same transaction files with LOAD TRANRANSACTION. ---------------------------------------------------------------- BUG: Recovery May Fail w/Error 6902 and Mark Database as Suspect ---------------------------------------------------------------- ARTICLE-ID: Q170090 BUG #: 16964 SYMPTOMS ======== LOAD TRAN or recovery may fail with the following error and mark the database as suspect if UPDATETEXT was used to insert at least two text/image pages into the middle of an existing text/image column: 6902 21 Page timestamp value falls between the old and new timestamps from log. Page #=%ld, object id = %ld, page timestamp=%04x %08lx. Log: old timestamp=%04x %08lx, new timestamp=%04x %08lx. WORKAROUND ========== To work around this problem, avoid using UPDATETEXT to insert data into the middle of existing text/image pages. However, UPDATETEXT works fine if new data is appended to the end rather than inserted into the middle.