SQL Server is very big and it is not possible to know everything in SQL Server but we all keep learning. Recently I was going over the best practices of transactions log and I come across following statement. The statement is about the largest supported DML operation, and I have a question about it.
The log size must be at least twice the size of largest supported DML operation (using uncompressed data volumes).
First of all I totally agree with this statement. However, here is my question – How do we measure the size of the largest supported DML operation?
I welcome all the opinion and suggestions. I will combine the list and will share that with all of you with due credit.
How I Would Measure the Largest Supported DML Operation
Here is my own answer to the question. You can’t guess this number from table size alone, so measure it. Pick the heaviest jobs you run: a big month-end update, a purge that deletes old rows, an index rebuild. Run each one on a test server with a realistic copy of the data.
While the job runs, a few tools show you the log it uses:
DBCC SQLPERF(LOGSPACE)shows how full each log file is.- In newer versions,
sys.dm_db_log_space_usagegives the same for the current database. sys.dm_tran_database_transactionsshows log bytes used and reserved by each open transaction.
The reserved part explains the ‘twice’ in the rule. SQL Server sets aside extra log space so the transaction can roll back if it has to, and a rollback writes log records too. Write down the biggest number you see and add a safety margin on top.
Also count the log that replication or availability groups need. If a secondary falls behind, the log cannot be cleared until the changes are sent, so a large batch can hold space longer than you expect.
If the number turns out huge, you have another choice besides a bigger disk. Break the job into smaller batches, and in the full recovery model take log backups between them so the space can be reused. Just note that an index rebuild in full recovery is fully logged, so plan room for it.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
3 Comments. Leave new
In the performance lab of my company we had done a DML operation (data correction script) over 1 billion records over sql server 2008. Thats one of the biggest DML operation i had done. Normally our data correction script will have reverse option but here we done without that to reduce overhead.
Would this be based on the size of the largest insert into a table(s) multiplied by the number of records during a given timeframe. Really interested to know the answer(s).
Delete all (*) operation on largest table of database would be the most largest DML operation. So The log size must be at least twice the size of largest table size.
Table physical size can be obtained by…
SELECT LEFT(OBJECT_NAME(id), 30) AS [Table],dpages AS PagesUsed,
CAST(CAST(reserved * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS ‘Allocated (in M)’,
CAST(CAST(dpages * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS ‘Used (in M)’,
CAST(CAST((reserved – dpages) * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS ‘Unused (in M)’,
rowcnt AS ‘Row Count (approx.)’
FROM sysindexes
WHERE indid IN (0, 1) AND OBJECT_NAME(id) NOT LIKE ‘sys%’ AND OBJECT_NAME(id) NOT LIKE ‘dt%’
AND reserved * 8192 >= 5000000
ORDER BY reserved DESC, LEFT(OBJECT_NAME(id), 30)