Sunil
asked on
bypass recovering mode
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"
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"
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.
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.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
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.
ASKER
Okay, ive done the restore, how do i get it from emergency mode back to normal mode?
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.
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.
ASKER
yes i realised that right after i posted...
you've both been helpful.. how should i handle the points?
you've both been helpful.. how should i handle the points?
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.
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.
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.