Size of Filegroups and Files

In recent posts I looked at queries that show us data at the instance, the database, backups and now we will look at filegroups and the individual physical files.

Too many times disk space is not an issue, it is THE issue.  This query will show several key attributes of our database at the file level including:

  • List of filegroups for a database
  • List of files that are members of the filegroup
  • The amount of space ALLOCATED for the data file
  • The amount of space USED/FREE for the data file
  • The amount of remaining FREE SPACE for the drive that the file is on

Too many times we have a drive that is running out of storage and need to move tables/indexes to other files.  This query is a great starting point for identifying which files are in trouble.  It also gives us a birds eye view of other drives that may be candidates to move crowded tables to another drive.

Filegroups and Files
–drop table #sfs
–drop table #fixed_drives
–drop table #output_table
–drop table #databases
–drop table #dbf
–drop table #fg

— Save result set from showfilestats
CREATE TABLE #sfs(    fileid            tinyint
                    , filegroupid    tinyint
                    , totalextents    int
                    , usedextents    int
                    , dbfilename    sysname
                    , physfile        varchar(255) );
— Save result set from sys.database_files
CREATE TABLE #dbf(    [file_id]                int
                    , file_guid                uniqueidentifier
                    , [type]                tinyint
                    , type_desc                nvarchar(60)
                    , data_space_id            int
                    , [name]                sysname
                    , physical_name            nvarchar(260)
                    , [state]                tinyint
                    , state_desc            nvarchar(60)
                    , size                    int
                    , max_size                int
                    , growth                int
                    , is_media_ro            bit
                    , is_ro                    bit
                    , is_sparse                bit
                    , is_percent_growth        bit
                    , is_name_reserved        bit
                    , create_lsn            numeric(25, 0)
                    , drop_lsn                numeric(25, 0)
                    , read_only_lsn            numeric(25, 0)
                    , read_write_lsn        numeric(25, 0)
                    , diff_base_lsn            numeric(25, 0)
                    , diff_base_guid        uniqueidentifier
                    , diff_base_time        datetime
                    , redo_start_lsn        numeric(25, 0)
                    , redo_start_fork_guid    uniqueidentifier
                    , redo_target_lsn        numeric(25, 0)
                    , redo_target_fork_guid    uniqueidentifier
                    , back_lsn                numeric(25, 0) );
— Save result set from sys.filegroups select * from sys.filegroups
CREATE TABLE #fg(    [name]                sysname
                    , data_space_id        int
                    , [type]            char(2)
                    , type_desc            nvarchar(60)
                    , is_default        bit
                    , [filegroup_id]    uniqueidentifier
                    , log_filegroup_id    int
                    , is_read_only        bit );
— Populate #disk_free_space with data
CREATE TABLE #fixed_drives(DriveLetter    char(1) NOT NULL
                            , FreeMB    int NOT NULL );

INSERT INTO #fixed_drives
    EXEC master..xp_fixeddrives;
CREATE TABLE #output_table(DatabaseName    sysname
                            , FG_Name        sysname
                            , GB_Allocated    numeric(8, 2)
                            , GB_Used        numeric(8, 2)
                            , GB_Available    numeric(8, 2)
                            , DBFilename    sysname
                            , PhysicalFile    sysname
                            , Free_GB_on_Drive    numeric(8, 2) );
SELECT    name AS DBName
INTO    #databases
FROM    sys.databases
WHERE    database_id > 4
AND        state_desc = ‘ONLINE’;

DECLARE    @dbname    sysname;

SELECT    @dbname =(    SELECT    TOP (1) DBName FROM    #databases);
DELETE FROM #databases WHERE DBName = @dbname;


    — Get the file group data
    INSERT INTO #sfs            
        EXEC(‘USE ‘+ @dbname + ‘; DBCC showfilestats;’);
    INSERT INTO #dbf
        EXEC(‘USE ‘+ @dbname + ‘; SELECT * FROM sys.database_files;’);
        EXEC(‘USE ‘+ @dbname + ‘; SELECT * FROM sys.filegroups;’);
    — Wrap it up!
    INSERT INTO #output_table    (DatabaseName
                                , FG_Name
                                , GB_Allocated
                                , GB_Used
                                , GB_Available
                                , DBFilename
                                , PhysicalFile
                                , Free_GB_on_Drive )
        SELECT    @dbname AS DATABASE_NAME
                , AS [File Group Name]
                , CAST(((sfs.totalextents * 64.0) / 1024000.0) AS numeric(8, 2)) AS GB_Allocated
                , CAST(((sfs.usedextents * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Used
                , CAST((((sfs.totalextents sfs.usedextents) * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Available
                , sfs.dbfilename
                , sfs.physfile
                , CAST((fd.FreeMB / 1000.0) AS NUMERIC(8, 2)) AS Free_GB_on_Drive
        FROM        #sfs sfs
                INNER JOIN #dbf dbf
                    ON    dbf.[file_id] = sfs.fileid
                INNER JOIN #fg fg
                    ON fg.data_space_id = sfs.filegroupid
                INNER JOIN #fixed_drives fd
                    ON    fd.DriveLetter = SUBSTRING(sfs.physfile, 1, 1);
    SELECT    @dbname =(    SELECT    TOP (1) DBName FROM    #databases);
    IF @dbname IS NOT NULL
        DELETE FROM #databases WHERE DBName = @dbname;

SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
        , DatabaseName
        , FG_Name
        , GB_Allocated
        , GB_Used
        , GB_Available
        , DBFilename
        , PhysicalFile
        , Free_GB_on_Drive
FROM    #output_table
ORDER BY DatabaseName
        , FG_Name


I’ll add the code for this to the downloads on the right side of this post…

Hopefully you’ll find this query useful for getting a detailed picture of how your databases are physically laid out.  Without this info it’s impossible to figure out how to reconfigure / redistribute your data.

Stay curious; keep learning…

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>

phd ghostwriting website ca cheap papers editor sites uk help writing classic english literature cv cheap creative essay writing websites for college bibliography writer for hire ca popular admission paper ghostwriting services ca top personal statement editing site us is freedom free the last question meaning professional business plan proofreading service au popular research proposal editing services for phd best book review writers sites online top dissertation proposal ghostwriter for hire for school pay for best university essay on donald trump write me chemistry papers popular masters essay writer for hire gb popular article review writer sites usa paradise lost in frankenstein help with chemistry dissertation introduction esl persuasive essay writer websites gb university writing site usa pay to write leadership dissertation results do my esl rhetorical analysis essay on donald trump cheap dissertation chapter editing services cheap assignment editing services ca pepsi promotion strategy cheap analysis essay writing site ca top thesis writer for hire uk help writing professional analysis essay on founding fathers pay for my law paper type my esl rhetorical analysis essay on lincoln cheap cover letter editor sites for phd indian culture essay best expository essay editor website ca rhetorical analysis writer website us top home work editing websites for mba creative writing writers for hire uk custom college blog example best literature review proofreading website au how i learned to sweep by julia alvarez order cheap expository essay on shakespeare top phd essay editor site usa custom home work editing service for mba buy top college essay online custom thesis proposal ghostwriter websites essay about swimming best personal essay editor sites for masters custom dissertation results proofreading for hire uk esl persuasive essay editor service for phd professional school papers topic top analysis essay writer services for college cheap cover letter ghostwriters website usa pay to get human resource management application letter best essays proofreading sites au professional thesis proposal proofreading website usa help me write popular case study online professional paper writing services uk order literature biography best article ghostwriters service esl masters scholarship essay ideas political science essay ghostwriters services pay to get accounting letter best assignment writers for hire for masters top resume ghostwriting websites for phd best blog ghostwriter website ca esl case study ghostwriters website au professional cv proofreading services for school pay for my esl reflective essay on hacking top article review editing site for masters critical thinking editor service gb best descriptive essay proofreading site ca best dissertation introduction ghostwriters service us professional homework writer websites au custom course work writer sites for phd top school essay ghostwriting services for university types of computer technology esl dissertation hypothesis ghostwriter website usa best cheap essay writing service for masters esl university essay ghostwriting site for mba order custom expository essay on presidential elections definition essay editing site us cheap dissertation conclusion writers website uk professional research paper ghostwriters service us pay for scholarship essay on hillary clinton blog ghostwriting site best business plan editing websites for school best rhetorical analysis essay ghostwriters services us top article review editing service ca professional university essay ghostwriter for hire ca top annotated bibliography ghostwriter website ca popular creative essay editor services for college popular business plan writers site for college esl problem solving writing websites gb cheap business plan proofreading sites for masters bibliography proofreading service ca professional dissertation results ghostwriting websites for university cheap assignment ghostwriting services usa cheap dissertation conclusion ghostwriting website online order esl admission paper math essay editing service cheap ghostwriting services cheap essays editing site au best descriptive essay ghostwriters service for phd popular problem solving editing service for college help writing anthropology admission paper best thesis statement ghostwriting site online professional essays editor website uk top university critical analysis essay advice custom admission paper proofreading service usa custom masters essay writer services ca what role does religion play in beowulf esl critical analysis essay ghostwriter websites uk popular dissertation chapter ghostwriters for hire for phd buy government course work professional cheap essay writers website ca custom persuasive essay writer for hire for university admission editing sites us esl assignment ghostwriting services gb analysis essay proofreading services gb esl dissertation introduction editor site for mba cheap mba persuasive essay help esl article review writing site for university esl presentation editor site gb top dissertation results writers websites for college pay to write cheap creative essay on brexit professional article review ghostwriters service uk best phd essay writer for hire ca best bibliography writers websites for mba pay to write top creative essay on pokemon go best papers proofreading for hire for college journalism proofreading website pay to write shakespeare studies dissertation custom definition essay ghostwriters services for masters best application letter editing for hire for college custom papers ghostwriting sites for masters report editing cheap critical essay writers websites legalizing marijuana speech best scholarship essay ghostwriters websites popular literature review writer for hire for school esl dissertation methodology editing for hire online custom application letter writers for hire for mba u.s. history and government ghostwriter services top bibliography ghostwriters for hire usa cheap admission essay writing sites for school esl case study ghostwriters for hire us popular scholarship essay ghostwriting services us custom custom essay writers site gb top cv editing services uk top article review writing for hire for masters help me write professional cheap essay on hillary clinton professional dissertation proposal editor service for college order tourism biography professional expository essay proofreading for hire online japanese quince short story rhetorical devices in brutus speech dissertation writers service au esl personal statement writer service for mba esl university critical thinking ideas pay for my geometry blog post cheap paper writing websites online top course work editing service ca blog post ghostwriting top bibliography writer site ca professional course work writer service ca popular argumentative essay ghostwriting for hire online cheap essays ghostwriting services uk pay to write english as second language course work best rhetorical analysis essay ghostwriting site for phd esl critical analysis essay writing site for mba custom presentation proofreading website online best cheap essay proofreading sites ca a sound of thunder short story summary pay to write top literature review online pay to do culture speech custom cv ghostwriter for hire au top thesis statement writers site custom analysis essay ghostwriting site for college custom research proposal writers websites for college popular masters essay writer service for phd write me reflective essay on pokemon go great gatsby nick best term paper editor website gb top academic essay ghostwriting site ca custom critical thinking writing for hire online professional college essay editor service au custom blog post proofreading service ca esl bibliography ghostwriting site for school professional content proofreading services usa popular homework writers websites for university cheap ghostwriters site us write cheap essays cheap critical analysis essay editor websites for mba custom dissertation introduction proofreading website for phd pay for my finance paper top best essay ghostwriters sites gb esl masters assignment cheap thesis proposal writing for hire uk custom argumentative essay ghostwriter site essay on the most dangerous game professional argumentative essay ghostwriters services for college esl cheap essay editing services gb reagans inaugural address cheap reflective essay ghostwriter website for mba literature review writer services us buy popular expository essay on hillary best phd essay editor services uk best dissertation chapter ghostwriting site us professional business plan writers site us pay to do popular personal essay on civil war mr dolphus raymond professional university home work example cheap thesis proposal writer service au popular school home work assistance esl custom essay ghostwriter websites gb cheap persuasive essay editing website au the lottery sparknotes do my top papers popular course work ghostwriter site for university professional masters movie review assistance professional assignment writer site for masters write me cheap rhetorical analysis essay on lincoln pay for my top cheap essay on usa research paper editing sites uk homework writers services usa course work writers website ca popular thesis proposal ghostwriters services gb best blog post ghostwriters service best school essay ghostwriter website us self bailing boat article writers sites best literature review ghostwriting sites for masters esl book review ghostwriter site for university cheap application letter editor site for school best phd essay writers service ca best phd essay writers services us top thesis proofreading website for masters custom essay writer site au pay to do top presentation data analysis research paper best problem solving proofreading websites gb cheap reflective essay ghostwriters for hire for university best assignment editor services for college buy professional personal essay on lincoln book review writers services us top scholarship essay ghostwriter websites for school do my ecology curriculum vitae best thesis ghostwriters for hire for college accounting ghostwriters sites help writing custom school essay on donald trump dick symbols school writer website usa top problem solving proofreading service for college cheap curriculum vitae editing for hire ca top resume editing site for masters do my english argumentative essay best literature review editor websites us custom descriptive essay writing for hire for college top annotated bibliography writing site for mba critical analysis ghostwriters website ca esl essays ghostwriters websites for university esl paper proofreading for hire uk do my cheap creative essay on shakespeare rhetorical analysis essay proofreading services gb popular definition essay on shakespeare top paper ghostwriters websites gb esl masters essay writing services uk popular school essay writer sites usa top critical thinking proofreading service for phd esl application letter writer sites au best application letter ghostwriter sites us article review ghostwriters services ca cheap dissertation proposal ghostwriter services gb best presentation ghostwriter services for school health ghostwriting websites popular homework ghostwriting sites gb buy psychology dissertation hypothesis professional college cheap essay sample help writing top descriptive essay on trump cheap dissertation abstract ghostwriters services online write my best case study professional speech writer website for phd dissertation writer best presentation ghostwriters sites for mba top critical thinking ghostwriting service for school popular argumentative essay ghostwriting websites problem solving editor services ca cheap mba letter sample top book review writer websites for school do my life science case study cheap term paper writers service for phd pay for esl expository essay on hillary clinton custom scholarship essay ghostwriting for hire online cheap research paper writing websites for mba esl dissertation methodology ghostwriters services for phd popular cover letter writing sites for mba esl critical essay ghostwriting sites uk custom school letter assistance custom dissertation proposal proofreading services for university popular masters reflective essay samples professional writing sites us pay to do esl critical analysis essay on lincoln dissertation chapter topic best case study writing websites gb popular article review ghostwriter for hire for university custom custom essay proofreading for hire for phd pay to get world affairs bibliography custom analysis essay ghostwriter website online professional academic essay editor site ca esl admission paper ghostwriter service for mba top dissertation proposal ghostwriters for hire ca the importance of being earnest themes college writers sites online custom presentation ghostwriters site for school expository essay editor service uk write my ancient civilizations dissertation hypothesis help writing cheap rhetorical analysis essay on founding fathers esl critical analysis essay ghostwriter for hire for masters type my health article review top critical analysis essay writer services uk cheap research proposal editing website au top custom essay writing site ca best masters dissertation abstract help pay for my environmental studies movie review professional thesis proposal editing website usa thesis proposal proofreading website usa rhetoric of the image roland barthes best presentation proofreading website au dissertation results ghostwriting for hire uk custom persuasive essay ghostwriting service online order remedial math course work esl phd rhetorical analysis essay sample professional research paper ghostwriting websites online school essay ghostwriters service uk best home work writer websites ca best business plan writer for hire for mba popular reflective essay proofreading website for college type my best school essay on presidential elections best application letter proofreading services au pay for reflective essay on trump custom rhetorical analysis essay writers website for college top critical analysis essay editing websites custom cheap essay proofreading site for masters help me write physics thesis proposal best blog post editing websites us pay to do family and consumer science thesis proposal professional biography ghostwriters sites for university professional rhetorical analysis essay writer for hire gb professional phd thesis advice cheap college thesis statement help custom thesis proposal editing site uk pay to write admission essay on usa best critical essay writing services for mba a narrative essay example best descriptive essay writers sites au write cheap definition essay on presidential elections best thesis statement editor website for mba top school essay writers site for mba esl homework writing website au order psychology cover letter professional term paper proofreading for hire gb type my cheap essay on civil war order philosophy curriculum vitae popular application letter writers website gb masters editing websites gb help writing best best essay on shakespeare mending walls robert frost pay to do geometry content what sparked the french revolution popular critical thinking ghostwriting websites esl rhetorical analysis essay ghostwriters for hire for university pay for my esl argumentative essay on donald trump professional persuasive essay writer for hire au best biography editor website best thesis proposal editor websites ca cheap university biography professional analysis essay ghostwriting website for school pay for my poetry application letter pay for my religious studies biography cheap creative writing writing websites usa professional annotated bibliography writer websites for masters cheap personal essay editor websites for school pay for professional argumentative essay on lincoln custom research paper proofreading service au best critical essay ghostwriter website ca best thesis writing for hire au pay for my biology bibliography top literature review ghostwriting for hire custom report ghostwriting service gb top case study ghostwriters websites usa hester prynne character write professional cheap essay on hillary clinton top research paper proofreading site us popular personal essay writing for hire uk popular university essay writer site for school custom critical analysis essay writer sites gb best admission paper ghostwriter website for school do my technology dissertation chapter top personal statement editor site for phd my parents essay esl assignment writing for hire us custom dissertation conclusion proofreading website uk esl expository essay proofreading site us top blog post editing sites for masters professional mba essay editing services professional paper proofreading for hire best essay editor sites us buy esl essays men at forty poem cheap speech proofreading service for mba psychology essay ghostwriters websites esl expository essay writer service for university custom resume editing website uk quantitative statistics project ideas top dissertation chapter editing for hire for masters esl article review writers for hire online research paper writer service usa raymond carver neighbors popular reflective essay writers service for masters custom term paper ghostwriter for hire for mba top rhetorical analysis essay ghostwriters site uk cheap creative essay editing service for mba esl admission paper editing for hire us cheap bibliography writing websites for university top biography editing site for masters paper writer websites uk cheap home work proofreading website uk professional thesis proposal writing service uk esl academic essay editor site au pay to write earth science dissertation methodology best assignment proofreading websites for masters assignment ghostwriting websites au speeches about animal abuse the victims poem pay for best masters essay on hacking popular best essay ghostwriters service gb esl book review editing services professional home work writing websites esl course work editing site for university roderigo in othello custom persuasive essay ghostwriters websites ca custom phd business plan ideas esl thesis proposal ghostwriter website us do my earth science thesis statement cheap scholarship essay editor sites for mba best letter ghostwriter service ca top creative writing editing sites gb best creative essay ghostwriter sites for university professional thesis statement writers for hire usa annotated bibliography writer service write my biology annotated bibliography best essays ghostwriting site us popular research proposal writing website gb professional mba scholarship essay advice logic editing websites popular custom essay editing site gb best creative writing writers service us popular dissertation chapter writers website au professional critical essay writers for hire us professional homework editor site evil and omnipotence analysis essay ghostwriter site online cover letter editing sites presentation ghostwriter site us professional academic essay editing for hire us best dissertation hypothesis ghostwriting services critical analysis ghostwriters sites usa best course work writer site for college communication essay proofreading websites best scholarship essay ghostwriters website for mba do my education admission essay professional cover letter writers website gb masters writers website usa are light sabers possible popular term paper ghostwriting services for college professional rhetorical analysis essay editor websites au cheap critical essay writers website for school albert beveridge imperialism custom course work proofreading sites for mba argumentative ghostwriting website us thesis proposal proofreading websites us popular resume editing services for phd best term paper editor sites for school top business plan ghostwriters site diversity essay ideas people vs hall cheap article editor website ca top thesis ghostwriting sites write botany term paper help with government article write culture business plan popular creative writing editor for hire for university order best school essay top papers editor service ca help with my cheap cover letter online cheap writers site au literature review ghostwriting custom bibliography ghostwriting service for mba pay for my professional application letter online best admission paper ghostwriter website gb