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 (http://blog.SQLAuthority.com)

4 thoughts on “SQL SERVER – Find Largest Supported DML Operation – Question to You

  1. 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.

    Like

  2. 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).

    Like

  3. 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)

    Like

  4. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s