SQL Server Q&A

Symptoms
Web applications running on IIS 7.5 and that rely on SQL Server Express user instancing will fail to run using the default IIS 7.5 security configuration on both Windows 7 Client and Windows Server 2008 R2. Developers will encounter problems developing web applications using Visual Studio 2005 + SQL Server Express 2005, Visual Studio 2008 + SQL Server Express 2008, or Visual Studio 2010 + SQL Server Express 2008 on both Windows 7 Client and Windows Server 2008 R2.
Developers will encounter similar problems attempting to develop web application projects (WAP) or websites hosted under IIS6/IIS7/IIS7.5 that rely on SQL Server Express user instances where the WAP project structure or website folder structure exists in a user’s Documents folder.  This issue exists for all versions of Visual Studio regardless of the underlying operating system version.  A web application that attempts to create a database or read/write to a database using SQL Server Express user instance mode can encounter any of the following errors:
An attempt to attach an auto-named database for file c:\Users\[YourUserAccountName]\Documents\Visual Studio 20XX\Projects\[YourSolutionName]\[YourProjectnName]\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
–or–
Failed to generate a user instance of SQL Server due to failure in retrieving the user’s local application data path.  Please make sure the user has a local user profile on the computer. The connection will be closed.
 
NOTE: A web application relies on SQL Server Express’ user instance mode if either of the following is true: The application relies on the default “LocalSQLServer” connection string defined in machine.config The application uses a connection string that contains the following attributes:
            “AttachDBFilename=|DataDirectory|xxxxxx.mdf;User Instance=true”
 
 
Resolution
For Windows Server 2008 R2 and Windows 7
The default security configuration for IIS 7.5 sets application pools to run as the “application pool identity”.  Running an application pool using this special identity was first introduced as an optional setting in Vista SP2 and Windows Server 2008 SP2.  On Windows 7 Client and Windows Server 2008 R2 this special identity is now the default.
 
Web applications built with Visual Studio 2005, Visual Studio 2008, or Visual Studio 2010 and that rely on user instancing with either SQL Server Express 2005 or SQL Server Express 2008 do not work with the new application pool identity.  These products were developed and tested against application pools running with the older NETWORK SERVICE account.
 
For Web Application Projects and Websites Located in a User’s Documents Folder Hosted in IIS
Web application projects (WAP) exist in a folder structure under a user’s “Documents\Visual Studio 20XX\Projects” folder.  Website projects exist in a folder structure under a user’s “Documents\Visual Studio 20XX\Websites” folder.  SQL Server Express user instances require file access rights to the parent folders of the website or WAP project’s directory structure.  Because the IIS service account (NETWORK SERVICE) by default does not have these rights within the Visual Studio project folder structure, WAP projects and websites located in a user’s Documents folder and that are hosted in IIS will not be able to open SQL Server Express user instanced databases for read access.
 
WAPs that were originally created within a user’s Documents folder, but were subsequently changed to use IIS for a web server via the web tab of the project’s properties will encounter this file permissions problem.  Websites hosted in IIS where the website directory structure is located within a user’s Documents folder will also encounter the file permissions problem.  This behavior occurs for WAP projects and websites hosted with any IIS versions that run as NETWORK SERVICE (IIS6, IIS7 and IIS 7.5) where the project structure exists within a user’s Documents folder.
 

Symptoms
In Microsoft SQL Server 2008, new functionalities are added to SQL Server Management Objects (SMO). An application that uses the SQL Server 2005 version of SMO cannot be compiled by using the SQL Server 2008 version of SMO. You must make some changes to the application, and then you must recompile the application. Then, you can use the new functionalities in the SQL Server 2008 version of SMO.
Resolution
This article describes what you must do to use the new functionalities of the SQL Server 2008 version of SMO in an application that uses the SQL Server 2005 version of SMO.

Symptoms
Microsoft SQL Server performs dynamic memory management based on the memory requirements of the current load and activities on the system. On a Windows Server 2003 or a Windows XP or later version system,SQL Server can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API. On a Microsoft Windows 2000 Server-based system, SQL Server periodically calculates the free physical memory on the system by using the native Windows API. Based on this information from the QueryMemoryResourceNotification Windows API or from the memory calculation, SQL Server responds to the current memory situation on a specific system. This provides the following benefits:The system does not page out the working set of the SQL Server process.The necessary database pages are available in memory to reduce physical I/O needs.For more information, see the “Dynamic memory management” topic and the “Server memory options” topic in SQL Server Books Online.
Resolution
In 64-bit editions of SQL Server,various problems may occur. For example, the following problems may occur:The performance of SQL Serverdecreases suddenly.A computer that is running SQL Serverstops responding for a short time.A time-out occurs for applications that connect to SQL Server.Problems occur when you run even simple commands or use applications on the system.If you installed SQL Server 2005 Service Pack 2 (SP2) or a later version, one of the following error messages is logged in the SQL Server error log when these problems occur:
Error message 1

date time spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 50%.
Error message 2

date time spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 315 seconds. Working set (KB): 410156, committed (KB): 2201296, memory utilization: 18%.
Error message 3

date time spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 646 seconds. Working set (KB): 901904, committed (KB): 2215752, memory utilization: 40%.Additionally, a known problem exists in SQL Server in which you may receive an instance of this error message. The error message is logged in the SQL Server Error log during the startup process of SQL Server. The error message might be a false warning. Therefore, it might not indicate that working set trim has actually occurred. For more information, visit the following Microsoft Web site:
http://blogs.msdn.com/psssql/archive/2009/05/12/sql-server-reports-working-set-trim-warning-message-during-early-startup-phase.aspx(http://blogs.msdn.com/psssql/archive/2009/05/12/sql-server-reports-working-set-trim-warning-message-during-early-startup-phase.aspx) The Microsoft SQL Server support team has also observed additional error messages or warnings that are recorded in the SQL Server Error log or in the Windows Event logs. These messages resemble the following:

2009-05-05 15:43:56.01 ServerResource Monitor (0×13c43) Worker 0×0412C1E8 appears to be non-yielding on Node 0. Memory freed: 34152 KB. Approx CPU Used: kernel 171 ms, user 140 ms, Interval: 125093.2009-05-05 12:54:52.18 Server* *******************************************************************************2009-05-05 12:54:52.18 Server* BEGIN STACK DUMP:2009-05-05 12:54:52.18 Server*05/05/08 12:54:52 spid 02009-05-05 12:54:52.18 Server* Non-yielding Resource Monitor2009-05-05 12:54:52.18 Server* *******************************************************************************2009-06-10 09:13:53.44 Server* *******************************************************************************2009-06-10 09:13:53.44 Server* BEGIN STACK DUMP:2009-06-10 09:13:53.44 Server*06/10/09 09:13:53 spid 02009-06-10 09:13:53.44 Server* Non-yielding IOCP Listener2009-06-10 09:13:53.44 Server* *******************************************************************************2009-06-10 09:13:55.85 spid2sLazyWriter: warning, no free buffers found.2009-07-15 13:27:45.35 spid4sAppDomain xx (SQLCLR.dbo[runtime].xx) is marked for unload due to memory pressure.2009-07-15 13:27:45.35 spid4sAppDomain xx (SQLCLR.dbo[runtime].xx) unloaded.2009-07-15 13:37:51.42 LogonError: 17189, Severity: 16, State: 1.2009-07-15 13:37:51.42 LogonSQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: xx.xxx.xx.xx]Event Type: ErrorEvent Source: SQLBrowserEvent ID: 8Description: The SQLBrowser service was unable to process a client request. These additional error messages or warnings may appear together with the “A significant part of sql server process memory has been paged out” error message. In several instances, these additional error messages or warnings did not appear together with the error message.
If you notice one of these error messages or warnings, consider the SQL Server working set paging that is discussed in this article as one possible cause but not as the only cause. These additional error messages or warnings could be logged because of various other conditions or causes.

Symptoms
Windows Firewall on Windows Server 2008 helps prevent unauthorized access to computer resources. However, if the firewall is configured incorrectly, attempts to connect to an instance ofSQL Server may be blocked.To access an instance of SQL Server that is behind the firewall, you must configure the firewall on the computer that is running SQL Server.
This article helps you open the firewall ports for SQL Server on Windows Server 2008.
To have us fix this problem for you, go to the “Fix it for me”section. If you prefer to fix this problem yourself, go to the “Let me fix it myself” section.

Resolution
To fix this problem automatically, click theFix this problemlink. ClickRunin the File Downloaddialog box, and then follow the steps in this wizard.

a.button {background: url(/library/images/support/KBGraphics/PUBLIC/cn/FixItButton.jpg) no-repeat 0 0;width: 139px;height: 56px;display:block;cursor:pointer;}a.button:hover {background-position: bottom right;}Fix this problem
Microsoft Fix it 50169

Note this wizard may be in English only; however, the automatic fix also works for other language versions of Windows.
Note if you are not on the computer that has the problem, save the Fix it solution to a flash drive or a CD and then run it on the computer that has the problem.
Next, go to the “Did this fix the problem?” section.

Symptoms
When Microsoft Office Excel 2007 tries to connect to an OLAP cube on a server that is running Microsoft SQL Server 2005, the connection fails.
Resolution
This problem occurs because SQL Server 2008 installs the Msolap100.dll, and this file checks for the SQL Server 2008 only value DBPROP_MSMD_SUBQUERIES. When the value is not present, the Msolap100.dll fails and prevents Excel 2007 from connecting to the server.

Symptoms
Consider the following scenario. You create a maintenance plan in an instance of Microsoft SQL Server 2008. You perform a full backup for the MSDB database.You restore the MSDB database to another instance of SQL Server 2008. You delete the maintenance plan in the second instance by using SQL Server Management Studio.In this scenario, the original maintenance plan in the first instance is deleted unexpectedly. When you refresh the maintenance plans folder in SQL Server Management Studio in the second instance, the maintenance plan is not deleted from the second instance.
Resolution
A maintenance plan is aSQL Server Integration Services (SSIS) package that is stored as XML in the MSDB database. A maintenance plan contains a list of connection strings that use the actual name of the server. Therefore, if you restore the MSDB database of a server to another server, the maintenance plan connection strings still point to the original server.