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 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.
Reference: Pinal Dave (https://blog.sqlauthority.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)