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

Question

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

aaron.reese

SSChampion

Points: 13498
More actions
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?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

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

aaron.reese SSChampion Points: 13498 More actions · Answer 3

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.