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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
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
*******************************************************************************
*
* BEGIN STACK DUMP:
*   03/04/02 13:53:38 spid 51
*
* 
*
* Input Buffer 124 bytes -
*  alter database dbalter set read_only with rollback immediate  
*  
*******************************************************************************
-------------------------------------------------------------------------------
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: <recovery.c>, 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'..

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..
*******************************************************************************
*
* 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
*******************************************************************************
-------------------------------------------------------------------------------
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:
runtime error R6025 - pure virtual function call
Modification Type:MinorLast Reviewed:9/27/2005
Keywords:kbHotfixServer kbQFE kbSQLServ2000sp3fix kbbug kbfix kbSQLServ2000preSP3fix KB318878
©2004 Microsoft Corporation. All rights reserved.