Log File Growing

Question

Post reply

Log File Growing

PebbleRock978

SSC Veteran

Points: 231
More actions
October 8, 2020 at 7:56 am

#3793946

Hi,
We have a database that has 50GB of data, 99% of which is in one table. In this table there are 31,004,447 records, the developer is running a query to remove all records older than 15 days. This query should delete all the records as there are no records less than 15 days old.
When running this query the transaction log is reaching 126GB and the transaction fails as the disk is full. Once the query rolls back the transaction log has 99% free space
Database is in simple recovery. Only one index in the table.
What am I missing, surely this isn't right, the transaction log for a 'simple' delete shouldn't be 2.5 times the data its removing..
Thanks in advance

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

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

Cebisa SSC Enthusiast Points: 151 More actions · Answer 1

Perhaps you should make the database full recovery.

You can schedule regular transaction log backups to limit growth.

Is the transaction log and data file set to grow by a percentage? If it is, set it to an amount instead.

I would also consider pre growing the data files

Grant Fritchey SSC Guru Points: 398696 More actions · Answer 2

Instead of one giant transaction, break the delete up into smaller transactions. Simple or Full recovery, won't matter if the single transaction is bigger than you have storage space for.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

PebbleRock978 SSC Veteran Points: 231 More actions · Answer 3

Thanks both for the replies

The developer needed to get on with their work so we ended up truncating the table. I do have a copy of the database to look into this though.

I have never had the transaction log become 2.5 times the data it needed to delete (and still growing). Have I just been lucky and this is more common than I think?

The query was run with no other activity on the server.

Data files are pre-grown to 100gb, so 50% used. Log files set growth increments too, however not per our standards, this is 3rd party database so I do need to address those before we think about production.

This reply was modified 3 years, 6 months ago by PebbleRock978.

Michael L John One Orange Chip Points: 27038 More actions · Answer 4

PebbleRock978 wrote:

I have never had the transaction log become 2.5 times the data it needed to delete (and still growing). Have I just been lucky and this is more common than I think?

Simple recovery logs all transactions, same as in full recovery. What does occur is at each checkpoint, simple recovery truncates the unused portion of the transaction log. In your case, the transaction is large, and still being used. Therefore, no truncation can occur. As Grant said, breaking this into smaller transactions will likely help.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 5

PebbleRock978 wrote:

Hi,
We have a database that has 50GB of data, 99% of which is in one table. In this table there are 31,004,447 records, the developer is running a query to remove all records older than 15 days. This query should delete all the records as there are no records less than 15 days old.
When running this query the transaction log is reaching 126GB and the transaction fails as the disk is full. Once the query rolls back the transaction log has 99% free space
Database is in simple recovery. Only one index in the table.
What am I missing, surely this isn't right, the transaction log for a 'simple' delete shouldn't be 2.5 times the data its removing..
Thanks in advance

I've found that if you need to delete more than about 1/10th of a large table, it's much more efficient to simply copy the rows you want to keep to another table and then either rename the tables and add all the constraints to the new table (after dropping them from the original) OR using SELECT/INTO to copy the data you want to keep to another semi-permanent holding table, truncate the original, and then reinsert the rows from the holding table.

Either way takes a little forethought but it CAN be done in a Minimally Logged fashion (which can be incredibly fast) whereas DELETEs cannot be minimally logged and will log the deletes for every index (which is a part of the reason why the log file can grow much larger than the data).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)