Post reply

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

  • October 7, 2015 at 9:39 am

    #321175

    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?

  • October 7, 2015 at 10:29 am

    #1832062

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

  • October 9, 2015 at 9:52 am

    #1832586

    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]

  • October 13, 2015 at 1:31 am

    #1832905

    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