SQL Server Q&A

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by the hotfix packageThe prerequisites for installing the hotfix packageWhether you must restart the computer after you install the hotfix packageWhether the hotfix package is replaced by any other hotfix packageWhether you must make any registry changesThe files that are contained in the hotfix package
Resolution
In Microsoft SQL Server 2005, when you call a method of a component object model (COM) object by using the sp_OAMethod stored procedure or the sp_OAGetProperty stored procedure, a memory leak occurs. For the sp_OAMethod stored procedure, this problem occurs if the method returns the result in the output parameter of the method. For the sp_OAGetProperty stored procedure, this problem occurs if the stored procedure returns a property value in the OUTPUT parameter.
Additionally, if the memory leaks exhausts the non-buffer pool memory region, the following error message is logged in the SQL Server error log file:

2008-05-01 08:35:07.80 spid535 Downgrading backup log buffers from 1024K to 64K
2008-05-06 09:35:22.55 spid77 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576
2008-05-06 10:48:04.85 spid593 Error: 701, Severity: 17, State: 123.
2008-05-06 10:48:04.85 spid593 There is insufficient system memory to run this query.

Symptoms
This article describes the following about this hotfix release: The issues that are fixed by this hotfix package.The prerequisites for installing the hotfix package.Whether you must restart your computer after you install the hotfix package.The files that are contained in the hotfix package.
Resolution
When you open a database in Microsoft SQL Server 2000, you notice that a user database is marked as suspect. Also, an error message that is similar to the following is logged in the SQL Server 2000 error log:

<Date> <Time> spid9 Recovery of database ‘UserDatabaseName’ (5) is 2% complete (approximately 185 more seconds) (Phase 3 of 3).
<Date> <Time> spid9 Using ‘dbghelp.dll’ version ‘4.0.5′ *Dump thread – spid = 9, PSS = 0×80178098, EC = 0×801783c0
<Date> <Time> spid9 The exception information has been submitted to Watson error reporting.
<Date> <Time> spid9 Stack Signature for the dump is 0×986D5509
<Date> <Time> spid9 SQL Server Assertion: File: <page.cpp>, line=3430 Failed Assertion = ‘m_freeCnt + emptySlots * sizeof (Slot) >= spaceNeed’.
<Date> <Time> spid9 Location: page.cpp:3430 Expression: m_freeCnt + emptySlots * sizeof (Slot) >= spaceNeed

Symptoms
When you try to start an instance of SQL Server after you restore the model system database by using the WITH NORECOVERY option, the instance of SQL Server may not start successfully. Additionally, you may notice the following error messages in the SQL Server error log files:

2003-04-18 09:37:38.85 spid5 Starting up database ‘model’.
2003-04-18 09:37:39.24 spid5 Bypassing recovery for database ‘model’ because it is marked IN LOAD.
2003-04-18 09:37:39.65 spid5 Database ‘model’ cannot be opened. It is in the middle of a restore.
Resolution
When you restore the model system database by using the WITH NORECOVERY option, the restore leaves the model database in an unrecovered state. Therefore, the model database can no longer be used as expected.

Symptoms
Consider the following scenario. In Microsoft SQL Server 2005 or in Microsoft SQL Server 2008, you have a database that has query notification enabled. You back up the database, and then you try to restore the database from this backup. However, the restore operation may fail during the recovery phase.
This issue may also occur during the recovery phase of a regular startup of the database.

When this problem occurs, a mini-dump file is generated in the SQL Server log folder. Additionally, the following error message is logged in the SQL Servererror log file:

<Date><Time> spid61* *******************************************************************************
<Date><Time> spid61*
<Date><Time> spid61* BEGIN STACK DUMP:
<Date><Time> spid61*08/13/09 04:15:19 spid 61
<Date><Time> spid61*
<Date><Time> spid61* Server was configured to produce dump on the following event – ex_raise2: Exception raised, major=97, minor=68, state=1, severity=16
<Date><Time> spid61*2009-08-13 04:15:19.57 spid61* Input Buffer 510 bytes -
<Date><Time> spid61*<Restore database statement>
<Date><Time> spid61Error: 9768, Severity: 16, State: 1.
<Date><Time> spid61A database user associated with the secure conversation was dropped before credentials had been exchanged with the far endpoint. Avoid using DROP USER while conversations are being created.
<Date><Time> spid61Failed to check for pending query notifications in database “36″ because of the following error when opening the database: ‘A database user associated with the secure conversation was dropped before credentials had been exchanged with the far endpoint. Avoid using DROP USER while conversations are being created. The query notification subscriptions cleanup operation failed. See previous errors for details.’.
<Date><Time> spid61Error: 9001, Severity: 16, State: 5.
<Date><Time> spid61The log for database <Database Name> is not available. Check the event log for related error messages. Resolve any errors and restart the database.
<Date><Time> spid61Error: 3314, Severity: 21, State: 4.
<Date><Time> spid61During undoing of a logged operation in database <Database Name>, an error occurred at log record ID (1835:7401:137). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Resolution
The issue occurs because query notifications are sent to a user who has been dropped from the database before the database has been backed up. When you restore the database, SQL Server tries to redo all the query notification transactions as part of database startup. However, processing of query notifications fails because the user does not exist, and an error 9768 is raised. This error is not handled appropriately by SQL Server, and the severity of this error is increased. This causes recovery transaction to fail, and then the restore operation fails.

Symptoms
In Microsoft SQL Server 2008, an access violation occurs intermittently during message processing for service broker,and the session may be disconnected. When this problem occurs, a mini-dump file is generated in the SQL Server log folder, and the following error message is logged in the SQL Server log file:

<Date><Time> spid18s***Stack Dump being sent to <drive>:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\SQLDump0005.txt
<Date><Time> spid18sSqlDumpExceptionHandler: Process 18 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
<Date><Time> spid18s* *******************************************************************************
<Date><Time> spid18s*
<Date><Time> spid18s* BEGIN STACK DUMP:
<Date><Time> spid18s*<Date><Time> spid <spid>
<Date><Time> spid18s*
<Date><Time> spid18s*
<Date><Time> spid18s*Exception Address = 0000000002164AC2 Module(sqlservr+0000000001164AC2)
<Date><Time> spid18s*Exception Code= c0000005 EXCEPTION_ACCESS_VIOLATION
<Date><Time> spid18s*Access Violation occurred reading address 0000000000000000
<Date><Time> spid18sStack Signature for the dump is 0×00000001CCC51BCC
<Date><Time> spid18sExternal dump process return code 0×20000001.External dump process returned no errors.
<Date><Time> spid15sThe Service Broker protocol transport has stopped listening for connections.
<Date><Time> spid15sService Broker manager has shut down.
Resolution
The fix for this issue was first released in Cumulative Update5 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
975977?(http://support.microsoft.com/kb/975977/LN/) Cumulative update package 5 for SQL Server 2008 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
970365?(http://support.microsoft.com/kb/970365/LN/) The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was releasedMicrosoft SQL Server 2008hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.