Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

DELETE running for 8 hours need to Stop the process Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 12:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:31 PM
Points: 466, Visits: 1,914
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
Post #1512108
Posted Thursday, November 7, 2013 12:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1512117
Posted Thursday, November 7, 2013 3:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
Interesting read. Gives the pretty much idea on what you can end up into if not consider the impact of the DML.
Post #1512176
Posted Thursday, November 7, 2013 3:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:31 PM
Points: 466, Visits: 1,914
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
Post #1512179
Posted Thursday, November 7, 2013 4:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,193, Visits: 4,267
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/

Post #1512198
Posted Thursday, November 7, 2013 4:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1512199
Posted Thursday, November 7, 2013 5:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
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.
Post #1512206
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse