FIX: Error messages after SQL Server 2005 or SQL Server 2000 has been running for some time on Windows Server 2003 or on Windows 2000: "Failed to load FileName.dll" and "Attempts to load any type of dll might fail within the SQL Server process"

When you run Microsoft SQL Server 2005 or Microsoft SQL Server 2000 on a Windows Server 2003-based computer or on a Windows 2000-based computer, you may receive an error message that contains the following error messages:

• Failed to load FileName.dll
• Attempts to load any type of dll might fail within the SQL Server process with errors pointing to LoadLibrary failure.

This problem usually occurs after SQL Server has been running for some time.

The complete error message that you receive resembles the following:

This problem occurs because no thread local storage (TLS) slot is available for the SQL Server process (Sqlservr.exe). This is a problem in Windows Server 2003 and in Windows 2000.

When SQL Server unloads a DLL, the TLS slots that the DLL uses may not be released because of a problem in the TlsFree function. If SQL Server frequently loads various DLLs, and then unloads these DLLs, these DLLs use up all the available TLS slots. Eventually, SQL Server can no longer load a DLL that requires TLS slots.

Note This resolution is only for Windows Server 2003. If you are using Windows 2000, follow the steps in the "Workaround" section." section.

Hotfix information for Windows Server 2003

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. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.

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, contact 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: 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

You must have Windows Server 2003 Service Pack 2 installed on the server.

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

Restart information

You do not have to 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 Server 2003 with Service Pack 2, x86-based versions
Windows Server 2003 with Service Pack 2, x64-based versions
Windows Server 2003 with Service Pack 2, IA64-based versions

You can use one of the following methods to work around this problem. We recommend that you use method 1 because the extended stored procedure feature may be removed in future releases of SQL Server.

Method 1

1. Use the sp_OACreate stored procedure to determine the DLLs that SQL Server loads into the SQL Server process. To do this, follow these steps:
   1. Open SQL Server Profiler, and then connect to the instance of SQL Server.
   2. In the Trace Properties dialog box, specify the name of the trace, specify the location where SQL Server Profiler saves the trace, and then click Run.
   3. After you run SQL Server Profiler for some time, analyze the trace to locate the occurrences of the sp_OACreate stored procedure.
   4. Notice the first parameter of each occurrence of the sp_OACreate stored procedure.
      
      Note The first parameter indicates the programmatic identifier (ProgID) of the OLE object or the class identifier (CLSID) of the OLE object.
   5. Determine the DLL that contains this OLE object.
   Note You can use SQL Server Profiler to monitor the instance of SQL Server when you start the SQL Server service.
2. Make sure that you have enabled the OLE Automation procedures option.
   For more information about how to determine whether the OLE Automation procedures option is enabled and how to enable the OLE Automation procedures option, visit the following Microsoft Develop Network (MSDN) Web site:
   http://msdn2.microsoft.com/en-us/library/ms191188.aspx (http://msdn2.microsoft.com/en-us/library/ms191188.aspx)
3. In SQL Server Management Studio of SQL Server 2005 or in SQL Server Query Analyzer of SQL Server 2000, run the following statements:
   
   Note This following code shows you how to load the Wbemdisp.dll file as an example.

   DROP PROC sp_Load_Dll
   GO
   CREATE PROC sp_Load_Dll
   AS
   declare @WmiServiceLocator int, @hr int
   exec @hr = sp_OACreate 'WbemScripting.SWbemLocator', @WmiServiceLocator output 
   if @hr = 0
    raiserror ( 'wbemdisp.dll loaded into sql address space by startup procedure sp_Load_Dll, Object Info: %d' , 10 , 1 , @WmiServiceLocator) --WITH LOG
   WHILE 1=1
    WAITFOR DELAY '01:00:00'
   GO
   EXEC sp_procoption 'sp_Load_Dll' , 'startup' , 'true' 
   GO
   

   The statements create a stored procedure. The statements also enable the stored procedure to run when the SQL Server service starts. This stored procedure performs the following operations:
   • Loads the Wbemdisp.dll file.
   • Keeps the Wbemdisp.dll file in memory because the statements do not exit.
   As long as the Wbemdisp.dll file is in memory, other statements do not load the Wbemdisp.dll file again. Therefore, SQL Server does not call the TlsAlloc function and the TlsFree function for the Wbemdisp.dll file.
   
   Note You must update the code to load the DLLs that you determined in step 1.
4. Add the statements to the sp_Load_Dll stored procedure in step 3. These statements load the DLLs that you determined in step 1.
5. Restart the SQL Server service.
6. Verify that SQL Server loads the Wbemdisp.dll file in memory. To do this, follow these steps:
   1. Open the SQL Server error log file.
   2. Search for the following message:
      wbemdisp.dll loaded into sql address space by startup procedure sp_Load_Dll
      Note This message indicates that SQL Server has loaded the Wbemdisp.dll file.
   3. At a command prompt, run the following command:

      tasklist /m

   4. In the result that you obtain, locate the Sqlservr.exe process, and then verify that the Wbemdisp.dll file is listed.
      
      Note If the Wbemdisp.dll file is listed, SQL Server has loaded the Wbemdisp.dll file.

Method 2

1. Use the sp_OACreate stored procedure to determine the DLLs that SQL Server loads into the SQL Server process. To do this, follow these steps:
   1. Open SQL Server Profiler, and then connect to the instance of SQL Server.
   2. In the Trace Properties dialog box, specify the name of the trace, specify the location where SQL Server Profiler saves the trace, and then click Run.
   3. After you run SQL Server Profiler for some time, analyze the trace to locate the occurrences of the sp_OACreate stored procedure.
   4. Notice the first parameter of each occurrence of the sp_OACreate stored procedure.
      
      Note The first parameter indicates the programmatic identifier (ProgID) of the OLE object or the class identifier (CLSID) of the OLE object.
   5. Determine the DLL that contains this OLE object.
   Note You can use SQL Server Profiler to monitor the instance of SQL Server when you start the SQL Server service.
2. Create an extended stored procedure. The extended stored procedure contains a function that loads the DLLs that you determined in step 1.
3. Add the extended stored procedure to SQL Server.
4. In SQL Server, use the sp_addextendedproc system stored procedure to register the function in the extended stored procedure.
5. Use the sp_procoption stored procedure to enable the extended stored procedure to automatically start when the SQL Server service starts.
6. Restart the SQL Server service.

When SQL Server executes the function in the extended stored procedure, SQL Server loads the DLLs by calling the LoadLibrary API.

This is not a problem in SQL Server. This is a problem in Windows Server 2003 and in Windows 2000. This problem does not occur in Windows Vista or in Windows Server 2008.

For more information about the TlsAlloc function, visit the following MSDN Web site: For more information about the TlsFree function, visit the following MSDN Web site: For more information about the sp_OACreate stored procedure, visit the following MSDN Web site: