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

Question on doing a lot of deletes in one Agent job step, keeping the Trans Log from exploding... Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 7:23 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:21 PM
Points: 755, Visits: 5,539
If I'm going to be doing a lot of large deletes from different tables, via an Agent job, and want to keep the Transaction log from exploding, would this work:

Wrap each delete statement in a begin Transaction / commit?
Similiar to this:
BEGIN TRANSACTION
DELETE TABLENAME WHERE ID NOT IN (SELECT ID FROM SOMEOTHERTABLE)
COMMIT

Yes, I know the not in is going to make these things take a long time each, the DB was here and poorly created well before I started.

The DB is in our QA, and we're clearing out a large chunk of the records so we can then shrink the QA DB down to free up some space in QA.

I could, but would rather not, put in a DBCC SHRINKFILE('DB_Log', 256) after each delete...
To give an idea: When I did just one delete, the log ballooned from ~700MB to ~25000MB. If it grows by almost that much for each delete, I'll run out of disk space for the logs really fast... Seeing as I've got ~40 tables in this one DB to delete from...

Thanks
Jason
Post #1563016
Posted Friday, April 18, 2014 7:29 AM This worked for the OP Answer marked as solution


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 @ 4:32 AM
Points: 40,669, Visits: 37,131
No, that's exactly the same as the delete without a transaction. It's a single statement, so it's implicitly in its own transaction.

Delete in batches, not in a transaction and have checkpoint between them (if the DB is in simple recovery)



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 #1563020
Posted Friday, April 18, 2014 7:56 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:21 PM
Points: 755, Visits: 5,539
Cool, thanks!

And yes, I should've mentioned the DB is in Simple recovery.
Post #1563037
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse