April 18, 2014 at 7:23 am
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
April 18, 2014 at 7:29 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2014 at 7:56 am
Cool, thanks!
And yes, I should've mentioned the DB is in Simple recovery.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply