Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
leszekw
Starting Member
2 Posts |
 Posted - 2007-04-24 : 04:10:37
|
| Hello,I have a problem with my application. It uses SQLServer2000 sp3 installed on the computer with 8CPUs. Parallelism is set. Application deletes 250.000 rows from one table and updates 200.000 rows in another ("Delete from ... where id =XXX", "Update ... set ... where id = YYY"). It makes almost 500.000 commands being executed in less than 2 hours period. Operations are grouped in transactions consisting of 10-25 commands (both updates and deletes) and sequence of command execution is crucial.Somehow it happens (once for a few months - it is not deterministic) that one of the transactions fails. It looks like commands belonging to one transaction are executed in the inappropriate sequence and transaction is either rolled back or left half commited (like it was not atomic). I guess it hardly can be an application error. Do you know any SQLServer bugs which might be the possible reason?Thanks in advance for any ideas,Leszek. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-24 : 11:32:28
|
| are you doing the delete in one big transaction or small batches?************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
leszekw
Starting Member
2 Posts |
Posted - 2007-04-24 : 15:35:52
|
| Small batches. Usually one transaction consists of few UPDATE commands and few DELETE commands.Every command has condition on primary key (where ID = XXX) so one command affects only one row always. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-24 : 16:14:53
|
| hmm. perhaps its some external factors...someone else accessing the same data at the same time or has a lock on the data you are trying to delete that part of your script executes and the other cannot so it rolls back. Do you have profiler running during these operations? If its a nightly job then you can have profiler running during that time so you can see whats happening. you might not be able to catch the culprit right away but at least you can have some trace data to dig into whenever it happens, instead of guessing.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
Transaction problem