SQL SERVER – Find Largest Supported DML Operation – Question to You

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)

Best Practices
Previous Post
SQL SERVER – Shrinking Database NDF and MDF Files – Readers’ Opinion
Next Post
SQL SERVER – Data Pages in Buffer Pool – Data Stored in Memory Cache

Related Posts

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.

    Reply
  • 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).

    Reply
  • Aasim Abdullah
    June 17, 2010 3:24 pm

    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)

    Reply

Leave a Reply

Menu
Exit mobile version