Without using transaction log - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Without using transaction log

Author

Topic

inancgumus
Starting Member

40 Posts

Posted - 2004-07-26 : 07:01:32

Hello,

I have a table which storages ~300million of records, each row is 96byte. Table has indexes on the specific range columns. I wanna delete the records which were less than the date that i would be specified. Which are equal to 200million of records.

When I try to delete the records t-log grows in the size of gigabytes (20-30gb). In shortly, how can I delete the records without having this impact on the transaction-log? And what the other things I should notice?

Thank you a lot.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-26 : 07:31:46

I would suggest creating another table with the same layout of the table that you want to delete from (without the indexes for now), Then inserting into this new table just the rows that you want to keep, then dropping the original table and renaming this new table to the original tables name and create the indexes again.

This should hopefully cause less problems.

Duane.

inancgumus
Starting Member

40 Posts

Posted - 2004-07-26 : 07:39:34

OK, I'll give it a try... But I'm still suspicious that the t-log not gonna grow out.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-26 : 07:52:26

I would also suggest partitioning that table in future.

That's a damn big table 300 million rows.

Duane.

inancgumus
Starting Member

40 Posts

Posted - 2004-07-26 : 07:54:10

Yeah, this was the first step to the partitioning :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 09:48:34

quote:
Originally posted by inancgumus

When I try to delete the records t-log grows in the size of gigabytes (20-30gb).

Where's the DELETE statement?

Is it like DELETE FROM myTable99?

Like the whole thing?

Try TRUNCATE TABLE

Otherwise, do it in batches, wrapped in transactions, and commit them when they're done.....make sure you keep track though...

I'm always suspicious of such HUGE DELETEs

EDIT: Also, think about it backwards...move what you want into a new table, then drop the old

Brett

8-)

inancgumus
Starting Member

40 Posts

Posted - 2004-07-26 : 10:08:50

quote:
I wanna delete the records which were less than the date that i would be specified

Means, I wanna delete from a partial section of the table.

quote:
Otherwise, do it in batches, wrapped in transactions, and commit them when they're done.....make sure you keep track though...

Yeah, cool. I've an idea, what do you say? I'm gonna write a stored procedure which wraps the delete routine inside a transaction and commits it when done. And for each block (say 1000 rows/steps) I'm gonna backup the transaction log for the log not gonna grow out.

Thanks a lot.

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 10:14:30

Can you just set the database to recovery Model SIMPLE for the duration of this task to stop the logs growing?

Then:


SET ROWCOUNT 10000  -- DO 10,000 at a time
DECLARE @intRowCount int
SELECT @intRowCount = 1
WHILE @intRowCount > 0
BEGIN
    DELETE FROM MyTable WHERE SomeColumn < 'MyCutoffValue'
    SELECT @intRowCount = @@ROWCOUNT
END
SET ROWCOUNT 0

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 10:14:33

I can see the light from here....good luck...

post the code so we can take a look...

Brett

8-)

inancgumus
Starting Member

40 Posts

Posted - 2004-07-30 : 13:01:46

Hello,

I have been solved the problem with your aid. Now the transaction log grows and shrinks after the transaction ends. Here's the solution:


declare @range datetime
declare @rowCount int
declare @totalRows bigint

set @range = '20040601'

set @rowCount = 1
while @rowCount > 0
begin
    exec DeleteImpressionsBelowRange @range, @rowCount output
    set @totalRows = @totalRows + @rowCount
    print cast(@rowCount as nvarchar(64)) +' rows has been processed...'
    print 'until now, '+ cast(@totalRows as nvarchar(64)) +' rows has been processed...'
end

And here's the procedure:


create procedure dbo.DeleteImpressionsBelowRange (
	@dateRange datetime,
	@rowCount int output
) as

begin transaction

set rowcount 1000

delete from Log_Impressions
where ImpressionDate <= @dateRange

set @rowCount = @@rowcount

if (@@error <> 0)
	rollback tran
else
	commit tran

backup log Server_080704 with no_log
dbcc shrinkfile ('Server_log')

set rowcount 0
go

Thanks a lot, any other recommendations or comments would be more okay :)

Kristen
Test

22859 Posts

Posted - 2004-07-30 : 13:12:44

I don't think you need to do
dbcc shrinkfile ('Server_log')
each iteration - it probably takes a while to do this, and the main task SHOULD reuse the log space - so once at the end should be enough.

Logging of all other transactions will be lost using this technique, so you ought to do a FULL backup immediately after completing this task to avoid losing any data.

Kristen

inancgumus
Starting Member

40 Posts

Posted - 2004-07-30 : 13:21:30

quote:
Originally posted by Kristen

I don't think you need to do
dbcc shrinkfile ('Server_log')
each iteration - it probably takes a while to do this, and the main task SHOULD reuse the log space - so once at the end should be enough.

Logging of all other transactions will be lost using this technique, so you ought to do a FULL backup immediately after completing this task to avoid losing any data.

Kristen

There is more than ~100millions of rows, and this operation will take a while. After completing the operation whether I would do a FULL backup or wouldn't, what will be changed? The data would be lost again, wouldnt it?

Kristen
Test

22859 Posts

Posted - 2004-07-30 : 13:30:50

You can't restore a transaction log after a
backup log Server_080704 with no_log
so in order to able to restore TRANSACTION LOGs then you need to do a full backup - you will then be able to recover to a point-in-time using Full Backup + Subsequent Transaction log backups.
BOL:

quote:

After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.

However, if you are not making transaction log backups (i.e. database recovery model is set to SIMPLE) then this isn't necessary

Kristen

inancgumus
Starting Member

40 Posts

Posted - 2004-07-30 : 13:43:37

When I execute,

backup log Server_080704 with no_log

the command just deletes the logs which are 'transaction committed' state? And other transactions which are still continuing and meanwhile if this command had been executed, the other ones would be erased or stored in the db but in log?

Kristen
Test

22859 Posts

Posted - 2004-07-30 : 14:01:12

Its only to do with if you need to be able to recover to point-in-time after you have done "backup log Server_080704 with no_log"; the data is sure to be committed to the database correctly.

So you do:

02:00 Full backup
every 10 minutes backup transaction log
09:45 backup log Server_080704 with no_log
every 10 minutes backup transaction log continues ...

17:26 you want to recover - but you can only recover up to 09:45, not upto 17:26.

To recover upto 17:26 you must make a full backup at 09:46, after the "backup log Server_080704 with no_log" command.

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-30 : 14:19:40

I would suggest doing the deletes in batches and also transaction log backups every 5-15 minutes to keep up with the massive deletes.

Tara

inancgumus
Starting Member

40 Posts

Posted - 2004-07-30 : 14:27:59

quote:
Originally posted by tduggan

I would suggest doing the deletes in batches

I am doing the deletes within a stored procedure, do you mean this; with 'in batches'?

quote:
and also transaction log backups every 5-15 minutes to keep up with the massive deletes.

The last command in the sproc gets backup log for the deleted (1000) rows. And Kristen suggested that doing full backups in a day and tlog backups every 10mins.

So, I am suspicious about whether my other transactions to the same database would be effected because of this operation. I have to be sure about the other ones had been committed; permanently stored. In a failure I can use the backup strategy that you have offered.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-30 : 14:37:01

Here's what we do for situations like this:

We create a job that deletes the rows in batches. We only run the job in the middle of the night. And we only delete like a couple hundred thousand rows each night. The job runs each night until the rows have been deleted, then we delete the job. The batches are done with 10,000 rows. We also often put a pause in between each loop with a WAITFOR DELAY command.

Here's the code I recently used for a large delete:



CREATE PROC isp_Purge_OrphanedTrailer
AS

SET NOCOUNT ON

DECLARE @RowCnt INT

SELECT TOP 100000 ID AS TrailerID
INTO #Temp
FROM Trailer
WHERE DP_LOAD_ID NOT IN (SELECT ID FROM DP_LOAD)

SET @RowCnt = @@ROWCOUNT

SET ROWCOUNT 5000

WHILE @RowCnt > 0
BEGIN

	DELETE t1
	FROM Trailer t1
	INNER JOIN #Temp t2
	ON t1.ID = t2.TrailerID

	SET @RowCnt = @@ROWCOUNT

	WAITFOR DELAY '00:00:30'

END

SET ROWCOUNT 0

DROP TABLE #Temp

RETURN 0


GO

So this one deleted rows in 5000 batches until it had deleted 100000 rows. It paused for 30 seconds in between each batch. This sproc ran each night for about 2 weeks.

We backup the transaction log every 15 minutes, and while the delete was running, the LDF file remained a reasonable size.

As long as you don't run BACKUP LOG WITH NO_LOG, then you'll be able to restore to a point in time.

Tara

inancgumus
Starting Member

40 Posts

Posted - 2004-07-30 : 14:45:03

Cool, your approach seems logical to me and beside you have the practial experiment about the issue. I will give a try.

quote:
We backup the transaction log every 15 minutes

I think you do not use 'with no_log' option don't you?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-30 : 14:46:18

No. We never (except when we have to like when you run out of disk space) run that as you lose the ability to restore to a point in time.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-01 : 14:43:18

If you ever use the "with no_log" option, you need to immediately make a full backup and restart your transaction log backups all over again. Otherwise, like Tara said, they are pretty much useless from that point on for point-in-time restores.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources