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 databas
e 'abcdef'. The other similar jobs connects to other databases are running
fine. The application is an Web-based Application. The Database Server is MS
SQL 2000+SP3+Standard Editi
on. Replication has been configured from this server to the other similar da
tabase 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 & R
e-index Jobs runs for this DB Server every weekend.
The OS for this DB server is Windows advanced Server + 4CPU + 2 GB Memory. I
t'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% co
mplete (approximately 154 more seconds) (Phase 2 of 3).
2004-04-28 15:28:58.02 spid21 Recovery of database 'abcdef' (18) is 3% co
mplete (approximately 55 more seconds) (Phase 2 of 3).
2004-04-28 15:28:58.24 spid21 Recovery of database 'abcdef'(18) is 11% co
mplete (approximately 23 more seconds) (Phase 2 of 3).
2004-04-28 15:28:59.90 spid21 Recovery of database 'abcdef' (18) is 30% c
omplete (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% c
omplete (approximately 5 more seconds) (Phase 2 of 3).
2004-04-28 15:29:06.01 spid21 Recovery of database 'abcdef' (18) is 97% c
omplete (approximately 0 more seconds) (Phase 2 of 3).
2004-04-28 15:29:06.41 spid21 Recovery of database 'abcdef' (18) is 99% c
omplete (approximately 0 more seconds) (Phase 2 of 3).
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% c
omplete (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 'abc
def' (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 dis
played like this for database 'abcdef' and what it means,
"
Recovery of database 'abcdef' (18) is 99% complete (approximately 0 more sec
onds) (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 critical
server, only few people have access to the Server.
Apart from these problems any other problem which makes these mesg to displa
y 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 databas
e not happened and which situtation make
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 thi
s.
Why I raised this in forum is, out of 24 databases, only for this database t
his kind of informational mesgs. displayed and few jobs which connects to th
is database were also failed saying "timedout error" but at the time users a
ble to connect to this data
base 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 se
rver 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 h
appen to other databases also, right
"
2004-04-28 15:28:57.85 spid21 Recovery of database 'abcdef' (18) is 0% co
mplete (approximately 154 more seconds) (Phase 2 of 3).
- - -
- - -
2004-04-28 15:29:06.46 spid21 Recovery of database 'abcdef' (18) is 99% c
omplete (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