Solved

bypass recovering mode

Posted on 2000-02-28
8
1,287 Views
Last Modified: 2010-08-05
Hello..
I have a database that is in "recovering" mode.. I dont think it will ever stop recovering so i'll just restore from backup..

How can I take the database out of recovering mode into a state so i can do a restore ?

Please provide the exact commands i do.. i dont have books online..

The name of the database is "ibf"
0
Comment
Question by:Sunil
  • 4
  • 2
  • 2
8 Comments
 
LVL 1

Expert Comment

by:vujos
ID: 2564756
Hi,
run following command :

UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = @dbname

@dbname is yor database name.

Restart you SQL server after above command execution.

Good luck.
0
 
LVL 1

Expert Comment

by:vujos
ID: 2564790
Hi,

one more thing to do say. Following article is good reading on this

ftp://ftp.microsoft.com/bussys/sql/kb/q165/9/18.txt

Luck.
0
 

Accepted Solution

by:
LeeOgley earned 100 total points
ID: 2564813
Sunil,

Following from vujos please find below the two main articles regarding recovering :-

---------------------------------------

INF: How to Troubleshoot Long Recovery on Databases
Last reviewed: September 26, 1997
Article ID: Q170115  
The information in this article applies to:
Microsoft SQL Server, versions 6.0 and 6.5


SUMMARY
Database recovery during SQL Server startup or loading transaction logs may take a long period of time, during which the state of recovery is not easy to determine. This article describes steps to troubleshoot this scenario.



MORE INFORMATION
When SQL Server recovers a database, transactions may roll forward or back. You can reduce the amount of time required to roll forward transactions by performing a normal shutdown. However, you cannot control the amount of time required to roll back transactions. So, if a long running query is aborted by shutting down SQL Server, the rollback of the transaction may cause the recovery of the database to exceed the recovery interval setting time.

During a normal shutdown, SQL Server checkpoints all databases where the recovery interval is exceeded. SQL Server is normally shut down by the following means:

Stopping the service with SQL Service Manager, SQL Enterprise Manager, or Control Panel Services.
Shutting down Windows NT.
Running the NET STOP MSSQLSERVER command.
Running the SHUTDOWN query.

SQL Server is shut down without checkpointing databases by the following means:
Restarting the computer.
Quitting SQL Server with CTRL+C when it was started from the command line.
Killing the SQL Server process by using a KILL command or the Task Manager.
Attaching to SQL Server with a debugger and closing the debugger.
SQL Server encountering an exception that it does not handle (normally Dr. Watson would report this situation).
Running the SHUTDOWN WITH NOWAIT query .

Additionally, loading transaction logs after loading a database dump may also cause long recovery.
In a long recovery situation, there are three possibilities of transactions that need to roll forward or back:

Many small transactions
One large transaction
A combination of either of the above

In these cases, it is difficult to determine the amount of time that recovery will take on a database. However, if you perform the following steps, you can see that SQL Server is progressing in the database recovery:

Determine the spid that is running recovery for the database by examining the errorlog for a message such as the following (note that in the example below, the spid is 10):

      spid10   Recovering database 'pubs'


From this point, there are three simple ways to check that database recovery is still occurring:


    - Look for the recovery spid number from step one in the sysprocesses
      table or sp_who output. If the spid is not in the output, the
      database recovery has not completed.


    - Look at the status column of the sysdatabases system table to see if
      the "Database not recovered yet" status bit (64) is still enabled and
      that the "Database is suspect" status bit (256) is disabled. If so,
      the database recovery has not completed.


    - Examine the errorlog for a transaction summary from the recovery
      spid. If it is not there, the database recovery has not completed.
      The following is an example of the transaction summary:

         spid10   1 transactions rolled forward in dbid 4.


If database recovery has not completed yet, examine the errorlog for the checkpoint message from the recovery spid. This message means that the database has entered the active part of recovery, where transactions are rolled forward and back. The following is an example:

      spid10   Recovery dbid 4 ckpt (1128,31) oldest tran=(1128,0)


Normally, during the active part of recovery, the hard disk drive that contains the database's transaction log will see heavy activity. You can see this activity by either looking at the drive activity light on the computer or by using Windows NT Performance Monitor and looking at the "LogicalDisk: % Disk Time" counter, if the disk counters were already enabled with the DISKPERF -Y command.

Enable the trace flags 3412, which reports when each transaction is rolled forward or back, and examine the errorlog for progress. However, you will not see any progress if SQL Server is rolling a large transaction forward or back. Additionally, this trace flag duplicates the sp_configure setting Recovery flags. The following is an example of the output:

      spid10   Roll forward transaction 'user_transaction' in dbid 4.

If recovery still seems to be progressing, or if it cannot be determined, you have the following three options:
Allow recovery to continue until it eventually completes.
Stop recovery and load the database from a backup.
Stop recovery and truncate the transaction log. For additional information, please see the following article in the Microsoft Knowledge Base:


      ARTICLE-ID: Q165918
      TITLE     : INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH
                  NO_LOG

----------------------------------------
INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH NO_LOG
Last reviewed: April 22, 1997
Article ID: Q165918  
The information in this article applies to:
Microsoft SQL Server, versions 4.2, 6.0, and 6.5


SUMMARY
In infrequent situations, a database may be marked SUSPECT due to recovery failure at startup time. Normally, this prevents anybody from accessing the data. However, it is possible to manually set the status of a SUSPECT database to "bypass mode" (also called "emergency mode") and SELECT or use the Bulk Copy Program (BCP) to copy out the data. While you cannot do any regular data modifications in bypass mode, it is possible to run DUMP TRANSACTION WITH NO_LOG. Note that doing this operation in bypass mode is unsupported and is a potentially dangerous operation.

For similar reasons, if startup recovery is taking a long time, you should not abort it, set the database in bypass mode, and then do DUMP TRANSACTION WITH NO_LOG.



MORE INFORMATION
All actions taken by DUMP TRANSACTION are usually logged, so it is recoverable and abortable. However, log space is consumed by the DUMP command itself. If the transaction log is so full that insufficient space exists to do a logged DUMP TRANSACTION, the WITH NO_LOG option can truncate the transaction log with no logging.

DUMP TRANSACTION WITH NO_LOG is relatively safe under normal conditions. The server takes measures to ensure that recovery will succeed even if the server fails during this operation.

Under rare circumstances automatic recovery (also called startup recovery) may fail, marking a database SUSPECT. Recovery fails for a specific reason. It is very important to note the errorlog message that initially caused recovery to fail, because it may help to diagnose the cause.

"Recovery" is the process of making the database consistent by redoing or undoing all transactions that were either started after or uncommitted at the time of the last checkpoint. This process relies on the write-ahead nature of the transaction log (all modified pages are written to the log before being written to the database). Recovery consists of reading each log record, comparing its timestamp to the timestamp of the corresponding database page, and either undoing the change (in the case of an uncommitted transaction) or redoing the change (in the case of a committed transaction).

After noting the errorlog message that is causing recovery to fail, try setting the database status back to NORMAL, and restart SQL Server to see if recovery succeeds the second time. You can change the database status by means of the sp_resetstatus stored procedure. This is a supplemental stored procedure you can install from the Instsupl.sql script in the Mssql\Install directory. For more information, see "Resetting the Suspect Status" in the online documentation.

If recovery still fails, note the error message and contact your primary support provider. You should also verify the availability of your last good database backup, because it may be needed. However much of the data in your database is often still available, albeit transactionally (and physically) inconsistent. You can access this data by setting the database status to bypass, or emergency mode. This is done by setting sysdatabases.status to -32768 for the database, after turning "allow updates" on. For example, use the following command:


   UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'

After doing this, you can enter the database and SELECT the data or use BCP to get it out. You may encounter errors while doing this, but in most cases much of the data can be retrieved.  


Both Articles :-
) 1998 Microsoft Corporation. All rights reserved. Terms of Use.


Hope this explains further.

Lee.
0
 

Expert Comment

by:LeeOgley
ID: 2564814
Sunil,

Following from vujos please find below the two main articles regarding recovering :-

---------------------------------------

INF: How to Troubleshoot Long Recovery on Databases
Last reviewed: September 26, 1997
Article ID: Q170115  
The information in this article applies to:
Microsoft SQL Server, versions 6.0 and 6.5


SUMMARY
Database recovery during SQL Server startup or loading transaction logs may take a long period of time, during which the state of recovery is not easy to determine. This article describes steps to troubleshoot this scenario.



MORE INFORMATION
When SQL Server recovers a database, transactions may roll forward or back. You can reduce the amount of time required to roll forward transactions by performing a normal shutdown. However, you cannot control the amount of time required to roll back transactions. So, if a long running query is aborted by shutting down SQL Server, the rollback of the transaction may cause the recovery of the database to exceed the recovery interval setting time.

During a normal shutdown, SQL Server checkpoints all databases where the recovery interval is exceeded. SQL Server is normally shut down by the following means:

Stopping the service with SQL Service Manager, SQL Enterprise Manager, or Control Panel Services.
Shutting down Windows NT.
Running the NET STOP MSSQLSERVER command.
Running the SHUTDOWN query.

SQL Server is shut down without checkpointing databases by the following means:
Restarting the computer.
Quitting SQL Server with CTRL+C when it was started from the command line.
Killing the SQL Server process by using a KILL command or the Task Manager.
Attaching to SQL Server with a debugger and closing the debugger.
SQL Server encountering an exception that it does not handle (normally Dr. Watson would report this situation).
Running the SHUTDOWN WITH NOWAIT query .

Additionally, loading transaction logs after loading a database dump may also cause long recovery.
In a long recovery situation, there are three possibilities of transactions that need to roll forward or back:

Many small transactions
One large transaction
A combination of either of the above

In these cases, it is difficult to determine the amount of time that recovery will take on a database. However, if you perform the following steps, you can see that SQL Server is progressing in the database recovery:

Determine the spid that is running recovery for the database by examining the errorlog for a message such as the following (note that in the example below, the spid is 10):

      spid10   Recovering database 'pubs'


From this point, there are three simple ways to check that database recovery is still occurring:


    - Look for the recovery spid number from step one in the sysprocesses
      table or sp_who output. If the spid is not in the output, the
      database recovery has not completed.


    - Look at the status column of the sysdatabases system table to see if
      the "Database not recovered yet" status bit (64) is still enabled and
      that the "Database is suspect" status bit (256) is disabled. If so,
      the database recovery has not completed.


    - Examine the errorlog for a transaction summary from the recovery
      spid. If it is not there, the database recovery has not completed.
      The following is an example of the transaction summary:

         spid10   1 transactions rolled forward in dbid 4.


If database recovery has not completed yet, examine the errorlog for the checkpoint message from the recovery spid. This message means that the database has entered the active part of recovery, where transactions are rolled forward and back. The following is an example:

      spid10   Recovery dbid 4 ckpt (1128,31) oldest tran=(1128,0)


Normally, during the active part of recovery, the hard disk drive that contains the database's transaction log will see heavy activity. You can see this activity by either looking at the drive activity light on the computer or by using Windows NT Performance Monitor and looking at the "LogicalDisk: % Disk Time" counter, if the disk counters were already enabled with the DISKPERF -Y command.

Enable the trace flags 3412, which reports when each transaction is rolled forward or back, and examine the errorlog for progress. However, you will not see any progress if SQL Server is rolling a large transaction forward or back. Additionally, this trace flag duplicates the sp_configure setting Recovery flags. The following is an example of the output:

      spid10   Roll forward transaction 'user_transaction' in dbid 4.

If recovery still seems to be progressing, or if it cannot be determined, you have the following three options:
Allow recovery to continue until it eventually completes.
Stop recovery and load the database from a backup.
Stop recovery and truncate the transaction log. For additional information, please see the following article in the Microsoft Knowledge Base:


      ARTICLE-ID: Q165918
      TITLE     : INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH
                  NO_LOG

----------------------------------------
INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH NO_LOG
Last reviewed: April 22, 1997
Article ID: Q165918  
The information in this article applies to:
Microsoft SQL Server, versions 4.2, 6.0, and 6.5


SUMMARY
In infrequent situations, a database may be marked SUSPECT due to recovery failure at startup time. Normally, this prevents anybody from accessing the data. However, it is possible to manually set the status of a SUSPECT database to "bypass mode" (also called "emergency mode") and SELECT or use the Bulk Copy Program (BCP) to copy out the data. While you cannot do any regular data modifications in bypass mode, it is possible to run DUMP TRANSACTION WITH NO_LOG. Note that doing this operation in bypass mode is unsupported and is a potentially dangerous operation.

For similar reasons, if startup recovery is taking a long time, you should not abort it, set the database in bypass mode, and then do DUMP TRANSACTION WITH NO_LOG.



MORE INFORMATION
All actions taken by DUMP TRANSACTION are usually logged, so it is recoverable and abortable. However, log space is consumed by the DUMP command itself. If the transaction log is so full that insufficient space exists to do a logged DUMP TRANSACTION, the WITH NO_LOG option can truncate the transaction log with no logging.

DUMP TRANSACTION WITH NO_LOG is relatively safe under normal conditions. The server takes measures to ensure that recovery will succeed even if the server fails during this operation.

Under rare circumstances automatic recovery (also called startup recovery) may fail, marking a database SUSPECT. Recovery fails for a specific reason. It is very important to note the errorlog message that initially caused recovery to fail, because it may help to diagnose the cause.

"Recovery" is the process of making the database consistent by redoing or undoing all transactions that were either started after or uncommitted at the time of the last checkpoint. This process relies on the write-ahead nature of the transaction log (all modified pages are written to the log before being written to the database). Recovery consists of reading each log record, comparing its timestamp to the timestamp of the corresponding database page, and either undoing the change (in the case of an uncommitted transaction) or redoing the change (in the case of a committed transaction).

After noting the errorlog message that is causing recovery to fail, try setting the database status back to NORMAL, and restart SQL Server to see if recovery succeeds the second time. You can change the database status by means of the sp_resetstatus stored procedure. This is a supplemental stored procedure you can install from the Instsupl.sql script in the Mssql\Install directory. For more information, see "Resetting the Suspect Status" in the online documentation.

If recovery still fails, note the error message and contact your primary support provider. You should also verify the availability of your last good database backup, because it may be needed. However much of the data in your database is often still available, albeit transactionally (and physically) inconsistent. You can access this data by setting the database status to bypass, or emergency mode. This is done by setting sysdatabases.status to -32768 for the database, after turning "allow updates" on. For example, use the following command:


   UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'

After doing this, you can enter the database and SELECT the data or use BCP to get it out. You may encounter errors while doing this, but in most cases much of the data can be retrieved.  


Both Articles :-
) 1998 Microsoft Corporation. All rights reserved. Terms of Use.


Hope this explains further.

Lee.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Sunil
ID: 2565054
Okay, ive done the restore, how do i get it from emergency mode back to normal mode?
0
 

Expert Comment

by:LeeOgley
ID: 2565092
Sunil,

Not sure, you could try looking in sysdatabase, and see what status other database tables have, and set your database to the same.  

I would'nt use any of the system tables as they might jave different status to user database.  

If no other databases exist, perhaps create a small test database and see what status it is given.  It might also be adviseable to set this new temp table to emergency mode and then back to see if it works before you attempt on proper table.

Hope this helps

Lee.
0
 

Author Comment

by:Sunil
ID: 2565099
yes i realised that right after i posted...

you've both been helpful.. how should i handle the points?
0
 

Expert Comment

by:LeeOgley
ID: 2565114
Sunil

Just found the following article, may help:

----------------------------------------
Contains one row for each database on SQL Server. When SQL Server is initially installed, sysdatabases contains entries for the master database, the model database, and the tempdb database. The status column values are additive when more than one option is set or database condition applies.

   
   
Column Datatype Description
   
name varchar(30) Name of the database.
dbid smallint Database ID.
suid smallint Server user ID of database creator.
mode smallint Used internally for locking a database while it is being created.
status smallint Status bits, some of which can be set by the user with the sp_dboption system stored procedure (READ ONLY, DBO USE ONLY, SINGLE USER, and so on):
2    Database is in transition
4    select into/bulkcopy; set with
    sp_dboption
8    trunc. log on chkpt; set with
    sp_dboption
16    no chkpt on recovery; set
    with sp_dboption
32    Crashed while the database
    was being loaded; instructs
    recovery not to proceed
64    Database not recovered yet
128    Database is in recovery
256    Database is suspect; cannot be
    opened or used in its present
    state
1024    read only; set with
    sp_dboption
2048    dbo use only; set with
    sp_dboption
4096    single user; set with
    sp_dboption
8192    Database being checkpointed
16384    ANSI null default; set with
    sp_dboption
32768    Emergency mode
version smallint Internal version number of the SQL Server code with which the database was created.
logptr int Pointer to the transaction log.
crdate datetime Creation date.
dumptrdate datetime Date of the last DUMP TRANSACTION.
category  int Used for publication and subscription databases.


Index
sysdatabases clustered, unique on name

ncsysdatabases nonclustered, unique on dbid


It doesn't give a 'normal mode' status code but you perhaps set your database to 'select into/bulkcopy' manual via this table then use the proper sp_dboption to remove the operation, and see if it resets the status back to a 'normal' state

Hope this helps

Lee.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now