February 16, 2012 at 12:53 pm
we have a SQL job that runs a package, The package is basically delete from 2 tables , then import data from other server to these two tables.
But the job failed because transaction log full.
How can we avoid the problem?
We do want to keep the recovery mode as full
February 16, 2012 at 1:01 pm
In full recovery mode minimal logging is out.
You'll have to include backups into your process. Do a delete, backup the log. Perform an insert, backup the log. Continue ad naseum.
If that still doesn't get you there, you'll have to do staged inserts/deletes. Delete say 20% of the table, then backup the log.
Either you need more space or you need to workaround what you've got with a few hundred backups off to a filesystem somewhere.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2012 at 1:10 pm
Thank you, it is helpful!
March 7, 2012 at 1:25 am
For the delete process use "truncate table" if possible in your case. Truncate Table isn't logged.
Regards
Alex
March 7, 2012 at 1:52 am
Truncate table will not add to the transaction log, so where possible this is better.
Data Warehouses I have worked on have generally gone for Simple rather than full recovery mode. All of the source data is available again so could be reloaded so no need for full recovery. Going to simple should clear the full transaction log.
M
March 8, 2012 at 12:24 am
Alexander Kleinwächter (3/7/2012)
For the delete process use "truncate table" if possible in your case. Truncate Table isn't logged.Regards
Alex
Aaah, don't say that on this site. Many DBA's will get a heart attack when they read this.
Truncate table is a logged operation.
If it is inside a transaction, you can roll it back. Period.
A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2012 at 1:04 am
Koen,
I did not assume it was inside a transaction.
Good Point
Mark:-)
March 8, 2012 at 1:42 am
Going to Simple recovery option may not solve this problem: if the log file is filled by a single transaction then it doesn't make any difference how often it's cleared.
TRUNCATE TABLE is indeed a logged operation, but it's only the page deallocations that are logged, so it should take up less transaction log space than a DELETE. Of course, it's not an option unless you want to remove all rows from the table.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply