Live Insert Counts Without Disturbing Running Transaction

There are times long running insert transactions are annoying and we don’t get direct details of running transaction like how many rows inserted and how many are pending.

Following query gets estimated rows inserted for the specific table mentioned.

SELECT 
    TableName = t.NAME,
    TableSchema = s.Name,
    RowCounts = p.rows
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.is_ms_shipped = 0 and t.name=’Table Name’ — change table name
GROUP BY
    t.NAME, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

 

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (10 votes, average: 5.00 out of 5)
Loading...

2 thoughts on “Live Insert Counts Without Disturbing Running Transaction

Add Comment