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 from Deadlocks: retrying the query

I'm having some deadlocking issues in my ASP.Net 2.0 web application with SQL Server 2000. One solution I've come across involves catching the the deadlock error (at the application level) and then retrying the query again, in the hopes that whatever was causing the deadlock is no longer locked.

I've tried to implement such a solution, where I catch the deadlock exception, then try to execute the same query again. Only now I'm getting a "The transaction is in doubt" exception.

So am I going about this wrong? Am I supposed to retry the entire transaction, or is valid to just try to redo the one query that it failed on? Is there some way around the transaction being in doubt?

You should determine the cause of the deadlock and correct your SQL code rather than retry mechanism. Retry mechanism can be complicated depending on what you are doing, whether you are in a transaction and so on. Deadlock typically means that you have access paths in your code that is not optimized and different for similar operations. Check out the following links on how to troubleshoot deadlocks:

http://msdn2.microsoft.com/en-us/library/ms177433.aspx

http://msdn2.microsoft.com/en-us/library/ms188246.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8i93.asp

sql

Recordset Field Limit??

Hey Everyone,

I have what seems to be a unique problem, which I am not sure if it is an ASP problem or an SQL Server issue. I'm making a website that pulls its content from ntext fields within SQL Server 2000.

Heres my problem, when I pull the data into the page it cuts off the text from the field at a certain spot. I pulled the data into a variable and did some tests on it, if I run length on it I get 1023 and also if I run length on just the recordset field I also get 1023. But if I check the SIZE of the recordset I get a return of 2046. I have had this problem before with an Access database as well but it seems as if the amount of characters that it cut off at with the access database was in the 200's. I've self taught myself ASP so this could very easily be something I have missed. Same for both Access and SQL Server.One mystery is easy enough. ntext is a double-byte system, so 1023 characters takes up 2046 bytes.

The other mystery is a bit harder to figure. What sort of variable are you retrieving the data into? If it is not declared, it is probably a variant datatype, which could top out at 1k. I am not too good with VB6 datatypes, but have you tried casting the variable as VB's equivalent of text?|||Well I'm not using ASP.Net so no type casting, and actually I wasn't originally pulling the data into a variable type, I only done so to do those tests. I am pulling the data onto the page with the following

<%=(recordset.Fields.Item("ntextfield").Value)%>

But I did set a variable equal to that to check the length and size.|||I got nothing on that. Most I have ever worked with in ASP is varchar(1000). If you retreive the value into a variable, I take it you get the same result?

And I suppose there is no possiblity of you not using ntext? Terribly finicky stuff. At least, if you can avoid using chinese/japanese/greek/hebrew letters in your text, you can demote it down to just text. Likely, you can get a way with a varchar(4000) field or something.|||It is like that cuz the data being pulled in can sometimes be up to 10000 characters long... Ugh I bet thats going to cause me major headaches before this is said and done :D

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...

Records Versioning/Auditing

Howdy,
Forgive me if this is the wrong group. I'm writing a ASP.Net application and
i need to track every change to a table's records. In SQL 2005, is there a
way to easily keep a record change history? Something with rules or triggers
or something? Or am I stuck comparing every field manually before i perform
the UPDATE?
Thanks a lot!
--
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.netA trigger can do what you need (i.e. putting a changed record into an audit
table prior to or after an update).
I recommend checking out ApexSQL's Audit. It can set up everything for you
with a mostly point-and-click interface.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
> Howdy,
> Forgive me if this is the wrong group. I'm writing a ASP.Net application
> and
> i need to track every change to a table's records. In SQL 2005, is there a
> way to easily keep a record change history? Something with rules or
> triggers
> or something? Or am I stuck comparing every field manually before i
> perform
> the UPDATE?
> Thanks a lot!
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net|||Yeah, i saw them but at $600 per license, i'll figure it out myself.
thanks,
--
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.net
"TheSQLGuru" wrote:
> A trigger can do what you need (i.e. putting a changed record into an audit
> table prior to or after an update).
> I recommend checking out ApexSQL's Audit. It can set up everything for you
> with a mostly point-and-click interface.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
> news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
> > Howdy,
> >
> > Forgive me if this is the wrong group. I'm writing a ASP.Net application
> > and
> > i need to track every change to a table's records. In SQL 2005, is there a
> > way to easily keep a record change history? Something with rules or
> > triggers
> > or something? Or am I stuck comparing every field manually before i
> > perform
> > the UPDATE?
> >
> > Thanks a lot!
> >
> > --
> > David Lozzi
> > Delphi Technology Solutions
> > Blog: www.lozzi.net
>
>|||$600 is a real bargain for what you get in the package. If you get paid $20
per hour that is only 30 hours before you hit the same amount. No way you
can build out a fully functional system like Audit can do for you in that
time frame.
Note too that the license is just for the client that sets up the auditing.
You can audit unlimited servers from that one purchase. See here,
http://www.apexsql.com/sql_tools_audit.asp, 4th bullet point from the top.
And they have a viewer to report on the audited information that is only $50
per client.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:F7CA333A-71F4-4B8A-BC90-FD23D60CF3DA@.microsoft.com...
> Yeah, i saw them but at $600 per license, i'll figure it out myself.
> thanks,
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net
>
> "TheSQLGuru" wrote:
>> A trigger can do what you need (i.e. putting a changed record into an
>> audit
>> table prior to or after an update).
>> I recommend checking out ApexSQL's Audit. It can set up everything for
>> you
>> with a mostly point-and-click interface.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
>> news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
>> > Howdy,
>> >
>> > Forgive me if this is the wrong group. I'm writing a ASP.Net
>> > application
>> > and
>> > i need to track every change to a table's records. In SQL 2005, is
>> > there a
>> > way to easily keep a record change history? Something with rules or
>> > triggers
>> > or something? Or am I stuck comparing every field manually before i
>> > perform
>> > the UPDATE?
>> >
>> > Thanks a lot!
>> >
>> > --
>> > David Lozzi
>> > Delphi Technology Solutions
>> > Blog: www.lozzi.net
>>

Records Versioning/Auditing

Howdy,
Forgive me if this is the wrong group. I'm writing a ASP.Net application and
i need to track every change to a table's records. In SQL 2005, is there a
way to easily keep a record change history? Something with rules or triggers
or something? Or am I stuck comparing every field manually before i perform
the UPDATE?
Thanks a lot!
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.net
A trigger can do what you need (i.e. putting a changed record into an audit
table prior to or after an update).
I recommend checking out ApexSQL's Audit. It can set up everything for you
with a mostly point-and-click interface.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
> Howdy,
> Forgive me if this is the wrong group. I'm writing a ASP.Net application
> and
> i need to track every change to a table's records. In SQL 2005, is there a
> way to easily keep a record change history? Something with rules or
> triggers
> or something? Or am I stuck comparing every field manually before i
> perform
> the UPDATE?
> Thanks a lot!
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net
|||Yeah, i saw them but at $600 per license, i'll figure it out myself.
thanks,
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.net
"TheSQLGuru" wrote:

> A trigger can do what you need (i.e. putting a changed record into an audit
> table prior to or after an update).
> I recommend checking out ApexSQL's Audit. It can set up everything for you
> with a mostly point-and-click interface.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
> news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
>
>
|||$600 is a real bargain for what you get in the package. If you get paid $20
per hour that is only 30 hours before you hit the same amount. No way you
can build out a fully functional system like Audit can do for you in that
time frame.
Note too that the license is just for the client that sets up the auditing.
You can audit unlimited servers from that one purchase. See here,
http://www.apexsql.com/sql_tools_audit.asp, 4th bullet point from the top.
And they have a viewer to report on the audited information that is only $50
per client.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:F7CA333A-71F4-4B8A-BC90-FD23D60CF3DA@.microsoft.com...[vbcol=seagreen]
> Yeah, i saw them but at $600 per license, i'll figure it out myself.
> thanks,
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net
>
> "TheSQLGuru" wrote:

Records Versioning/Auditing

Howdy,
Forgive me if this is the wrong group. I'm writing a ASP.Net application and
i need to track every change to a table's records. In SQL 2005, is there a
way to easily keep a record change history? Something with rules or triggers
or something? Or am I stuck comparing every field manually before i perform
the UPDATE?
Thanks a lot!
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.netA trigger can do what you need (i.e. putting a changed record into an audit
table prior to or after an update).
I recommend checking out ApexSQL's Audit. It can set up everything for you
with a mostly point-and-click interface.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
> Howdy,
> Forgive me if this is the wrong group. I'm writing a ASP.Net application
> and
> i need to track every change to a table's records. In SQL 2005, is there a
> way to easily keep a record change history? Something with rules or
> triggers
> or something? Or am I stuck comparing every field manually before i
> perform
> the UPDATE?
> Thanks a lot!
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net|||Yeah, i saw them but at $600 per license, i'll figure it out myself.
thanks,
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.net
"TheSQLGuru" wrote:

> A trigger can do what you need (i.e. putting a changed record into an audi
t
> table prior to or after an update).
> I recommend checking out ApexSQL's Audit. It can set up everything for yo
u
> with a mostly point-and-click interface.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
> news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
>
>|||$600 is a real bargain for what you get in the package. If you get paid $20
per hour that is only 30 hours before you hit the same amount. No way you
can build out a fully functional system like Audit can do for you in that
time frame.
Note too that the license is just for the client that sets up the auditing.
You can audit unlimited servers from that one purchase. See here,
http://www.apexsql.com/sql_tools_audit.asp, 4th bullet point from the top.
And they have a viewer to report on the audited information that is only $50
per client.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:F7CA333A-71F4-4B8A-BC90-FD23D60CF3DA@.microsoft.com...[vbcol=seagreen]
> Yeah, i saw them but at $600 per license, i'll figure it out myself.
> thanks,
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net
>
> "TheSQLGuru" wrote:
>

Records Duplicate when edited...?

Hi,

I have written a web application using dreamweaver MX, asp.net, and MSsql server 2005.

The problem I am having occurs when I attempt to edit a record. I have setup a datagrid with freeform fields so that the user can click on edit, make the required changes within the data grid then click update. The data is then saved to the database. All this was created using dreameaver and most of the code was automatically generated for me.

The problem is that, not everytime, but sometimes when I go to edit a record once I hit the update button to save the changes the record is duplicated 1 or more times. This doesnt happen everytime but when it does it duplicates the record between 1 and about 5 times. I have double checked everything but cannot find anything obvious that may be causing this issue.

Does anyone have any suggestions as to what I should look for? Is this a coding error or something wrong with MSsql? Any ideas?

Thanks in advance
-Mitch

If you provide the code, then it will be easier to give suggestions.