Select the product you need help with

When you use a file handle for FileStream access in a SQL Server 2008 transaction, the transaction may randomly fail to commit in Windows XP

Article ID: 957613 - View products that this article applies to.

SYMPTOMS

On a Windows XP-based computer, when you use the OpenSqlFilestream API or the .NET SqlFilestream class to obtain a file handle for FileStream access in a transaction in Microsoft SQL Server 2008, the transaction may randomly fail to commit. When this problem occurs, one of the following errors is logged in the SQL Server Errorlog file:

3910 (Transaction context in use by another session)
3981 (The transaction operation cannot be performed because there are pending requests working on this transaction)
3922 (Cannot enlist in the transaction because the transaction does not exist)

The cause of this problem is not related to the FILESTREAM feature in SQL Server 2008. However, you typically encounter this problem when you use the FILESTREAM feature in SQL Server 2008.

Back to the top | Give Feedback

CAUSE

This problem occurs because of an error in the Server service. The Server service incorrectly delays closing the file handle. Therefore, SQL Server 2008 cannot perform commit operations.

Back to the top | Give Feedback

RESOLUTION

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

(http://support.microsoft.com/contactus/?ws=support)

Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

To apply this hotfix, you must have Windows XP Service Pack 2 or a later service pack installed on the computer. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

322389

(http://support.microsoft.com/kb/322389/ )

How to obtain the latest Windows XP service pack

Restart requirement

You must restart the computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace any other hotfixes.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Windows XP with Service Pack 2, x86-based versions

Collapse this tableExpand this table

File name	File version	File size	Date	Time	Platform
Srv.sys	5.1.2600.3442	333,056	08-Sep-2008	10:12	x86

Windows XP with Service Pack 3, x86-based versions

Collapse this tableExpand this table

File name	File version	File size	Date	Time	Platform
Srv.sys	5.1.2600.5671	333,824	08-Sep-2008	11:37	x86

Back to the top | Give Feedback

WORKAROUND

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756

(http://support.microsoft.com/kb/322756/ )

How to back up and restore the registry in Windows

Warning This workaround will cause a performance issue when you operating on shared files in the system.

To work around this problem, set the value of the Cachedopenlimit registry entry to 0 , and then restart the Server service. To do this, follow these steps:

In Registry Editor, locate the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Lanmanserver\Parameters
Under the Parameters registry subkey, click Edit, click New, click DWORD Value, type Cachedopenlimit, and then press ENTER.
Set the decimal value of the Cachedopenlimit registry entry to 0.
Restart the Server service.

Note By default, the value of the Cachedopenlimit registry entry is 5 , even though you did not set it.

Warning When you set the value to 0 , file handles of all the shared files are not cached by the system. This can cause a performance issue when you are accessing shared files in the system.

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top | Give Feedback

MORE INFORMATION

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

955280

(http://support.microsoft.com/kb/955280/ )

When you use a file handle for FileStream access in a SQL Server 2008 transaction, the transaction may randomly fail to commit in Windows Server 2003 or in Windows XP Professional x64 Edition

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

953263

(http://support.microsoft.com/kb/953263/ )

When you use a file handle for FileStream access in a SQL Server 2008 transaction, the transaction may randomly fail to commit in Windows Vista or in Windows Server 2008

When to use the FILESTREAM storage

In SQL Server 2008, binary large object (BLOB) data can be the data of the VARBINARY(MAX) type that stores data in database tables. BLOB data can also be the data of the VARBINARY(MAX) FILESTREAM type that stores the data in the file system. The data size and the data usage determine whether you should use the database storage or the FILESTREAM storage. You should consider using the FILESTREAM storage when the following conditions are true:

On average, the BLOB data that you store is larger than 1 megabyte (MB).
Fast-read access is important.
You are developing applications that use a middle tier for the application logic.

If the BLOB data is less than 1 MB, you should consider using the database storage. The database storage frequently provides better streaming performance.

For more information, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/en-us/library/bb933993.aspx

(http://msdn.microsoft.com/en-us/library/bb933993.aspx)

Back to the top | Give Feedback