SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DELETE running for 8 hours need to Stop the process


DELETE running for 8 hours need to Stop the process

Author
Message
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2433 Visits: 2134
GilaMonster (11/6/2013)
Next time don't restart SQL while there's a large transaction rolling back.


One question here, don't you think the T-log file will keep growing even during the rollback? If the data needs to be put again, won't it add those entries to the log file again and make it grow.

I haven't tested this on my side yet, I think I can initiate a big delete on my machine, kill it and using undocumented functions, will try to read the entries in the log file.

Thanks
Chandan Jha
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232509 Visits: 46360
chandan_jha18 (11/7/2013)
GilaMonster (11/6/2013)
Next time don't restart SQL while there's a large transaction rolling back.


One question here, don't you think the T-log file will keep growing even during the rollback?


No. Not from the delete anyway. From other operations maybe.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6481 Visits: 2774
Interesting read. Gives the pretty much idea on what you can end up into if not consider the impact of the DML.
chandan_jha18
chandan_jha18
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2433 Visits: 2134
sqlnaive (11/7/2013)
Interesting read. Gives the pretty much idea on what you can end up into if not consider the impact of the DML.


Burned my hands with heavy deletes once, so always performed this operation in batches thereafter so that even if it had to rollback for whatever reason, it can do it fast enough plus doing it this way controls your CPU else delete operation is just nasty.

Cheers!!

Chandan
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28018 Visits: 4895
chandan_jha18 (11/7/2013)
sqlnaive (11/7/2013)
Interesting read. Gives the pretty much idea on what you can end up into if not consider the impact of the DML.


Burned my hands with heavy deletes once, so always performed this operation in batches thereafter so that even if it had to rollback for whatever reason, it can do it fast enough plus doing it this way controls your CPU else delete operation is just nasty.

Cheers!!

Chandan


I did not do the delete but yesterday I told them to run in batches and commit so that tis does not happen.

I have had issues with other DML operations from other users.

I was supposed to be on vacation.

Maybe I will be able to relax today.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13750 Visits: 4077
chandan_jha18 (11/7/2013)
[quote]sqlnaive (11/7/2013)
it can do it fast enough plus doing it this way controls your CPU else delete operation is just nasty.
its also perfect way to manage the log size.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6481 Visits: 2774
chandan_jha18 (11/7/2013)
sqlnaive (11/7/2013)
Interesting read. Gives the pretty much idea on what you can end up into if not consider the impact of the DML.


Burned my hands with heavy deletes once, so always performed this operation in batches thereafter so that even if it had to rollback for whatever reason, it can do it fast enough plus doing it this way controls your CPU else delete operation is just nasty.

Cheers!!

Chandan


Totally agreed. In between, also there should be a proper way to write your DML commands in a way that under any case you not accidentally press "F5" without selecting "WHERE" condition. I have seen one of my friend doing it accidentally after which he changed his way of writing code.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search