Delete statements causing Transaction Log to fill up

Question

Delete statements causing Transaction Log to fill up

firstrate

Old Hand

Points: 379
More actions
April 28, 2005 at 8:46 am

#88892

My task is to free up some database space. I am trying to delete a bunch of historical data from some tables and am now at the point where no matter what I delete, the statement crashes because the transaction log fills up and it tells me to back it up. I am using a basic delete statement:
begin tran
delete from tablename where date ='date'
commit tran
Is there a better way or some setting that I can add to my query that will prevent the trans log from completely filling up?

Thanks for any thoughts/ideas

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jimmy Jen SSCrazy Points: 2915 More actions · Answer 1

Take a look at:

(1) right click the database and select "Transaction Log" tab. Then check if "Restrict file growth" is selected. If so, that means you need to expand the file size for transaction log.

(2) check how much disk space is available where transaction log is located.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

Assuming your database is in full recovery mode.

You have to perform batch deletion to prevent log being filled up.

declare @go_on int

set @go_on = 1

while @go_on = 1

begin

set rowcount 1000

delete from tablename where date ='date'

if (@@rowcount<1)

set @go_on = 0

set rowcount 0

end

Run transaction log backup job regularly to free the spaces in log file.

If it is in "simple" recovery, you don't need to backup the log.

firstrate Old Hand Points: 379 More actions · Answer 3

Jimmy Jen:

1) unrestricted filegrowth is selected

2) I have about 5gb left on the drive

Allen Cui:

We are running SQL 7. The recovery mode has truncate log on checkpoint and select into/bulk copy checked. So is simple recovery only truncate log.., bulk logged only the select into/bulk copy option, and then full recovery is both checked? I tried searching through the books online and couldn't quite find the clarification I needed...

Thanks!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

How many rows are in that table?

How many shall remain after your job is done?

If you're about to delete more than there will remain, it might be an option to export the remaining ones (to another table or via bcp), then TRUNCATE or DROP the original table, and finally reimport (or recreate and then reimport) the remaining data.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]