Recovery of database 'abcdef'...

Hi Everybody
We have an web-based application which connects to MSSQL Database 2000 + SP3
Few ASP jobs failed says "timedout error" while connecting to one of database 'abcdef'. The other similar jobs connects to other databases are running fine. The application is an Web-based Application. The Database Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured from this server to the other similar database server. Type of Replication is Merge. This DB Server is having around 24 Databases.
The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC & Re-index Jobs runs for this DB Server every weekend
The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory. It's a dedicated MSSQL Server
We thought it's a resouce constraint, so we rebooted the Server. The Job was initiated, it ran well without any problem
After the reboot, I checked the MSSQL ErrorLog, the following informational messgs. been registered.
******************************************************************************
2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0% complete (approximately 154 more seconds) (Phase 2 of 3)
2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3% complete (approximately 55 more seconds) (Phase 2 of 3)
2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11% complete (approximately 23 more seconds) (Phase 2 of 3)
2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30% complete (approximately 16 more seconds) (Phase 2 of 3)
2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'
2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74% complete (approximately 5 more seconds) (Phase 2 of 3)
2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97% complete (approximately 0 more seconds) (Phase 2 of 3)
2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 2 of 3)
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 3 of 3)
2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is 100% complete (approximately 0 more seconds) (Phase 3 of 3)
2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database 'abcdef' (18)
2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database 'abcdef'(18
******************************************************************************
The above mesgs didn't come for other databases. Can anybody tell why it displayed like this for database 'abcdef' and what it means
Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 2 of 3).
"
Is that database is in Corruption stage..
tks in advance
vasumI've seen this when a user killed the SQL Server service after attempting to
kill a transaction and giving up on the rollback operation.
When the service comes back up, it needs to do a lot of repair.
I suppose this could also happen if you trip on the power card, or something
like that.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85584AE2-2D6B-4976-B32D-1ACA7B4F1915@.microsoft.com...
> Hi Everybody,
> We have an web-based application which connects to MSSQL Database 2000 +
> SP3.
> Few ASP jobs failed says "timedout error" while connecting to one of
> database 'abcdef'. The other similar jobs connects to other databases are
> running fine. The application is an Web-based Application. The Database
> Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured
> from this server to the other similar database server. Type of Replication
> is Merge. This DB Server is having around 24 Databases.
> The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC &
> Re-index Jobs runs for this DB Server every weekend.
> The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory.
> It's a dedicated MSSQL Server.
> We thought it's a resouce constraint, so we rebooted the Server. The Job
> was initiated, it ran well without any problem.
> After the reboot, I checked the MSSQL ErrorLog, the following
> informational messgs. been registered.
> *******************************************************************************
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
> complete (approximately 154 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3%
> complete (approximately 55 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30%
> complete (approximately 16 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760'
> to execute extended stored procedure 'sp_MSgetversion'.
> 2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74%
> complete (approximately 5 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
> 100% complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database
> 'abcdef' (18).
> 2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database
> 'abcdef'(18)
> *******************************************************************************
> The above mesgs didn't come for other databases. Can anybody tell why it
> displayed like this for database 'abcdef' and what it means,
> "
> Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more
> seconds) (Phase 2 of 3).
> ".
> Is that database is in Corruption stage...
> tks in advance,
> vasum|||Aaron is correct. These entries are from your database going through
transaction recovery. SQL has to walk through the transaction log from the
last checkpoint and roll transactions either forward or back depending on
their commit status. Some databases have very recent checkpoints or no long
running transactions so they recover very quickly. SQL will give progress
reports on recovery when it thinks the recovery will take a while. The
long-running transaction probably was the original cause of your timeout
errors. You can use DBCC OPENTRAN to determine if there is a long-running
transaction in a particular database. I suspect this is related to your
merge replication, but that is just a guess.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85584AE2-2D6B-4976-B32D-1ACA7B4F1915@.microsoft.com...
> Hi Everybody,
> We have an web-based application which connects to MSSQL Database 2000 +
SP3.
> Few ASP jobs failed says "timedout error" while connecting to one of
database 'abcdef'. The other similar jobs connects to other databases are
running fine. The application is an Web-based Application. The Database
Server is MSSQL 2000+SP3+Standard Edition. Replication has been configured
from this server to the other similar database server. Type of Replication
is Merge. This DB Server is having around 24 Databases.
> The database size of 'abcdef' is 1200 MB (Data 850 MB, Log 400 MB). DBCC &
Re-index Jobs runs for this DB Server every weekend.
> The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory.
It's a dedicated MSSQL Server.
> We thought it's a resouce constraint, so we rebooted the Server. The Job
was initiated, it ran well without any problem.
> After the reboot, I checked the MSSQL ErrorLog, the following
informational messgs. been registered.
>
****************************************************************************
***
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
complete (approximately 154 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3%
complete (approximately 55 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11%
complete (approximately 23 more seconds) (Phase 2 of 3).
> 2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30%
complete (approximately 16 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:01.14 spid55 Using 'xpstar.dll' version '2000.80.760'
to execute extended stored procedure 'sp_MSgetversion'.
> 2004-04-28 15:29:01.45 spid21 Recovery of database 'abcdef' (18) is 74%
complete (approximately 5 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97%
complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
100% complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 1 transactions rolled back in database
'abcdef' (18).
> 2004-04-28 15:29:06.80 spid21 Recovery is checkpointing database
'abcdef'(18)
>
****************************************************************************
***
> The above mesgs didn't come for other databases. Can anybody tell why it
displayed like this for database 'abcdef' and what it means,
> "
> Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more
seconds) (Phase 2 of 3).
> ".
> Is that database is in Corruption stage...
> tks in advance,
> vasum|||Thanks for the reply
In this case, nobody has killed the SQL Server Service or removing the trip on the Power Card. Since it a very critica
server, only few people have access to the Server
Apart from these problems any other problem which makes these mesg to display in the SQL Server Errorlog
tks in advance
vasum|||If the transaction recovery not happened, will that database would have gone to corrupt state. what other implications expected if transaction recovery not happened
tks in advance
vasum|||vasum
Did you mean that your transaction log file gets corrupted?
Perform RESTORE DATABASE with RECOVERY option
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:A69F9633-7198-4400-97DB-CD0F252E0F34@.microsoft.com...
> If the transaction recovery not happened, will that database would have
gone to corrupt state. what other implications expected if transaction
recovery not happened.
> tks in advance,
> vasum|||No. I am saying that the 'Transaction log corrupted' in that database
I am just asking what other problems are expected if the recovery of database not happened and which situtation mak
the database server to display this kind of informational mesg
vasum|||Well, if your Transaction log corrupted during the recovery process you will
get an error and the database will be displayed in suspect mode. So you will
need to perfom restore database with recovery option or detach and the
reatach your database.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:85D59D9B-FB3E-4D0B-8C4D-A83EDD6A527C@.microsoft.com...
> No. I am saying that the 'Transaction log corrupted' in that database.
> I am just asking what other problems are expected if the recovery of
database not happened and which situtation make
> the database server to display this kind of informational mesg.
> vasum|||Recovery is about transactional consistancy. Recovery is NOT about fixing
corruption. SQL runs recovery so that the transactional engine can bring
the database to a transactionally consistant state. Again, every database
is 'recovered' on SQL system startup. Some take very little time, some can
take a long time. I once had an 8 hour transaction crash the server (Older
version) and it took 8 hours to roll it back. Expensive lesson.
SQL will not allow you to access the database until the recovery process is
completed. If an underlying data file is corrupted, you will need to
restore according to your recovery plan. Exactly how you do this and how
much data is lost depends on your choice of recovery model and your backup
procedures.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:A69F9633-7198-4400-97DB-CD0F252E0F34@.microsoft.com...
> If the transaction recovery not happened, will that database would have
gone to corrupt state. what other implications expected if transaction
recovery not happened.
> tks in advance,
> vasum|||So you mean to say it's normal kind of informational mesg. We can ignore this
Why I raised this in forum is, out of 24 databases, only for this database this kind of informational mesgs. displayed and few jobs which connects to this database were also failed saying "timedout error" but at the time users able to connect to this database through isqlw and do queries...I think this could be also the reason, Merge Replication is happening in that database as I said earlier. Before server reboot, we didn't stop the merge agents and we went for reboot. At the time some transaction would have been published to the subscriber. May be this could also made this type of informational mesgs. to display in the errorlog. But same thing should happen to other databases also, right
2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0% complete (approximately 154 more seconds) (Phase 2 of 3)
- - -
- -
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more seconds) (Phase 3 of 3)
2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is 100% complete (approximately 0 more seconds) (Phase 3 of 3)
Pls. correct me if I am wrong...
vasum|||It depends.
Recovery is related to the number and size of open transactions and
uncommitted data in the database. If the database was checkpointed
immediately before the shutdown and didn't have any long-running
transactions, then the recovery will be short. SQL can estimate the amount
of work to be done and will update the event log as necessary. Read the
following topic in BOL for the details on how this process really works.
SQL Server Architecture | Relational Database Engine Architecture |
Transactions Architecture | Transaction Recovery.
For some reason, Transaction Recovery isn't in the index so you have to find
this through Contents.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:3AD49B0F-7185-4D3A-B78B-B54CAB861999@.microsoft.com...
> So you mean to say it's normal kind of informational mesg. We can ignore
this.
> Why I raised this in forum is, out of 24 databases, only for this database
this kind of informational mesgs. displayed and few jobs which connects to
this database were also failed saying "timedout error" but at the time users
able to connect to this database through isqlw and do queries...I think
this could be also the reason, Merge Replication is happening in that
database as I said earlier. Before server reboot, we didn't stop the merge
agents and we went for reboot. At the time some transaction would have been
published to the subscriber. May be this could also made this type of
informational mesgs. to display in the errorlog. But same thing should
happen to other databases also, right
> "
> 2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0%
complete (approximately 154 more seconds) (Phase 2 of 3).
> - - -
> - - -
> 2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
> 2004-04-28 15:29:06.79 spid21 Recovery of database 'abcdef' (18) is
100% complete (approximately 0 more seconds) (Phase 3 of 3).
> "
> Pls. correct me if I am wrong...
> vasum