Is it possible to delete records without logging them to the log file 1

[jimoo]

We have a SQL Table with over 155 million rows covering about 10 years. We want to delete out all by the current 4 years worth of data but are concerned it will overfill the log. Is there a way to delete these records without logging them to the log file and/or do you have another suggested solution?


Jim

 

[fredericofonseca]

its best to insert the last 4 years records into a new table, truncate the old one and then insert back to original table than to delete (and this could be done with SSIS bulk load which will do minimal logging.)

another possible option is to partition the table, truncate the old partition and them merge all partitions into a single one again if required. Just not sure of performance of partitioning the table itself.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gk53]

You can use something like that

declare @Records as decimal(19,4), @BatchSize as int, @BatchCount as int, @CurrentBatch as int, @End_dt as date

set @End_dt = '1/4/2010'

set @BatchSize = 1000000

set @Records = (select count(*) from yourTable where dateColumn < @End_dt)

select @BatchCount = ceiling(@records/@BatchSize)

set @CurrentBatch = 1

while @CurrentBatch <= @BatchCount
begin
       begin transaction
       delete top (@BatchSize) from yourTable
		       where dateColumn < @End_dt
       commit transaction
       
       set @CurrentBatch = @CurrentBatch + 1
end

it will create log record only on commit transaction, one per transaction

 

[Olaf Doschke]

It depends much more on your recovery model, what really is logged and what not. Bulk operations, as Frederico suggested, can be minimally logged, if you temporarily change to the bulk-logged recovery model.
See

http://msdn.microsoft.com/en-US/library/ms190925.aspx#MinimallyLogged

The log contains different entires, see

http://en.wikipedia.org/wiki/Transaction_log

A transaction entry is taking minimal space, what you would be concerned with is the Before and After images, SQL Server stores with a change log. Doing a number of smaller batches will create more change log entries, but in the end cost more space than a single change log entry, it's just less likely to fail overall.

If you want to get a minimal log file use DBCCSHRINK afterwards in any case.

Bye, Olaf.

 

[Olaf Doschke]

DBCC SHRINKFILE is the command, sorry.

Bye, Olaf.

 

[Olaf Doschke]

One more thing: I'm not really an expert on the SQL Server Transaction log inner workings. AFAIK a change entry only is needed for updates, not for deletes. From the perspective of the recoverabilty any operations done on records before they were deleted have been inserts and updates, the deletion change does not need an image, because even if you would need to recover a database to a timestamp before the deletion what is needed to reproduce the deleted records is already in the log file in the form of entries for their creation and changes, which are replayed in case of a restore. Apart of loggin the operation to delete what records, nothing needs to be stored in the transaction log.

In the end it might not change the log file size at all besides some transaction entries, if you delete data. You would only have to be concerned if updating data. Also, if you have features like CDC activated.

Bye, Olaf.

 

[gk53]

in most cases you do not admin have access which required to change recovery model and run system shrinkfile command...
shrink file should happens nightly as part of maintenance plan.
when you deleting 155 million rows usually it create huge transaction log and runs very slow.
By using script from my previous post it creates only one record for 1000,000 deleted rows so 155 record which never create a problem and it runs pretty fast

 

[Olaf Doschke]

All that is true, yet the 155 records would have about the same impact on log file growth, anyway, if that size is getting too big you typically will already have done the job partially.
Such an operation is worth to be done by a DBA, permissions shouldn't be in the way of working more ideally.

Bye, Olaf.