473,382 Members | 1,551 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 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 4711

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: OakRogbak_erPine | last post by:
My company is considering purchasing MS SQL Server to run an application on (SASIxp). I am mainly familiar with Oracle, so I was wondering how long it would take to copy a database. Basically we...
2
by: Jo Siffert | last post by:
Hi all, I would like to perform an INSERT INTO LINKEDSVR.dbo.xyz.abc SELECT ... FROM dbo.dfg where LINKEDSVR is a linked server on another machine. Both servers are running SQLServer 2000...
29
by: pb648174 | last post by:
I have a very long transaction that runs on the same database that other users need to use for existing data. I don't care if they see data from the transaction before it is done and am only using...
22
by: Joseph Shraibman | last post by:
On a 7.3.4 database: explain analyse select count(*) from elog; Aggregate (cost=223764.05..223764.05 rows=1 width=0) (actual time=81372.11..81372.11 rows=1 loops=1) -> Seq Scan on elog ...
6
by: Franco Lombardo | last post by:
Hi all, I'm running DB2 8.1.5 on Windows 2000 professional SP4. When I try to start Control Center, I see the splash screen for a while, then it closes and nothing else happens. It happens...
3
by: Eitan | last post by:
Hello, I have run a long transaction on the DB (sql server) For some long transaction I have got the following message : What can I do in order to run it properly ? Error Type:
1
by: pb648174 | last post by:
I just wanted to post a follow up to a message I posted some months ago about a long running transaction that was blocking all other users... The link is below ...
18
by: Larry Herbinaux | last post by:
I'm having issues with garbage collection with my long-standing service process. If you could review and point me in the right direction it would be of great help. If there are any helpful...
1
by: cheesey_toastie | last post by:
I have a long query which I have set off and would like to stop, and rename one of the tables used. My query is due to my lack of understanding of the underlying structure of MSSQL-Server... ...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.