Microsoft KB Archive/303287

= BUG: DTC Transactions May Fail When SQL Server Is Running in Lightweight Pooling Mode =

Article ID: 303287

Article Last Modified on 9/25/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q303287



BUG #: 233167 (SHILOH_BUGS)

BUG #: 236303 (SHILOH_BUGS)



SYMPTOMS
When SQL Server is run in &quot;lightweight pooling&quot; mode (fiber mode) and the DTC service is started, unexpected behavior may occur. Symptoms may include:  SQL Server Agent does not execute any jobs. When you try to start a job manually or attempt to create new jobs, you may receive the following error message:

Error 14258: cannot perform this operation while SQL server agent is starting. Try again later.

  SQL Server Agent does not appear to start up completely, with the following message in the SQL Server Agent log: Waiting for SQL Server to recover databases. NOTE: To view the SQL Server Agent Log, follow these steps:  Open SQL Server Enterprise Manager. Click the + sign next to the server name. Expand the Management folder.</li> Right-click SQL Server Agent and click Properties.</li> In the Error Log section on the General tab, check to see if the File Name field is filled in; if it is, the SQL Server Agent log is set up.</li> Click the View button to the right of this field to view the SQL Server Agent Log.</li></ol> </li> Transactional DTC behavior may be limited or not as expected.</li></ul>

<div class="cause_section">

CAUSE
The internal design that SQL Server uses to protect the DTC transaction is not appropriate when running in fiber mode.

<div class="workaround_section">

WORKAROUND
If DTC operations are required on the server, the SQL Server instance should always run in thread mode; in other words, lightweight pooling set to zero(0). Microsoft strongly recommends that you run the SQL Server instance in thread mode when DTC is needed.

Use of fiber mode should be limited to those high-end systems with a specific need to utilize it. If you use fiber mode on a system that does not have a specific need, it can often degrade performance.

To check whether fiber mode is turned on for your SQL Server, follow these steps: <ol>  In the SQL Server errorlog, check approximately the seventh line from the top of the errorlog for the currently configured fiber mode: <pre class="fixed_text">2002-02-21 15:37:28.06 server   Copyright (C) 1988-2000 Microsoft Corporation. 2002-02-21 15:37:28.06 server   All rights reserved. 2002-02-21 15:37:28.06 server   Server Process ID is 2272. 2002-02-21 15:37:28.06 server   Logging SQL Server messages in file 'H:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'. 2002-02-21 15:37:28.07 server   SQL Server is starting at priority class 'normal'(2 CPUs detected). 2002-02-21 15:37:28.26 server   Working Set size set to 1669632 kilobytes. 2002-02-21 15:37:28.29 server   SQL Server configured for fiber mode processing. </li> Execute the sp_configure stored procedure to check for the configuration value of Fiber Mode Processing. In the sp_configure output, if Fiber Mode Processing, config_value, and run_value show a value of &quot;1&quot;, SQL Server is configured for fiber-mode processing.</li> You can also check this by using Enterprise Manager. Right-click the server, click Properties, and then click Processor. If the Use Windows NT Fibers option is selected, SQL Server is configured for fiber-mode processing.</li></ol>

To change from fiber-mode processing to thread-mode processing, you can do either of the following: <ul> From SQL Server Enterprise Manager, follow these steps: <ol> Open SQL Server Enterprise Manager.</li> Right-click the server name, and then click Properties.</li> Click the Processor tab.</li> Clear the Use Windows NT Fibers check box.</li></ol>

-or-

</li>  In SQL Server Query Analyzer, execute the following code: SP_CONFIGURE 'ALLOW UPDATES', 1 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'lightweight pooling', 0 GO RECONFIGURE WITH OVERRIDE GO You will need to run the above script from SQL Server Query Analyzer after you connect to the SQL Server as either 'sa' or an administrator account.

You will need to stop and restart SQL Server for the settings to take effect. </li></ul>

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.

Keywords: kbbug kbpending KB303287

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.