471,073 Members | 1,363 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

long transaction

Hi,

I am still not very proficient in SQLServer. So apology if the
question sounds basic.

We have a script to clean old unwanted data. It basically deletes
all rows which are more than 2 weeks old. It deletes data from
33 tables and the number of rows in each table runs into few millions.
What I see in the script (not written by me :-) ) is that all data is
deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
I have background in informix, such an action in Informix may result
in "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.

Also won't it have performance problem if all rows are marked locked
till they are committed.

TIA.
Jul 20 '05 #1
3 4527

"rkusenet" <rk******@sympatico.ca> wrote in message
news:bq*************@ID-75254.news.uni-berlin.de...
Hi,

I am still not very proficient in SQLServer. So apology if the
question sounds basic.

We have a script to clean old unwanted data. It basically deletes
all rows which are more than 2 weeks old. It deletes data from
33 tables and the number of rows in each table runs into few millions.
What I see in the script (not written by me :-) ) is that all data is
deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
I have background in informix, such an action in Informix may result
in "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.

I'm not sure what the "LONG TRANSACTION PROBLEM" in Informix is, but yes,
you basically don't want transactions to run for long periods of time.

As you point out, this can cause blocking on reads depending on your
isolation level. In addition, it can make recovery that much harder.

Assume your transaction takes 20 minutes to run (that's a really long time
admittedly) and your server gets rebooted 19 minutes into it. You're
looking at probably at least 19 minutes of rollback time when you reboot.
During this time the database will be completely inaccessible.

You have a couple of options that depend on your environment.

One is to simply break this up into multiple transactions. Of course then
it depends on what happens if one fails. Does this matter to other
transactions? Are their any dependencies?

It may also be possible to copy the needed data into a temp table, truncate
the original table and move data back.

But regardless, I would try to redesign this.

Also won't it have performance problem if all rows are marked locked
till they are committed.

TIA.

Jul 20 '05 #2
"rkusenet" <rk******@sympatico.ca> wrote in message news:<bq*************@ID-75254.news.uni-berlin.de>...
Hi,

I am still not very proficient in SQLServer. So apology if the
question sounds basic.

We have a script to clean old unwanted data. It basically deletes
all rows which are more than 2 weeks old. It deletes data from
33 tables and the number of rows in each table runs into few millions.
What I see in the script (not written by me :-) ) is that all data is
deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
I have background in informix, such an action in Informix may result
in "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.

Also won't it have performance problem if all rows are marked locked
till they are committed.

TIA.


If the data is unwanted, it seems unusual to delete it like that,
unless there really is a genuine requirement to do it inside a single
transaction. A large transaction like that can easily cause
performance and locking problems.

Assuming there isn't a real need to use a single transaction, then you
could do batch deletes instead, perhaps as an overnight maintenance
job to prevent users seeing unexpected results if they run queries
during the deletion.

Simon
Jul 20 '05 #3

"Simon Hayes" <sq*@hayes.ch> wrote
If the data is unwanted, it seems unusual to delete it like that,
unless there really is a genuine requirement to do it inside a single
transaction.
I don't see any such requirement. It can always continue next time if
the delete fails for some reason.
A large transaction like that can easily cause
performance and locking problems.
That's what I thought.
Assuming there isn't a real need to use a single transaction, then you
could do batch deletes instead, perhaps as an overnight maintenance
job to prevent users seeing unexpected results if they run queries
during the deletion.


I plan to remove BEGIN and COMMIT. This way each delete will be atomic
by itself.

thanks.

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by OakRogbak_erPine | last post: by
22 posts views Thread by Joseph Shraibman | last post: by
6 posts views Thread by Franco Lombardo | last post: by
3 posts views Thread by Eitan | last post: by
18 posts views Thread by Larry Herbinaux | last post: by
1 post views Thread by cheesey_toastie | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.