Geek Answers Handbook

How much will the transaction log grow when deleting a given number of rows

I have a table in sql server that has the following data from exec sp_spaceused

rows        reserved           data               index_size         unused
182515      16721960 KB        16563768 KB        14920 KB           143272 KB

I want to delete rows 38693. Can this be correctly calculated in a rough estimate of how much the transaction log can increase at this deletion?

(38693/182515)*16721960 KB no more? 3.544 GB

Can anyone help me out.

+6
source share
1 answer

Your rating (16 GB data / 182 515 lines) X 38 693 lines looks like a reasonable rating.

Another estimate is to take the average row size and multiply by this the number of rows you want to delete.

, . :

select avg_record_size_in_bytes * 38693 / 1024.0 as [MB]
from sys.dm_db_index_physical_stats(db_id(), object_id('MyTable'), 1, 0, 'Sampled')

, .

0

More articles:


All Articles