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

Recovering or saving reports -Help

We had a memory error on our DEV SQL 2005 Server, the instance name was corrupted, it won't start up and there are no backups of DEV. Reporting Services is running but the Server is not. I'd like to keep any dev reports that had been deployed here. Where would I find the deployed reports? Can I find them and save them before we uninstall and re-install this Server.

Thanks!

If the sql server is not recoverable then the reports are not. Do you still have the .rdls from when you deployed the reports? If so you would have to deploy them all again, but obviously this would not have the report configured as they were before.|||I only have the ones I created. I saved on the reportserver database and tempdb, I was hoping I could recover some of the information from there.

Recovering database

Sql Server 2000 on Win 2k:
I'm recieving the following message when trying to reattach a database file: "Error 823: I/O error 38(Reached the end of the file.) detected during last read at offset 0000000000000000 of file 'D:\MSSQL\Data\DbName_log.ldf'." The database came offline after a disk problem and it looks like the log file became corrupted. Any ideas on a way to restore this database from only the mdf files? Can you reattach a DB and recreate the log file?

My other idea was possible inserting into sysdatabases, putting the Db in 32768=emergency mode, and running DBCC CHECKDB? If that is even possible?

Any feedback will be greatly appreciated.

Thanks.The log file is corrupted. You need to create new log file for the database. The thing is that you will lose all the uncommitted transaction. use dbcc rebuild_log to create a new log file and reattach the db.

recovering a database w/no log

I am trying to attach a database with no log. Receiving
the following error:
Error 1813: Could not open new database 'optima3'.
Create database is aborted. device activation error. The
physical file name 'g:\logs\optima_log.ldf may be
incorrect.Try sp_attach_single_file_db
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"ababl" <anonymous@.discussions.microsoft.com> wrote in message
news:011d01c3d3aa$227af390$a101280a@.phx.gbl...
> I am trying to attach a database with no log. Receiving
> the following error:
> Error 1813: Could not open new database 'optima3'.
> Create database is aborted. device activation error. The
> physical file name 'g:\logs\optima_log.ldf may be
> incorrect.

Recover DB from c$

I am trying to restore a database. The backuo file is on c$. The restore
is not successful. The error message on the Event Viewer is "could not open
file."
Now if I move the backup file to d$, I can restore the db without any
problem.
I can restore other databases from c$ with the same permissions on the .bak
file.
?
Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you will have to move backup data
files to disk D other than disk C to restore it successfully. If I have
misunderstood your concern, please feel free to point it out.
This is a really strange issue and would you please help me check the
following things?
1. Whether the Windows login user has administrator permission or it has
the permission to access all disk C and disk D files.
2. You restore the file from Enterprise Manager or Query Analyzer? Could
you let me know what the SQL error log say?
3. Please reconfirm the correct typed database name in Disk C
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Recover DB from c$

I am trying to restore a database. The backuo file is on c$. The restore
is not successful. The error message on the Event Viewer is "could not open
file."
Now if I move the backup file to d$, I can restore the db without any
problem.
I can restore other databases from c$ with the same permissions on the .bak
file.
?Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you will have to move backup data
files to disk D other than disk C to restore it successfully. If I have
misunderstood your concern, please feel free to point it out.
This is a really strange issue and would you please help me check the
following things?
1. Whether the Windows login user has administrator permission or it has
the permission to access all disk C and disk D files.
2. You restore the file from Enterprise Manager or Query Analyzer? Could
you let me know what the SQL error log say?
3. Please reconfirm the correct typed database name in Disk C
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Recover DB from c$

I am trying to restore a database. The backuo file is on c$. The restore
is not successful. The error message on the Event Viewer is "could not open
file."
Now if I move the backup file to d$, I can restore the db without any
problem.
I can restore other databases from c$ with the same permissions on the .bak
file.
?Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you will have to move backup data
files to disk D other than disk C to restore it successfully. If I have
misunderstood your concern, please feel free to point it out.
This is a really strange issue and would you please help me check the
following things?
1. Whether the Windows login user has administrator permission or it has
the permission to access all disk C and disk D files.
2. You restore the file from Enterprise Manager or Query Analyzer? Could
you let me know what the SQL error log say?
3. Please reconfirm the correct typed database name in Disk C
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FHDid you read:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
HTH, jens Suessmeyer,|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribi en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>|||Not sure if you fixed this yet but I just had a db marked to this status and
it simply means that the Log file is knackered. Unfortunately, without ba
ckups you will lose some uncommitted data, but at least the written data wil
l be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF - this can
then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily. I g
uess the problem with the log file is that you never clear it down as a resu
lt of a backup. It makes me sad to see you treating a databas in that way!
:-)
Good luck matey.
Mike

RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FH
Did you read:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
HTH, jens Suessmeyer,
|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribi en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>
|||Not sure if you fixed this yet but I just had a db marked to this status
and it simply means that the Log file is knackered. Unfortunately,
without backups you will lose some uncommitted data, but at least the
written data will be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF -
this can then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily.
I guess the problem with the log file is that you never clear it down
as a result of a backup. It makes me sad to see you treating a databas
in that way! :-)
Good luck matey.
Mike
mikeroberts23
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1201952.html

RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FHDid you read:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
HTH, jens Suessmeyer,|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribió en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>|||Not sure if you fixed this yet but I just had a db marked to this statu
and it simply means that the Log file is knackered. Unfortunately
without backups you will lose some uncommitted data, but at least th
written data will be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF
this can then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily
I guess the problem with the log file is that you never clear it dow
as a result of a backup. It makes me sad to see you treating a databa
in that way! :-)
Good luck matey.
Mik
-
mikeroberts2
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message1201952.htm

Recover Analysis Server from crash recovery

I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape. My
server came on-line with no error, i've not detect lost data, but, I don't
know if AS is now all fonctional or i need some configurations for finish
recover. The books on-line from Microsoft site have not information about
crash recovery.
Please can you tell me if i need some other restore files or other
configurations on AS ?
Thanks.
Sylvain H.
You don't need anything else...
Bye
Francesco Anti
"SHuet" <SHuet@.discussions.microsoft.com> ha scritto nel messaggio
news:CEAE3231-6CCA-421B-AF82-D255ADFB3EA5@.microsoft.com...
> I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape.
> My
> server came on-line with no error, i've not detect lost data, but, I don't
> know if AS is now all fonctional or i need some configurations for finish
> recover. The books on-line from Microsoft site have not information about
> crash recovery.
> Please can you tell me if i need some other restore files or other
> configurations on AS ?
> Thanks.
> Sylvain H.
>

Recover Analysis Server from crash recovery

I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape. My
server came on-line with no error, i've not detect lost data, but, I don't
know if AS is now all fonctional or i need some configurations for finish
recover. The books on-line from Microsoft site have not information about
crash recovery.
Please can you tell me if i need some other restore files or other
configurations on AS ?
Thanks.
Sylvain H.You don't need anything else...
Bye
Francesco Anti
"SHuet" <SHuet@.discussions.microsoft.com> ha scritto nel messaggio
news:CEAE3231-6CCA-421B-AF82-D255ADFB3EA5@.microsoft.com...
> I've server where I recover...\Data and ...\Bin\*.mdb. from backup tape.
> My
> server came on-line with no error, i've not detect lost data, but, I don't
> know if AS is now all fonctional or i need some configurations for finish
> recover. The books on-line from Microsoft site have not information about
> crash recovery.
> Please can you tell me if i need some other restore files or other
> configurations on AS ?
> Thanks.
> Sylvain H.
>

Recordset error 800a0bb9

I know, I know this is really an ASP question but I'm thinking that you db gurus write some frontend code too. I have an ASP script (see snippet) that connects to my SQL Server 7 db (see connect string below).

16 sqlUser = "SELECT instructorId, schoolId, salutation, firstName, lastName, phone, email " & _
17 "FROM tblInstructors " & _
18 "WHERE (((userName)='"&Request.Form("userName")&"') AND ((password)='"&Request.Form("password")&"'))"
19
20 Set rsUser = Server.CreateObject("ADODB.RecordSet")
21 rsUser.Open sqlUser, oConn

This code runs fine as long as I key in a valid username and password. If I key in a bad username and password combo the db returns the following to my browser:

ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/loginProc.asp, line 21

I tested by writing the finished sql to my browser and pasting it into the Enterprise manager and it runs as expected, I get a record with valid credentials and no record otherwise (but no error).

I'm using SQL Server v7, can anyone shed any light on this? thanks

connect string:

"Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=GradeTrax;" & _
"User ID=sa;" & _
"Password=pword"the db returns the following to my browser:

Ok maybe its not the db returning this message but ADO. Still does anyone have any experience with this?|||What's the line immediately following 21 (empty lines excluded)?

If you're attempting to pull a value from a recordset that is empty, you'll generate an error. Always make sure you're recordset actually contains records:

If NOT rsUser.BOF AND NOT rsUser.EOF Then
'User is valid
Else
'User is invalid
End If|||The next line I'm checking the status of EOF, however the line that is kicking the error is 21, so line 22 is never being processed.|||You don't need all the parens.. try:

"WHERE userName = '" & Request.Form("userName") & "' AND password = '" & Request.Form("password") & "'"

As a note, this will make both the userName and password fields case sensitive. If you want to make just the userName case insensitive, use the keyword LIKE in place of the "=".|||Thanks Seppuku,
This is really becoming very irritating. I've tried what you said and have in fact tried numerous variations, also using the Microsoft KB Article 235892 as guide and still can't get this to work. I've done response.write(sql) and pasted results here:

SELECT instructorId, schoolId, salutation, firstName, lastName, phone, email FROM tblInstructors WHERE userName LIKE 'dude' AND password LIKE 'badpassword'

again I'm convinced that this is not an SQL thing but a ADO thing, and what makes me mad is that it works as long as the db returns at least one record and fails if no records match criteria.

Any other info would be great.|||to all you coding comrads. I appologize for starting this thread. I stink as a programmer, I want to pump gas for a living or mow lawn or flip burgers or something.

I had a piece of code further down that did a redirect if login failed that looped back on the same page instead of redirecting to the login page. My error message came when the page was loaded the second time with the form collection null. I hate it when computers do what you tell them to and not what you want them to.|||PEBCAK - Problem Exists Between Chair and Keyboard.|||I hate it when computers do what you tell them to and not what you want them to.Me too, but the darned things keep right on doing it no matter how often I beat them!

-PatP|||PEBCAK - Problem Exists Between Chair and Keyboard.I like that! We call them id10t errors.

-PatP|||Some great jargon came from early editions of WIRED magazine... PEBCAK was one of those.. unfortunately, they've fizzled out...

Recordset Destination

Every time I try to us a recordset destination for error output, I have problems with setting the "VariableName" field. Is this a known issue? Or am I just doing something wrong?

Thanks
Jim

Jim,
Can you elaborate on what the problem is? An error message would be helpful.

My only tip based on what you have said is make sure that the type of the variable to which you are assigning the recordset is "Object".

Regards
Jamie|||Here is the text I get

Error 3 Validation error. Data Flow Task: Recordset Destination [332]: The VariableName property is not set to the name of a valid variable. Need a runtime variable name to write to. Package.dtsx 0 0|||Have a look here and see if it helps

This is the June CTP

Shredding a Recordset
(http://www.sqlis.com/default.aspx?59)|||In addition to having to point to a variable of type "Object", this property is also case sensitive. You may want to specify the variable's namespace as well.

Other than that I can't think what's wrong because I have had this working!

-Jamie|||Thanks Allan.

Pretty simple and I figured it was something like this. I didn't realize I needed a "variable" to hold onto my recordset.

Jim|||You need to create a variable by providing a name, of type object and also the scope of the variable. Check your object type.

I have written a book for beginners and even if you are not you may benefit from reading it. I recommend as it gives you a more deeper understanding of packages as implemented in the Visual studio 2005

http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book

Records don't lock-one user receives error

I created a MSAccess application with the forms in one db (front end) and the tables in another db (back end). The network admin migrated the back end to SQL Server. With a few minor changes (changing -1 to 1 in queries, etc.) the migration seemed to go pretty smoothly and everything works fine except....

The MSAccess backend used to lock records when someone was editing them. The status of the record was indicated in the record selector on each form, a right pointing arrow to indicate the record was free, a pencil with three dots indicating the record was locked by the current user, and a circle with a diagonal line to indicate the record was locked by another user. When 2 users attempted to simultaneously edit a record, the first user got the lock on the record. The second user received a message as soon as they attempted to edit the locked record to the effect that the record was locked.

Now (since the migration to SQL Server) the record selectors no longer give any indication as to the locking status of the records. When 2 users attempt to simultaneously edit a record, both are able to enter information in their forms, but when the second user attempts to save the record they receive the following message:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

Please help me find the cause of this behavior. Is it something I need to change in the MSAccess front end or something that needs to be set in the SQL Server back end by the network admin?

I am moving this question to the SQL Server Database Engine forum.

Records dont get deleted from the database!

I am using this code to delete records from a table in the database that has two fields (both are primary keys) I am not getting any error, but nothing gets deleted. Thanks for the help.

Code:

protectedvoid btnRemove_Click(object sender,EventArgs e)

{

Remove();

Response.Redirect("RegisteredCompanies.aspx");

}

protectedvoid Remove()

{

//update database with filename

//Save Changes

connection.Open();

using (SqlTransaction trans = connection.BeginTransaction())

{

try

{

SqlHelper.ExecuteNonQuery(trans,CommandType.StoredProcedure,"spRemoveCompany",newSqlParameter("@.Userid",getGdUserID(Profile.UserName)),newSqlParameter("@.BusinessID",getBusinessID());

}

catch (Exception err)

{

// throw exception

trans.Rollback();

lblStatus.Text ="An error has occurred in the delete process. Please try again.";

}

}

Here is the stored procedure:

ALTERPROCEDURE[dbo].[spRemoveCompany]

@.Useriduniqueidentifier OUTPUT,

@.BusinessID uniqueidentifier OUTPUT

AS

DELETEFROMAssociations2WHEREUserid = @.Userid AND BusinessID = @.BusinessID

RETURN

You must commit the transaction in your code.

|||

Also, why are you using OUTPUT on your parameters?

|||

yes, I used output in the stored procedure. How do i commit the transaction? Thanks!

|||

Got it working now!! Thanks a lot!!

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.
See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
AMB
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>
|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/d...br />
8dbn.asp
AMB
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combin
ed
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combin
ed
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>