Simple recovery?

Question

Simple recovery?

FreeHansje

SSChampion

Points: 11751
More actions
May 3, 2004 at 7:18 am

#87136

Awright,
I have this database on simple recorvery, nothing is loged(I think) so the logfile will not grow out of proportions(I think).
I am starting a cleanupoperation: delete all records before a certain date. Total rows in database, about 80 million. Records to be deleted, about 30 million. I am monitoring and see to my amazement the logfile is growing beyond reasonable proportions. It grows to up 60 Gb and the process stops because of to little diskspace.
I can solve the problem by deleting shorter intervals, but I wonder. I thought simple recoverymodel would minimize logfile usage. I seem to be wrong there. Can someone explain where I err in my assumption? Is there an in-depth explaination of what is happening in the logfile during a batchoperation as described, in the several recoverymodels?
TIA,
Greetz,
Hans Brouwer

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

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

Angela But Hall of Fame Points: 3848 More actions · Answer 1

Hans,

AFAIK (and I may be wrong), Simple Recovery will minimise the amount of logging that occurs, but will still log something. It needs to log some transactions so that if a process is interrupted or killed/cancelled it can perform a Rollback operation.

I think that because of the huge amount of data you are removing your transaction logs are growing to keep record of this. Try deleting the records in smaller batches and running:

Backup Log yourdatabasename

With Truncate_only

Ii between batches and see if this helps resolve your problem.

Cheers,

Angela

mssql_rules SSCrazy Points: 2783 More actions · Answer 2

Simple recovery model still has to log the entire transaction, just like full recovery model. The only difference is that with the simple recovery once the transaction is committed, it may be purged from the log freeing the log space. For a full recovery the log is purged only when the log backups are done. So the log should be able to hold atleast a few large transactions even in the simple recovery model. When you would like to delete large data, the preferable way is to break the transaction into smaller ones. This is recommended not only for log space usage but also from the recovery point of view (imagine a server restart in the middle of a 2 hour tx and waiting for that to rollback.. not good).

FreeHansje SSChampion Points: 11751 More actions · Answer 3

Tnx for the info. I'll have to do this in smaller bathces then.

Greetz,
Hans Brouwer

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

You may want to consider:

Changing the Recovery Model to Bulk-Logged,
Use Select Into to move the data you want to keep to another new table,
Truncate the original table, then drop the table.
Rename the new table to the original table.
Change Recovery Model back to Simple.

When in Bulk-Logged, the data from the Select Into transaction will not be logged (only a "note" that a bulk transaction was done). Truncating data from a table is never logged, nor are table renames. All these operations would write very little to the log.

Mark

FreeHansje SSChampion Points: 11751 More actions · Answer 5

Tnx, this sounds promising and I am going to give it a try. Cuurently I am struggling with another problem, related to this 1: see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=114219

If you have an answer to that 1, I owe you.

Greetz,
Hans Brouwer