BUG: DTC Transactions May Fail When SQL Server Is Running in Lightweight Pooling Mode (303287)

SYMPTOMS

When SQL Server is run in "lightweight pooling" 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:
1. Open SQL Server Enterprise Manager.
2. Click the + sign next to the server name.
3. Expand the Management folder.
4. Right-click SQL Server Agent and click Properties.
5. 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.
6. Click the View button to the right of this field to view the SQL Server Agent Log.
Transactional DTC behavior may be limited or not as expected.

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:

In the SQL Server errorlog, check approximately the seventh line from the top of the errorlog for the currently configured fiber mode:

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.

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 "1", SQL Server is configured for fiber-mode processing.
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.

To change from fiber-mode processing to thread-mode processing, you can do either of the following:

From SQL Server Enterprise Manager, follow these steps:
1. Open SQL Server Enterprise Manager.
2. Right-click the server name, and then click Properties.
3. Click the Processor tab.
4. Clear the Use Windows NT Fibers check box.
-or-
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.

BUG: DTC Transactions May Fail When SQL Server Is Running in Lightweight Pooling Mode (303287)

SYMPTOMS

CAUSE

WORKAROUND

STATUS