DB in Simple Recovery Mode but TLOG still becomes Full 2

[JohnBates]

Hi everyone,

This 2005 database is in Simple recovery and I need to delete approx 1 million rows from a table.

The max size for the trans log is set to 5 GB and I cannot increase it becasue no more space is available on the drive.

I shrink the log down to 50 MB and run this block of code. After 25 mins the log is full (5 GB) but no records have been deleted.

Note: there are 15 indexes on this table - is that a factor? Should I drop indexes before running the Delete?

declare @curDate datetime
Set @curDate = getDate()
delete from dbo.events
where Datediff(day, EVENT_DATE, @curDate) > 150;


Thanks, john

 

[bborissov]

Could you use other technique:

declare @curDate datetime
Set @curDate = getDate()
SELECT * INTO #Temp 
dbo.events
where Datediff(day, EVENT_DATE, @curDate) <= 150

TRUNCATE TABLE dbo.events

INSERT INTO dbo.events
SELECT * FROM #Temp

DROP TABLE  #Temp

TRUNCATE command is not logged one, but make sure you have a pretty good backup first.

Also you must remove all FK constraints from that table if any.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Faq183-3141

not only will it be faster, but it will be transaction log friendly, too.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnBates]

Thanks for your ideas.

declare @curDate datetime
Set @curDate = getDate()
SELECT * INTO #Temp
dbo.events
where Datediff(day, EVENT_DATE, @curDate) <= 150

This database is currently 650 GB
150 days of data written to a Temp table would be many GB more than I have available

No foreign keys exist. Would dropping the regular indexes (I would keep the clustered PK) help to speed up the delete?

Thanks John

 

[RiverGuy]

Take George's/the FAQ author's advice -- don't delete all of your rows at once, so you end up with smaller transactions.

 

[JohnBates]

Thanks, George.

This is what I adapted from your post and it works very well:

declare @curDate datetime
declare @AllDone bit;
declare @LoopCtr smallint;
Set @curDate = getDate()
Set @AllDone = 0;
Set @LoopCtr = 0;
BEGIN
WHILE @AllDone = 0 -- loop until all qualifying rows are deleted
BEGIN
SET ROWCOUNT 50000;
SET @LoopCtr = @LoopCtr + 1;
delete from dbo.events
where Datediff(day, EVENT_DATE, @curDate) > 180;
IF @@ROWCOUNT = 0
SET @AllDone = 1;
Backup log EventsManager with truncate_only;
DBCC SHRINKFILE ('EventsManager_Log',TRUNCATEONLY);
END
END