Best way to find how much of my partition filegroup contains data?

  • I have partitions that I have filled with data. I am not trying to figure out exactly how much data the partitions contain, and therefore I will be able to see if any of them are close to hitting their autogrow conditions. If I were looking at a single unpartitioned table, then I could maybe look at the table properties to determine data and index sizes, and compare that to the size of the mdf file size, but for partitions, then I am not sure how I would query this information out. Does someone have any pointers on how this information could be queried out of the system?

  • Are you putting partitions in their own filegroup? If so, wouldn't you query the data size in the filegroup v filegroup size?

  • This maybe?

    SELECT

    b.groupname AS 'File Group',

    Name,

    [Filename],

    CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))

    [Currently Allocated Space (MB)],

    CONVERT (Decimal(15,2),

    ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))

    AS [Space Used (MB)],

    CONVERT (Decimal(15,2),

    ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))

    AS [Available Space (MB)]

    FROM dbo.sysfiles a (NOLOCK)

    JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid

    ORDER BY b.groupname

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • Thanks, was looking for something along these lines.

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

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