次の方法で共有

Every time I ‘ATTACH DATABASE’ SQL logs error 1314 for SetFileIoOverlappedRange

  • [アーティクル]

Turns out this is an issue in the SQL Server code and the error is a bit noisy during attach database.

When opening the database files, SQL Server calls SetFileIoOverlappedRange (when enabled properly) in order to help improve I/O performance.  This is commonly done under the SQL Server, service account; which requires locked pages privilege.  When the privilege is not held the Windows error (1314 - A required privilege is not held by the client) is logged in the SQL Server error log, shown below.

Starting with SQL Server 2005, when attaching a database, SQL Server impersonates the client connection when opening the files to validate proper security (ACLs.)   In doing so the SQL Server invokes SetFileIoOverlappedRange under the impersonated account and not the SQL Server, service account.   This can lead to the 1314 error condition.

The error is more noise than a problematic issue.   Using ALTER DATABASE OFFLINE and ONLINE will re-open the database files under the SQL Server, service account and allow SetFileIoOverlappedRange to complete successfully for the database.

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

                Oct 19 2012 13:38:57

                Copyright (c) Microsoft Corporation

                Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

2013-10-16 03:23:20.010 Server Using locked pages in the memory manager.

                .

                .

                .

2013-10-16 09:21:35.970 spid52 Starting up database 'dbAttachTest'.

2013-10-16 09:25:10.300 spid52 SetFileIoOverlappedRange failed, GetLastError is 1314

 

WARNING – Make sure you have the following applied to avoid unexpected issues as well.

https://support.microsoft.com/kb/2679255 https://blogs.msdn.com/b/psssql/archive/2012/03/20/setfileiooverlappedrange-can-lead-to-unexpected-behavior-for-sql-server-2008-r2-or-sql-server-2012-denali.aspx

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

  • Anonymous
    October 25, 2013
    Hi Bob, Thank you very much for this post!  I'm hoping you can clarify something for me: My environment is: SQL server 2008 R2 SP2 CU1 (version 10.50.4260) Windows Server 2008 R2 with hotfix KB2679255 INSTALLED Trace flag 8903 set In the environment described above, if a database is attached via a user account that does NOT have the locked pages privilege, and the "SetFileIoOverlappedRange failed" error occurs, is this error, as you wrote above, "more noise than a problematic issue"?  Or is there still potential for corruption? As you can probably guess, I'm just looking to understand if it's OK to disregard this message after SP2 CU1 / T8903 / KB2679255 are all installed... Thanks for any information you can provide! Vince Misetich

  • Anonymous
    October 28, 2013
    RDORR The answer is NOISE.  The Windows fix remove the corruption issue.  The trace flag turns on the API call but you just hit the same issue as the blog describes with the attach from a user that does not have privs to lock pages.