Record Locks: jobs

Showing posts with label jobs. Show all posts

Friday, March 30, 2012

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 Editi
on. 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
I'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 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 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 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 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 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

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

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

Wednesday, March 28, 2012

Recovery mode "SIMPLE" & SP2

I have several databases that perform daily backups. Ever since I installed SP2, the backup jobs are failing. The logs state that it's because the database recovery mode cannot be SIMPLE, but need to be either FULL or Bulk-Logged.

Can anyone tell me if this is true, and that I do have to change my recovery mode?

Here's the error:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "ICIS-SQL-SERVER".
Maintenance Plan: DB Backup
Duration: 00:08:05
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db_SamirTesting] TO DISK = N'D:\\mssql\\backup\\hl7 db_SamirTesting\\hl7 db_SamirTesting_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_SamirTesting_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [dg_efilm_153] TO DISK = N'D:\\mssql\\backup\\dg_efilm_153\\dg_efilm_153_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'dg_efilm_153_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db] TO DISK = N'D:\\mssql\\backup\\hl7 db\\hl7 db_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Back Up Database (Full) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Type: Full
Append existing
Task start: 2007-03-10T05:00:06.
Task end: 2007-03-10T05:08:08.
Success
Command:EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\master''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\model''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\msdb''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db_SamirTesting''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\dg_efilm_153''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db''
GO
BACKUP DATABASE [master] TO DISK = N''D:\mssql\backup\master\master_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''master_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO DISK = N''D:\mssql\backup\model\model_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''model_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO DISK = N''D:\mssql\backup\msdb\msdb_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''msdb_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10

Please help,

-tim

Check what kind of recovery model is adopted on the databases that are involved in this backup schedule.|||

I have this issue aswell but with the ReportServer database and the ReportServerTempDB database. ReportServer recovery mode is set to full

ReportServerTempDB recovery mode is set to simple.

We have a database maintenance plan set to back up all user databases (at the top of the window it says that simple will be ignored'. The maintenance plan runs but fails as it tries to back up the ReportServerTempDB database.

The messages in the history of the plan say…

9/5/2007 09:15 Database 'ReportServerTempDB' will not be backed up because it does not have its recovery model set to Full or Bulk Logged.

9/5/2007 09:15 Executing the query "BACKUP LOG [ReportServerTempDB] TO DISK = N'G:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\ReportServerTempDB\\ReportServerTempDB_backup_200705091638.trn' WITH NOFORMAT, NOINIT, NAME = N'ReportServerTempDB_backup_20070509163838', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It appears that it reports it as an error when it checks the recovery mode, but then tries to back it up anyway. I do not have any problems with any other databases except the reporting services ones. At the moment I have both set to full recovery mode, just to get the maintenance plan to work. Any ideas anyone? This worked fine on Sp1 with the database set to Simple recover mode..

Thank you!

|||You cannot backup the system database and report server based databases in the transaction log schedule, as the error is obvious due to the configuration for system databases recovery model and you will not be able change that.

Recovery mode "SIMPLE" & SP2

Can anyone tell me if this is true, and that I do have to change my recovery mode?

Here's the error:

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db] TO DISK = N'D:\\mssql\\backup\\hl7 db\\hl7 db_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Please help,

-tim

Check what kind of recovery model is adopted on the databases that are involved in this backup schedule.|||

I have this issue aswell but with the ReportServer database and the ReportServerTempDB database. ReportServer recovery mode is set to full

ReportServerTempDB recovery mode is set to simple.

The messages in the history of the plan say…

9/5/2007 09:15 Database 'ReportServerTempDB' will not be backed up because it does not have its recovery model set to Full or Bulk Logged.

Thank you!

Friday, March 30, 2012

Recovery of database 'abcdef'...

Recovery of database 'abcdef'...

Recovery of database 'abcdef'...

Wednesday, March 28, 2012

Recovery mode "SIMPLE" & SP2

Recovery mode "SIMPLE" & SP2

Record Locks

Blog Archive

About Me