MS-SQL rollback lasts for HOURS, Table inaccessable in meantime

MS-SQL rollback lasts for HOURS, Table inaccessable in meantime

Post by Charlie Barret » Fri, 22 May 1998 04:00:00



After aborting a large table transfer (48MB, 219k row) from Access using
an Append Query, the SQL Server table has been rolling back for over 10
HOURS.

I'm running SQL 6.5 SP4 on NT4.0 SP3 on a 2-processor 300MHZ PII 128MB
server with fast SCSI drives - so the hardware just ain't the problem!

1) Ran Append Query from Access to SQL Server linked tables
2) When Access came up with 8,000 key violations (due to design error in
an SQL table index), I selected NO (do not append remaining data).
3) Up thru 10 hours later, ALL USE OF THE TABLE FAILS with ALL
APPLICATIONS USING THE TABLE - An unacceptable situation!!!
4) Up thru 10 hours later, try to even look at the SQL table being
rolled back by double-clicking the linked table in Access, and you get a
query timeout failure
5) Up thru 10 hours later, try to select a record using ISQL/w, and it
hangs forever, as does trying to change design with Enterprise manager.
6) 10 hours later, tried stopping the SQL process and re-started it -
MISTAKE!!! NOW access to the WHOLE *&(*^&% DATABASE IS LOST while it's
"recovering". It's been HOURS since I did that, and it's STILL
"recovering"!!!

This is INSANE (I've been going thru this song & dance for a solid WEEK
now - first running out of log space, then running out of locks, then
Access just locked up several hours into the append, and the NT Server
spontaneously re-booted once during a recover database operation)!!!

Anyone know if Oracle is any better????

Please reply via e-mail also
Thanks,
Charlie

 
 
 

MS-SQL rollback lasts for HOURS, Table inaccessable in meantime

Post by Isaac Bla » Fri, 22 May 1998 04:00:00



>After aborting a large table transfer (48MB, 219k row) from Access using

Not that much data actually, we transfer comparable amounts from an
Access-based application to SQL Server several times a day

Quote:>an Append Query,

Now that's a culprit!  We export the table in question from the Access
database to a flat file and then BCP it in - all that can be done
automatically

Quote:>the SQL Server table has been rolling back for over 10
>HOURS.

It's probaly due to the fact that the whole process was enclosed in a
transaction and each row was inserted via separate INSERT statement.
That would explain this enourmous logging volume.
Quote:

>I'm running SQL 6.5 SP4 on NT4.0 SP3 on a 2-processor 300MHZ PII 128MB
>server with fast SCSI drives - so the hardware just ain't the problem!

>1) Ran Append Query from Access to SQL Server linked tables
>2) When Access came up with 8,000 key violations (due to design error in
>an SQL table index),

Normally we BCP into a table with no indexes, then create the
clustered index with SORTED_DATA, then create the remaining indexes.
This saves both time and space.  To do this the database must set
'select into/bulk copy' option to true.  The batch size for BCP should
be set to some reasonable ( not too big, not too small) number (say,
1000).  That will save time and minimize log requirements.

Quote:>.......................
>This is INSANE (I've been going thru this song & dance for a solid WEEK
>now - first running out of log space, then running out of locks, then
>Access just locked up several hours into the append, and the NT Server
>spontaneously re-booted once during a recover database operation)!!!

>Anyone know if Oracle is any better????

Have not had any Oracle experience, sorry. But I think that you can
get some decent results with SQL Server for much less money. Just do
not do any insane things to it.
Quote:>Please reply via e-mail also
>Thanks,
>Charlie


 
 
 

MS-SQL rollback lasts for HOURS, Table inaccessable in meantime

Post by Charlie Barret » Fri, 22 May 1998 04:00:00


Isaac,

You definitely hit on the problem...
Access always encloses the entire Append query in a single transaction. I
tried using VBA/DAO and single-record transactions, but that's slow as
molasses, too, although it doesn't have the eternal RollBack problem!

Thanks -- I'll research the BCP docs and try it
-Charlie



> >After aborting a large table transfer (48MB, 219k row) from Access using
> Not that much data actually, we transfer comparable amounts from an
> Access-based application to SQL Server several times a day

> >an Append Query,
> Now that's a culprit!  We export the table in question from the Access
> database to a flat file and then BCP it in - all that can be done
> automatically

> >the SQL Server table has been rolling back for over 10
> >HOURS.
> It's probaly due to the fact that the whole process was enclosed in a
> transaction and each row was inserted via separate INSERT statement.
> That would explain this enourmous logging volume.

> >I'm running SQL 6.5 SP4 on NT4.0 SP3 on a 2-processor 300MHZ PII 128MB
> >server with fast SCSI drives - so the hardware just ain't the problem!

> >1) Ran Append Query from Access to SQL Server linked tables
> >2) When Access came up with 8,000 key violations (due to design error in
> >an SQL table index),
> Normally we BCP into a table with no indexes, then create the
> clustered index with SORTED_DATA, then create the remaining indexes.
> This saves both time and space.  To do this the database must set
> 'select into/bulk copy' option to true.  The batch size for BCP should
> be set to some reasonable ( not too big, not too small) number (say,
> 1000).  That will save time and minimize log requirements.

> >.......................
> >This is INSANE (I've been going thru this song & dance for a solid WEEK
> >now - first running out of log space, then running out of locks, then
> >Access just locked up several hours into the append, and the NT Server
> >spontaneously re-booted once during a recover database operation)!!!

> >Anyone know if Oracle is any better????

> Have not had any Oracle experience, sorry. But I think that you can
> get some decent results with SQL Server for much less money. Just do
> not do any insane things to it.

> >Please reply via e-mail also
> >Thanks,
> >Charlie

 
 
 

1. 2-hour rollback across all connections

An ASP/SQL7/ADO application has lost all data entered within an
approximate 2-hour block twice in the last month. The data lost spans
all spids connected for that period. Neither resulted in an entry in
either the error log nor the NT Event log.

The only evidence that data was entered at all is correlating gaps in
the relevant seeded fields.

INSTANCE 1: A DBCC CHECKDB statement (non-destructive) was issued along
with a BACKUP. The backup did not begin until 105 minutes after the
CHECKDB statement was issued. All data entered during those 105 minutes
were rolled back out of the database. No errors or unusual events were
logged.

INSTANCE 2: At 11 AM, spid14 was discovered to be blocking spid11. Kill
14 was issued. All data entered that morning by all spids in that
database was rolled back.

All the transaction management is handled at the stored procedure level
(none from ADO) and implicit transactions are not enabled. I cannot find
any BEGIN TRAN statements that could have been the culprit (though I am
open to the possibility). Even if I did, could that explain the loss of
data across all spids?

Help?

2. where to get jet 4.0 to install with an vb6 app?

3. Add day/hour/half-hour to given date in PL/SQL

4. Silent / Unattended Install of SS2K Books Online (Updated - SP3)

5. Last 2 hours definition

6. prorest across network

7. preparing Date Time Year To Second for last 24 hours

8. Returning multiple columns with a function??

9. MS Access tables -> MS SQL server tables

10. knowing last modification / last access times of tables..

11. MS SQL RollBack

12. MS SQL commits before Rollback

13. How long would data last in Rollback Segment?