Click here to monitor SSC
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
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 2133
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 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
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 2133
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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 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