Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

bypass recovering mode

Posted on 2000-02-28
8
Medium Priority
?
1,421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 300 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 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