Microsoft KB Archive/318878

= FIX: ALTER DATABASE SET READ_ONLY Statement May Lead to an Assertion If the Transaction Log is Full =

Article ID: 318878

Article Last Modified on 9/27/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q318878



BUG #: 356781 (SHILOH_BUGS)



SYMPTOMS
If the following conditions are true, SQL Server may encounter an assertion followed by an Access Violation (AV), which may then cause the server to either shut down or become unresponsive:


 * An ALTER DATABASE Set Read_Only statement has been issued on a database.
 * The transaction log of the database on which the ALTER DATABASE statement is being run is full.



CAUSE
When an ALTER DATABASE SET READ_ONLY statement is executed on a database, SQL Server must:
 * Shut down the database cleanly by issuing a CHECKPOINT statement.
 * Update the status in the Master..Sysdatabases system table for that database.
 * Bring the database back up with the new status.

However, if the transaction log is full, a CHECKPOINT record cannot be written to the database and it raises an error. SQL Server ignores this error and incorrectly continues to update the status in Master..Sysdatabases. As a result, when the database is brought back up again, the status is set to read-only but SQL Server continues to try to perform a recovery on a read-only database; therefore, an Assertion occurs.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

NOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this fix should have the following file attributes or later:   Date         Time      Version    Size      File name -

02/27/2002  1:42 PM   8.00.591   7273 KB   Sqlservr.exe NOTE: Due to file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.



MORE INFORMATION
The following messages, including the stack dumps, are in the SQL Server error log if the server encounters this problem.

On SQL Server 2000 Service Pack 1 (SP1): 2002-03-04 13:53:38.48 spid51   Error: 9002, Severity: 17, State: 2 2002-03-04 13:53:38.48 spid51   The log file for database 'dbalter' is full. Back up the transaction log for the database to free up some log space.. 2002-03-04 13:53:38.59 spid51   Starting up database 'dbalter'. 2002-03-04 13:53:38.73 spid51   Using 'dbghelp.dll' version '4.0.5' Dump thread - spid = 51, PSS = 0x196f7218, EC = 0x196f7528 Stack Dump being sent to d:\Microsoft SQL Server\MSSQL\log\SQL00289.dmp --- Short Stack Dump 00866627 Module(sqlservr+00466627) (CStackDump::GetContextAndDump+0000002D) 0086804E Module(sqlservr+0046804E) (stackTrace+0000021D) 0091D108 Module(sqlservr+0051D108) (utassert_fail+000002E2) 0084A6CB Module(sqlservr+0044A6CB) (RecoveryMgr::RedoPass+00000199) 005363FC Module(sqlservr+001363FC) (RecoveryMgr::RecoverDb+000002A5) 00539D66 Module(sqlservr+00139D66) (DBTABLE::Startup+00000705) 00538DA1 Module(sqlservr+00138DA1) (DBMgr::StartupDB+000003A1) 0081A63D Module(sqlservr+0041A63D) (DBMgr::ChangeDBState+000003A7) 005ED690 Module(sqlservr+001ED690) (CStmtDBExtend::ChangeStateOption+0000054E) 005EB719 Module(sqlservr+001EB719) (CStmtDBExtend::XretExecute+000000D9) 00413CEE Module(sqlservr+00013CEE) (CMsqlExecContext::ExecuteStmts+000002D2) 004133E9 Module(sqlservr+000133E9) (CMsqlExecContext::Execute+000001B6) 00412918 Module(sqlservr+00012918) (CSQLSource::Execute+00000331) 00448916 Module(sqlservr+00048916) (language_exec+000003E1) 00411D4C Module(sqlservr+00011D4C) (process_commands+000000E0) 41073379 Module(UMS+00003379) (UmsUserContext::SetWorkerWait+0000022E) 41073071 Module(UMS+00003071) (UmsSystemUserContext::UmsSystemUserContext+000003AF) 77C37E53 Module(MSVCRT+00027E53) ($I10_OUTPUT+0000051A) 77E802ED Module(kernel32+000202ED) (OpenConsoleW+000000B8) --- 2002-03-04 13:53:41.98 spid51   SQL Server Assertion: File: , line=2514 Failed Assertion = 'IS_OFF (DBT_RDONLY, m_dbt->dbt_stat)'. 2002-03-04 13:53:42.01 spid51   Error: 3624, Severity: 20, State: 1. 2002-03-04 13:53:42.01 spid51   Error: 3313, Severity: 21, State: 2 2002-03-04 13:53:42.01 spid51   Error while redoing logged operation in database 'dbalter'. Error at log record ID (0:0:0).. 2002-03-04 13:53:42.54 spid51   Error: 9004, Severity: 23, State: 7 2002-03-04 13:53:42.54 spid51   An error occurred while processing the log for database 'dbalter'..
 * BEGIN STACK DUMP:
 * 03/04/02 13:53:38 spid 51
 * Input Buffer 124 bytes -
 * alter database dbalter set read_only with rollback immediate
 * Input Buffer 124 bytes -
 * alter database dbalter set read_only with rollback immediate
 * Input Buffer 124 bytes -
 * alter database dbalter set read_only with rollback immediate
 * alter database dbalter set read_only with rollback immediate

Followed by an Access Violation (multiple times):

2002-03-04 13:53:47.00 spid4    Using 'dbghelp.dll' version '4.0.5' Stack Dump being sent to d:\Microsoft SQL Server\MSSQL\log\SQL00290.dmp 2002-03-04 13:53:47.02 spid4    Error: 0, Severity: 19, State: 0 2002-03-04 13:53:47.02 spid4    SqlDumpExceptionHandler: Process 4 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.. --- Short Stack Dump 0082CDA4 Module(sqlservr+0042CDA4) (DeadlockMonitor::ResolveDeadlock+00000024) 0082CC93 Module(sqlservr+0042CC93) (DeadlockMonitor::SearchAndResolve+00000142) 0054C24F Module(sqlservr+0014C24F) (lockMonitor+000002F4) 0054C0FB Module(sqlservr+0014C0FB) (lockMonitorThread+000000A3) 41073379 Module(UMS+00003379) (UmsUserContext::SetWorkerWait+0000022E) 41073071 Module(UMS+00003071) (UmsSystemUserContext::UmsSystemUserContext+000003AF) 77C37E53 Module(MSVCRT+00027E53) ($I10_OUTPUT+0000051A) 77E802ED Module(kernel32+000202ED) (OpenConsoleW+000000B8) If the server was started from a command prompt, then the following message displays before the server shuts down:
 * BEGIN STACK DUMP:
 * 03/04/02 13:53:47 spid 4
 * Exception Address = 0082CDA4 (DeadlockMonitor::ResolveDeadlock + 00000024 Line 0+00000000)
 * Exception Code   = c0000005 EXCEPTION_ACCESS_VIOLATION
 * Access Violation occurred reading address 00000000
 * Exception Address = 0082CDA4 (DeadlockMonitor::ResolveDeadlock + 00000024 Line 0+00000000)
 * Exception Code   = c0000005 EXCEPTION_ACCESS_VIOLATION
 * Access Violation occurred reading address 00000000

runtime error R6025 - pure virtual function call

Keywords: kbbug kbfix kbsqlserv2000presp3fix kbqfe kbsqlserv2000sp3fix kbhotfixserver KB318878

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.