Can A Rollback Be Delayed?

Nov 24, 2006

Hi all, thanks for reading.

Bit of a puzzler...

A customer of ours has reported a intermittent error using one of our applications to receive some stock.

Under normal circumstances the application updates about 10 tables in a single transaction, inserting to some, updating others. After the stock receipt they print off a Crystal report against the receipt data.

The customer is saying that (very) occasionally they perform a receipt (no errors) and print off the report (no problems) yet when they come to access the data some time later it is almost as if no receipt has been made.

I have seen their data and can see what they mean. None of the expected data updates seem to have taken place. And yet there is the report sat there, indicating that it must have...

Looking closely at the data I can see breaks in the sequencing in certain identity column-carrying tables, during the period when the stock receipt was made. To all intents and purposes, therefore, it looks as though a rollback has occurred.

Is this possible? Could a Crystal report show uncommitted data which is then rolled back? How 'long' can a rollback take? Can it be initiated in some other way?

NB: We have done all the obvious things like:
- checking they have received against the correct DB;
- checking that no app or procedure can remove data in this way.
- verified that the report is reporting from the correct place.
etc.

The 'missing' data is so perfectly removed, my instinct says it must be a rollback but I can't see how this can be (yet).

All suggestions gratefully received - how could I track down whether this was occurring? Or is it my fevered imagination?

Many thanks!!!!

pmb

ps: According to our records, this customer is running SQL 7 SP3.

View 5 Replies


ADVERTISEMENT

Lost Delayed - Write Data

Dec 4, 2001

Hello All,

Thanks in advance for your help.

I am wondering if there is a correlation between the size of a database and the amount of RAM required in the server. Here's what's going on:

NT4.0 SP 6a, SQL 7.0
2-800 MHz Pentium III Processors
512 MB RAM
Swap Files: two, divided evenly between two partitions, min 512 per partition (dynamic)
3- 9.2 GB Drives, RAID 5

SQL Database size: 2.5 GB

The SQL administrators have a scheduled job set to dump the database every four hours, so they have 6 of these dump files per day. Seagate Backup Exec v 6.? backs up those files every evening. Of course, this is a mission-critical application.

As the NT/Hardware administrator, I was tasked to flash upgrade all the hardware that could be flashed. That happened about three weeks ago. Ever since I flashed it, getting the data dumps has been an inconsistent process. Sometimes it works, sometimes it errors out. The error message is " Lost Delayed - Write Data: The system was attempting to transfer file data from buffers to (path). The write operation failed, and only some to the data may have been written to the file."

On and off during the day, you can't even copy a small file on the server without receiving the same message.

Before I flashed the server, it was blue-screening and needing to be rebooted about once every two weeks. Since I flashed it, the server hasn't been rebooted, because it hasn't blue-screened.

The database dumps are about 2 GB. We were thinking that the way the dump process worked was that a snapshot of the data was put into memory before it was written to disk. We thought since the dump size was larger than Physical Memory + Virtual Memory, that that could be causing the error.

What do you guys think?
1. Is dumping the database first the preferred way to get a backup, or should a backup directly to tape be scheduled instead?
2. Do we have enough RAM for the database?
3. Are there special settings we should set for the RAID controller card to maximize throughput for SQL?

Thanks again. I am definitely not a SQL DBA and any information you can provide would be really helpful.

View 2 Replies View Related

Classification Has Been Delayed Because The Routing Information...

Apr 25, 2007

Any ideas on why I get this message in the transmission status:



Classification has been delayed because the routing information is currently being updated.



I did create/drop some routes because I had pointed them to the wrong service, but that was some time ago.



I also just tried to restart the sql instance and it hung on stopping so I'm assuming something with the routing caused the issue.



Any ideas?



Thanks.

View 3 Replies View Related

How To Rollback SQL

Aug 12, 2007

Hello experts, I made a huge mistake on a production server, ran an update without the WHERE clause. UPDATE myTableSET Field1 = 'SOmetext',Field2 = 'sometext2'Is there a way to rollback? I read something a while ago about transaction logs.SOS!Thanks 

View 4 Replies View Related

Rollback

Sep 27, 2007

HI,
 Suppose I execute any transaction say inserted some rows.
I want  to delete my previous transaction using rollback.
What is the proper syntax for that.
In Query Window:
Insert ---------- executed
rollback.
it was asking for name of the transaction. How to give that.
Help me out
Thanks
Bye
 
 
 

View 1 Replies View Related

Why Not Rollback?

Nov 12, 2003

This is my stored procedure. Problem is that a rollback does not happen when there is an error ? How can this be, am starting and ending the transaction?

CREATE Procedure opretKunde
(
@cvr int,
@url varchar(200),
@firmaNavn varchar(50),
@adresse varchar(150),
@postnr int,
@fax int,
@tlf int,
@email varchar(200),
@bruger varchar(20),
@kode varchar(32)
)
AS

BEGIN TRANSACTION

INSERT INTO login
(
brugernavn,
kodeord,
type
)
VALUES
(
@bruger,
@kode,
'k'
)

INSERT INTO kunde
(
cvr,
url,
navn,
fax,
tlf,
email,
brugernavn
)

VALUES
(
@cvr,
@url,
@firmaNavn,
@fax,
@tlf,
@email,
@bruger
)

INSERT INTO adresse
(

type,
cvr,
adresse,
postnr
)
VALUES
(

'b',
@cvr,
@adresse,
@postnr
)

COMMIT

GO

View 1 Replies View Related

Rollback

Aug 10, 2000

Hi,

Can I rollback a database if I have transaction logs?

For example, I want to rollback the database without all the transactions that occurs within the last 10 days.

Thank you.

View 3 Replies View Related

Rollback

May 18, 2000

I tried killing a rollback several times, but it would not disapear.
I came back the next day, it is still there.

Anyone knows how to get rid of a rollback?

Thanks in advance.

View 2 Replies View Related

Rollback

May 5, 2000

Hi:

I have rollback on one of the users. I tried killing
her process through Enterprise Manager and Query Analyzer,
but I still can see her process on the screen. However, her
login is grayed out after I execute the kill query.

I have encountered this problem several times, and the only way
I can get clear the rollback is by rebooting the server.

Does anyone have a sugeestion on how to clear the rollback
without rebooting.

Please advise if you have any suggestion.

Thanks in advance.

View 2 Replies View Related

DTS ROLLBACK

Apr 5, 2000

Hi All
A question on DTS ROLLBACK CAPABILITY.
Is it possible to setup a DTS pkg to roll back the loaded files if a step fails?
If a step fail records inserted by Data Pump to a table should be removed.
Is this possible? If so how can we achieve this?

Any advice is appreciated.

Thanks

View 5 Replies View Related

Rollback

Dec 10, 2003

when i give rollback in my QA it gives an error saying (something like this)
- no begin transaction specified

In oracle it works fine why?

View 6 Replies View Related

Rollback

Jan 15, 2004

how to rollback(undo) record was i deleted???

pls tell me...

View 1 Replies View Related

Rollback

May 22, 2004

Hello every body,
I would like to know the procedure, that how to rollback my transaction in sql server 2000

View 5 Replies View Related

ROLLBACK

Jun 29, 2006

Hi,

i wanna know something, i inserted some rows of data to a table, but i wanna undo those insertion. How can i do it?

thanks

View 9 Replies View Related

Is Rollback Possible?

Aug 29, 2006

hi friends,

is it possible to roll back a transaction here? i heard that it will be possible only if we wrote the code only in certain block.please tell me the block.


vinod

View 5 Replies View Related

Rollback

Oct 17, 2007

The code inserts into table TABLES. TABLES has a primary key.
If the insert statement has no error it should insert 'ROWS INSERTED' to table CONTROL and update table DATETRACKING else insert 'ROWS NOT INSERTED' to table CONTROL and do not update table DATETRACKING.
1- I will like to improve my code if it is possible.
2- Can I use a rollback?

DECLARE @ROWCOUNT INT
DECLARE @ERROR INT
DECLARE @LASTDT DATETIME

SET @LASTDT =GETDATE()
BEGIN TRAN
insert tables
select 4

SELECT @ROWCOUNT = @@ROWCOUNT, @ERROR = @@ERROR
IF (@ERROR <> 0)
BEGIN
INSERT CONTROL
SELECT 'ROWS NOT INSERTED', @ROWCOUNT
END
ELSE
BEGIN
INSERT CONTROL
SELECT 'ROWS INSERTED', @ROWCOUNT
END
COMMIT TRAN

IF (@ROWCOUNT > 0)
BEGIN
UPDATE DATETRACKING
SET LASTDATEDL = GETDATE()
END




http://www.sqlserverstudy.com

View 4 Replies View Related

Rollback

Jan 28, 2008

Hi All,

Can anyone please tell how to rollback a transaction or a command.

TIA

RKNAIR

View 1 Replies View Related

Rollback SP4

May 12, 2008



Hi all

I have SQL server 2000 with SP4,
1.Some of the reason,I want roll back SP4 and I want replace SP3.
2.Is it true SP4 slow down the Quey performance compare with SP3.

any suggestion appreciated.

View 1 Replies View Related

My Rollback Does Not Work

May 30, 2007

My rollback does not work
In my SP I want any of cmdS,cmdS2,cmdS3,cmdS4 produces error Rollback must be executed for all of cmdS,cmdS2,cmdS3,cmdS4 . I tested for error producing situation but no rollbak occured.
How can I solve this problem. Thanks.
Below is my SP .
..........
..........
.......... 
begin transaction
..........
..........If Len(ltrim(rtrim(@cmdS)))>0
execute(@cmdS)
 If Len(ltrim(rtrim(@cmdS2)))>0
execute(@cmdS2) If Len(ltrim(rtrim(@cmdS3)))>0
execute(@cmdS3)
 If Len(ltrim(rtrim(@cmdS4)))>0
execute(@cmdS4)
If Len(ltrim(rtrim(@cmdS)))>0 or Len(ltrim(rtrim(@cmdS2)))>0 or Len(ltrim(rtrim(@cmdS3)))>0 or Len(ltrim(rtrim(@cmdS4)))>0
Beginif @@ERROR <>0
begin
rollback transaction
set @Hata = 'Error !'
end
Else
Beginset @Hata = 'Sucessfully executed :)' End
End
commit transaction
RETURN
 
 

View 1 Replies View Related

What To Do After A Transaction Rollback?

Dec 9, 2003

Hi!

I just made a transaction which writes and reads some data from a database as one unit. Now, I would like to know, what happens, if this transaction does not commit, but instead performs a rollback? Should I use a boolean loop which keeps on trying the transaction until it commits or is there an alternative? I would appreciate any suggestions or help!

Thanks!
Timothy

View 1 Replies View Related

Timeout On Rollback

May 17, 2004

I keep getting timeout expired when trying to rollback a transaction (have not tried to commit yet). The script hangs for around 30 secs, then fails.

Now, i've seen there's a command timeout for sqlcommands which defaults to 30 secs, but cannot find anything similar for sqltransaction...

Anyone can help?

Thanks. -julio

View 3 Replies View Related

SQL Transactions RollBack...

Dec 17, 2004

Hello:

I am just starting to falmiliarize myself with SQL transactions... I just created an SQL transaction... The first statement gets a value, if the value equals "" then the second statement executes... So if the value <> "" Then the second statement wont execute... What would happen in this scenario if the .Rollback is triggered? Heres my code:

Try
conSqlConnect.Open()
objTransaction = conSqlConnect.BeginTransaction
cmdSelect.Transaction = objTransaction
cmdInsert.Transaction = objTransaction
dtrdatareader = cmdSelect.ExecuteReader()
While dtrdatareader.Read()
varCheckNumber1 = dtrdatareader("Status")
End While
dtrdatareader.Close()
If varCheckNumber1 = "" Then
cmdInsert.ExecuteNonQuery()
End If
objTransaction.Commit
Catch
objTransaction.RollBack
Return "00"
Finally
If conSQLConnect.State = ConnectionState.Open Then
conSqlConnect.Close()
End If
End Try


Any Ideas?

View 5 Replies View Related

Rollback Issue

Mar 30, 2001

Hello list,

I've some problem in SQL Server 7.0 and hope some one out there will help me with it. The problem is as follows:

When I view a proces in the process info screen, I see one process says "Rollback" in the status window and the spid is greyed out like a system process. In the command window it says, Select and wait type is "Exchange". This is taking too much cpu cycles and causing much of the server to perform very slow.

I tried to kill this session from the ISQL and Enterprise manager, But to still see this process running on with out getting killed. Also this process keeps coming.

Please advice me how to get rid of this and why is it ocurring again and again.

Thanks in advance.

Khan

View 2 Replies View Related

Automatic Rollback

May 1, 2001

I am getting an automatic rollback in a transaction that fails to insert a row, via a trigger, due to a duplicate key. The transaction is automatically rolled back and unless the error correctly handled a 'partial update' occurs. Why in the example below is the transaction automatically rolled back (statements 1 to 3) and is there any way of knowing what errors generate an auto rollback. For example if the duplicate key error occurs on the insert into table B below, and not the triggered update, then no auto rollback occurs.

Begin Tran -- 1
insert into A values ('A1') -- 2 updates OK
insert into B values ('B1') -- 3 updates OK
-- triggers insert into B_Hist -- 4 Fails with Duplicate Key error
insert into C values ('C1') -- 5 updates OK
Commit Tran -- 6 3902 error No corresponding Begin

Thanks.

View 3 Replies View Related

Rollback Process--please Help

Aug 16, 2000

Hi,

I have this weird situation on one of the production database.
Somebody ran a sql script couple days ago and must have hit cancelled. Somehow this script is still rolling back. The script is actually very simple statement..should not take more than couple miliseconds to run. Is there any way to see whether rollback is really in process or this process has simply hung? is there any other way to get rid of this rollback without stopping and restarting sql server services? Can it cause problems in getting sql server up again if I restart sql server services?

View 7 Replies View Related

Rollback Optimization

Apr 28, 2000

This may sound a little silly, but does anyone have any words of wisdom on how to optimize a server/database for minimim rollback? We have some multimillion row tables we were trying to do updates against, and after several days they increased the size of the transaction log to the point they filled up the drive the database files/logs were on. We've now been running a rollback for about five days. I'd like to make sure this doesn't happen again.

View 1 Replies View Related

What Will Happen -- ROLLBACK Or NONE?

Jul 14, 1999

Hi,

what will happen in the following occasion?

checkpoint --> begin tran --> system failure

In MOC (SQL 7.0 Implemetation), it says there will be rollback, but in my humbel opinion, there should be nothing happened.

I think there was no dirty writing in this case. No data pages written, no log
pages written to disk. Am I right? Then, there might be nothing happened
instead of ROLLBACK.

If I am wrong, would you please let me know what is wrong in my thougt?

Thanks in advance,

View 2 Replies View Related

Rollback Of Sp Calling Another Sp

Mar 10, 2006

If you have a begin tran in a sp that calls another sp and that called sp fails. can you execute a roll back from the called sp, or should it return and error which will roll back the begin tran in the original sp

View 1 Replies View Related

SQL Rollback Issues

Nov 20, 2004

Hello all,

I have been informed that most (if not some) RDBMS are faced with problems with the SQL ROLLBACK (also known as undo) command. I have been searching online, but have not found any useful relevant sources on what this issues are.

I am wondering if their are any experienced MS SQL Server (or any other RDBMS) users who might be able to provide feedback on this matter.

Krz

View 1 Replies View Related

Despirately Need Help! Must Rollback But Don't Know How!!!

Oct 6, 2005

Don't ask, but I iadvertantly managed to delete 57,000+ records in the EM console and I need to roll back the database a few minutes to recover said information. I do not care about transactions that have occured since due to shutting the system down immediately upon realizing what I did. I have however never had the need to roll back the database before, so I have experience doing so. Any help is desperately needed and greatly appreciated!!!

View 14 Replies View Related

ROllback Transaction

Feb 26, 2007

How do I find out the transaction name to rollback. Thanks

View 1 Replies View Related

Sequence In Rollback

Oct 16, 2014

I am not sure about the ANSI/ISO rules on this? We have a SEQUENCE and insert it into a table.

CREATE SEQUENCE Cheque_Seq
AS INTEGER
START WITH 1
INCREMENT BY 1;
CREATE TABLE Cheque_Book
(cheque_seq INTEGER*NOT NULL PRIMARY KEY,
cheque_amt DECIMAL (15,2) DEFAULT 0.00 NOT NULL);

If I do an insertion with the SEQUENCE in a transaction and then ROLLBACK the transaction, what happens to the sequence value? My thought is that the value should reset to the state it was in before the transaction (basic definition of a ROLLBACK). But it does not in the products I have looked at.

View 1 Replies View Related

SQL Enterprise... Rollback

Apr 21, 2004

I tried adding columns to a table on a database manually. Unfortunately the database doesnt have a lot of free space, and on saving the columns, it was slowly running out of space. I made the decision to end the (sql enterprise) task via task manager. I am now unable to get into enterprise... I am assuming it is trying to rollback?<>?. can anyone tell me what happens next. will the database still be intact..

thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved