April 28, 2005 at 8:46 am
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
April 28, 2005 at 9:20 am
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.
April 28, 2005 at 9:27 am
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.
April 28, 2005 at 9:58 am
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!
April 29, 2005 at 12:08 am
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]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply