SQL Server 2005 script to display disk space usage

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 support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

SQL Server 2005 script to display disk space usage

Oracle Database Tips by Donald Burleson

This is one of the many SQL Server 2005 Windows scripts to display disk space utilization from the book "High Performance SQL Server DBA".

In the case of the up_bn_storage_overview procedure, there is one query change that must be made for it to work on SQL Server 2005. Microsoft has deprecated the sysindexes table in favor of a number of new object/space tables that support new features such as partitioning. This being the case, the database space query in the above procedure will need to be replaced with the following code snippet:

/*
**********************************************************************
* Collect database space
***********************************************************************
*/
    insert
        into #dbspace (database_name,total_space,used_db_space,total_log_space)
        EXEC ('use [' + @db_name + ']
        select
            db_name = db_name(),
            total_space =
            (select
            See code depot for full script
                (select low from master.dbo.spt_values where number = 1 and type =
                 ''E'')))
             from dbo.sysfiles),
             total_db_used =
             (select
                (sum(convert(float, case type when 2 then used_pages else

                 data_pages end)) *
                (select
                    low
                 from
                    master..spt_values
                 where number = 1 and
                        type = ''e''))/1024/1024
               from
                sys.allocation_units),
               total_log_space =
               (select
                sum(convert(decimal(35,2),size)) / convert( float, (1048576 /
                (select
                    low
                from
                    master.dbo.spt_values
                where
                    number = 1 and
                    type = ''E'')))
                from
                    dbo.sysfiles
                where (status & 0x40)=0x40)' )

-- end collection of database space

This is one of the many SQL Server 2005 Windows scripts to display disk space utilization from the book "High Performance SQL Server DBA".

��