I have been monitoring Blocking issues on our SQL Server database. Very often the below query is running on tempdb and is blocked with LCK_M_S

What is this query used for and why does it run regularly?


<?query --
select @reservedpages = sum(a.total_pages),
    @usedpages = sum(a.used_pages),
    @pages = sum(
                -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
                When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
                When a.type <> 1 Then a.used_pages
                When p.index_id < 2 Then a.data_pages
                Else 0
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id

    /* unallocated space could not be negative */


This code is from a system stored procedure called sp_spaceused, which returns the amount of allocated space (pages) for database objects.

Look for home-brewn monitoring jobs or maintenance scripts that may be running a bit too frequently.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.