tsunami

log in
history

SQL2005: disk usage

Luke Breuer
2010-09-20 19:59 UTC

Overview
select  f.file_id, 
        type_desc, 
        name,
        physical_name,
        used_gb = used_pages / 128 / $1024,
        allocated_gb = size / 128 / $1024,
        max_gb = max_size / 128 / $1024,
        growth_gb = growth / 128 / $1024
from    sys.master_files f
left join (
    select  file_id = 1,
            used_pages = sum(in_row_used_page_count)
    from    sys.dm_db_partition_stats
) s on s.file_id = f.file_id
where database_id = db_id()
Details
with index details
with table_space_usage as (
    select  schema_name = s.name,
            table_name = o.name,
            index_name = i.name,
            used = p.used_page_count * 8 / 1024,
            reserved = p.reserved_page_count * 8 / 1024,
            index_rows = p.row_count,
            table_rows = case when i.index_id in (0, 1) then p.row_count else 0 end
    from sys.dm_db_partition_stats p
    inner join sys.objects o on o.object_id = p.object_id
    inner join sys.schemas s on s.schema_id = o.schema_id
    left outer join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
    where   o.type_desc = 'user_table'
        and o.is_ms_shipped = 0
)
select  t.schema_name,
        t.table_name,
        index_name = coalesce(t.index_name, 'heap'),
        used_mb = sum(t.used),
        reserved_mb = sum(t.reserved),
        rows = case grouping(t.index_name) 
            when 0 then sum(t.index_rows) 
            else sum(t.table_rows) 
        end
from    table_space_usage t
group by t.schema_name,
        t.table_name,
        t.index_name
with rollup
--having sum(t.reserved) > 100
order by grouping(t.schema_name),
        t.schema_name,
        grouping(t.table_name),
        t.table_name,
        grouping(t.index_name),
        t.index_name
one row per table
with table_space_usage as (
    select  schema_name = s.name,
            table_name = o.name,
            index_name = i.name,
            used = p.used_page_count * 8 / 1024,
            reserved = p.reserved_page_count * 8 / 1024,
            index_rows = p.row_count,
            table_rows = case when i.index_id in (0, 1) then p.row_count else 0 end
    from sys.dm_db_partition_stats p
    inner join sys.objects o on o.object_id = p.object_id
    inner join sys.schemas s on s.schema_id = o.schema_id
    left outer join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
    where   o.type_desc = 'user_table'
        and o.is_ms_shipped = 0
)
select  table_name,
        row_count = sum(table_rows),
        reserved_mb = sum(reserved),
        data_mb = sum(case when table_rows != 0 then used end),
        index_mb = sum(case when table_rows = 0 then used end),
        unused_mb = sum(reserved) - sum(used)
from    table_space_usage
group by table_name
order by sum(used) desc
Advanced Stats
select  *
from    sys.dm_io_virtual_file_stats(db_id(), 1)
Altering files
-- this will be removed from a later SQL Server version; it breaks the log chain
backup log hb1_database with truncate_only

-- truncateonly and notruncate do not apply for transaction log files
dbcc shrinkfile ('hb1_database_log')

-- do not reorganize pages on the disk, only truncate excess space at the end
dbcc shrinkfile ('hb1_database_data', truncateonly)

-- if the maxsize is changed to be less than the filegrowth,
--   or the filegrowth changed to be greater than maxsize,
--   the two statements need to be run separately
alter database hb1_database modify file 
(
    name = hb1_database_log, 
    maxsize = 10GB, 
    filegrowth = 1GB
)