Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Display SQL Server storage filegroups script

SQL Server Tips by Donald Burleson


This is one of the many SQL Server Windows scripts to display storage filegroups from the book "High Performance SQL Server DBA".

After checking the global storage picture of the SQL Server, one can then drill down to obtain more detail on filegroups , files and databases.  The up_bn_storage_filegroups   procedure will give some good information on the filegroup front:

 
IF OBJECT_ID('up_bn_storage_filegroups') IS NOT NULL
BEGIN
    DROP PROCEDURE up_bn_storage_filegroups
    IF OBJECT_ID('up_bn_storage_filegroups') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE up_bn_storage_filegroups >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE up_bn_storage_filegroups >>>'
END
go
CREATE PROCEDURE up_bn_storage_filegroups
AS
    declare @db_name sysname,
            @testamt float

    set nocount on
    set ARITHABORT off
    set ARITHIGNORE on
    set ANSI_WARNINGS off

    create table #filegroup_info
        (dbname sysname NULL,
         groupid int NULL,
         groupname sysname NULL,
         total_space float NULL,
         max_file_growth_size float NULL,
         file_count tinyint NULL)

    create table #space_info_objects
        (dbname sysname NULL,
         groupid int NULL,
         reserved_pages_tables float NULL,
         reserved_pages_indexes float NULL)

    create table #logspace
        (database_name sysname,
        log_space decimal(15,2),
        pct_used decimal(15,2) NULL,
        status int)

    declare db_cursor cursor for
            select
                name
            from
                master..sysdatabases
            where
                databasepropertyex(name,'status') not in
                ('SUSPECT', 'OFFLINE',
                 'RESTORING', 'RECOVERING') and
                has_dbaccess(name) = 1
See code depot for full script
        insert
            into #filegroup_info
            (dbname,
             groupid,
             groupname,
             total_space,
             max_file_growth_size,
             file_count)
        exec
            ('use [' + @db_name + ']
            select
                db_name = db_name(),
                a.groupid,
                a.groupname,
                sum(b.size),
                max(b.growth),
                count(b.fileid)
            from
                sysfilegroups a,
                sysfiles b
            where
                a.groupid =* b.groupid
            group by
                a.groupid,a.groupname')

        -- Get object space totsls
        insert
            into #space_info_objects
            (dbname,
             groupid,
             reserved_pages_tables,
             reserved_pages_indexes)
        exec
            ('use [' + @db_name + ']
            select
                db_name = db_name(),
                groupid,
                table_pages =
                isnull((select
                            sum(reserved)
                        from
                            sysindexes a
                        where
                            a.indid in (0,1,255) and
                            c.groupid = a.groupid),0),
                index_pages =
                isnull((select
                            sum(reserved)
                        from
                            sysindexes b
                        where
                            b.indid >1 and b.indid < 255 and
                            c.groupid = b.groupid),0)
                from
                    sysindexes c
                group by
                    groupid')

        fetch
            db_cursor
        into
            @db_name
    end

    insert into
        #logspace (database_name,log_space,pct_used,status)
    select 
        db_name = a.instance_name,
        log_size_mb = convert(decimal(15,2),a.cntr_value) / 1024,
        log_pct_used = 100 * convert(decimal(15,2),b.cntr_value) / a.cntr_value,
        status = 0
    from
        master..sysperfinfo a,
        master..sysperfinfo b
    where  
        a.object_name = 'SQLServer:Databases' and
        b.object_name = 'SQLServer:Databases' and
        a.counter_name = 'Log File(s) Size (KB)' and
        b.counter_name = 'Log File(s) Used Size (KB)' and
        a.instance_name <> '_Total' and
        b.instance_name <> '_Total' and
        a.instance_name = b.instance_name


    select
        a.dbname,
        filegroupid = isnull(a.groupid,0),
        file_group = case groupname
            when NULL then 'LOG'
            else groupname
            end,
        can_grow = case max_file_growth_size
            when 0 then 'NO'
            else 'YES'
            end,
        file_count,
        size_in_mb = convert(decimal(17,2),((total_space * 8) / 1024)),
        table_reserved_mb = case b.reserved_pages_tables
            when NULL then 0
            else convert(decimal(17,2),((b.reserved_pages_tables * 8) / 1024))
            end -
            case b.reserved_pages_indexes
            when NULL then 0
            else convert(decimal(17,2),((b.reserved_pages_indexes * 8) / 1024))
            end,
        index_reserved_mb = case b.reserved_pages_indexes
            when NULL then 0
            else convert(decimal(17,2),((b.reserved_pages_indexes * 8) / 1024))
            end,
        /* don't subtract indexes for total free because they are
        included in total table space */
        free_space_mb = case groupname
            when NULL then convert(decimal(17,2),((100 - c.pct_used) /100) *
            convert(decimal(17,2),((total_space * 8) / 1024)))
            else
            convert(decimal(17,2),((total_space * 8) / 1024)) -
            case b.reserved_pages_tables
            when NULL then 0
            else convert(decimal(17,2),((b.reserved_pages_tables * 8) / 1024))
            end
            end,
         free_space_pct = case groupname
            when NULL then 100 - c.pct_used
            else
            convert(decimal(5,2),100 *
            (convert(decimal(17,2),((total_space * 8) / 1024)) -
            case b.reserved_pages_tables
            when NULL then 0
            else convert(decimal(17,2),((b.reserved_pages_tables * 8) / 1024))
            end) / (convert(decimal(17,2),((total_space * 8) / 1024))))
           end
    from  
        #filegroup_info a,
        #space_info_objects b,
        #logspace c
    where 
See code depot for full script

    deallocate db_cursor
    drop table #filegroup_info
    drop table #space_info_objects
go
IF OBJECT_ID('up_bn_storage_filegroups') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE up_bn_storage_filegroups >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE up_bn_storage_filegroups >>>'
go

Figure 5.2 is a representation of the filegroup demographic information. This is one of the many SQL Server Windows scripts to display storage filegroups from the book "High Performance SQL Server DBA".


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational