First published on MSDN on May 13, 2010
One of my customers was experiencing a lot of event 2000 errors on the SQL Server Machine. These were happening every time when they call the Write method to a SQLfilestream from an XP client. The issue occurs only on XP Client OS and not on any other Operating System.
Below is the
OpenSQLFileStream
API Call to write a BLOB into a SQL Server database.
SafeFileHandle handle = OpenSqlFilestream(
path,
DESIRED_ACCESS_WRITE,
SQL_FILESTREAM_OPEN_NO_FLAGS,
txCtx,
(UInt32)txCtx.Length,
0);
The functionality of the application is not affected but we still see below error message being logged in the windows system event logs.
Log Name: System
Source: srv
Date: 4/10/2010 2:32:12 AM
Event ID: 2000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: <MachineName>.microsoft.com
Description:
The server's call to a system service failed unexpectedly.
Whenever we call OpenSQLFileStream to write a BLOB to the SQL Server, Internally it calls CreateFile method which is a windows API Call. The OpenSqlFilestream API obtains a Win32 compatible file handle for a FILESTREAM binary large object (BLOB) that is stored in the file system. The handle can be passed to any of the following Win32 APIs: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile, or FlushFileBuffers.
One way to prevent this error from occurring on an XP client is to change the FLAGS passed to OpenSQLFileStream from SQL_FILESTREAM_OPEN_NO_FLAGS to SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING.
SafeFileHandle handle = OpenSqlFilestream(
path,
DESIRED_ACCESS_WRITE,
SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING,
txCtx,
(UInt32)txCtx.Length,
0);
When opening or creating a file with the CreateFile function, the FILE_FLAG_NO_BUFFERING flag can be specified to disable system caching of data being read from or written to the file.
This flag eliminates all read-ahead file buffering and disk caching, so that all reads are guaranteed to come from the file and not from any system buffer or disk cache.
So the issue has nothing to do with SQL Server specifically. It is normally recommended is to use SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING flag with OpenSQLFileStream function.
Author : Praveen(MSFT), SQL Developer Technical Lead , Microsoft
Reviewed by : Tejas(MSFT), SQL Escalation Services, Microsoft
