Post reply

Transaction log size and # records in table (via properties window)

  • April 4, 2012 at 7:17 am

    #258717

    Hi guys,

    I am deleting approx 3.7M rows from a 476M row table as a single DELETE command. This is the only script running against this database.

    I was monitoring progress by right-cliking on the table in MSSM, choosing 'Properties' and checking the number of rows which was gradually reducing. For the last two hours is has not moved.

    my DBA has checked the transaction log file size and it is being increased; I have checked the Activity monitor in MSSM and the query is suffering a PAGEIOLATCH wait but the wait times are fluctuating.

    we are assuming that the records are still being deleted (the delete takes about 4.5 hours) but I was wondering why the record count is no longer being updated?

  • April 4, 2012 at 7:24 am

    #1468824

    Assuming that the record count is pulled from sys.partitions, it is only supposed to be transactionally consistent, not necessarily correct during a transaction.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 4, 2012 at 12:43 pm

    #1469101

    For future reference here is what I use to track long running DML statements that affect a lot of rows. You can use it instead of the GUI steps you are using. Just update the schema and object names as needed:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT OBJECT_NAME(p.object_id) AS ObjectName,

    p.object_id,

    SUM(p.rows) AS rows

    FROM sys.partitions p

    WHERE OBJECT_SCHEMA_NAME(p.object_id) = 'schema_name'

    AND OBJECT_NAME(p.object_id) = 'object_name'

    AND p.index_id IN (0, 1)

    GROUP BY p.object_id

    ORDER BY rows DESC;

    As Gail pointed out, the numbers are not transactionally consistent, i.e. not guaranteed to be up to date at the time they are selected, but for the intended purpose that's a good thing because the numbers cannot be transactionally consistent until the DML statement is committed. This allows us to watch the intermediate row count change during the transaction.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 4, 2012 at 2:17 pm

    #1469162

    Thanks for the replies guys, I will take them on board.

    I am not so concerned about the count being accurate, I understand why it is out, it was more about why it had stopped updating.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply