Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Getting data and log size of all databases

Hi guys.

The procedure bellow get the data and log size of all databases in current instance.

 

CREATE PROCEDURE dbo.uspDatabaseSize
AS
declare
@tbl table (Database_Id int, DataUsedMB Float)

insert into @tbl
exec sp_MSforeachdb
'select db_id(''?'') , (SUM(ps.reserved_page_count)*8)/1024 from ?.sys.dm_db_partition_stats ps';
 
WITH DataSize
AS
(
    select
        DB_ID(d.instance_name) Database_Id
        , d.instance_name Database_Name
        , d.cntr_value/1024 DataSizeMB
    from sys.dm_os_performance_counters d
    where d.object_name like '%databases%'
    and d.counter_name like '%Data File(s) Size (KB)%'
),
LogSize AS
(
    select
        DB_ID(p.instance_name) Database_Id
        , p.instance_name Database_Name
        , p.cntr_value/1024 LogSizeMB
    from sys.dm_os_performance_counters p
    where p.object_name like '%databases%'
    and p.counter_name like 'Log File(s) Size (KB)%'
),
LogUsedSize AS
(
    select
        DB_ID(p.instance_name) Database_Id
        , p.instance_name Database_Name
        , p.cntr_value/1024 LogUsedSizeMB
    from sys.dm_os_performance_counters p
    where p.object_name like '%databases%'
    and p.counter_name like 'Log File(s) Used Size (KB)%'
),
LogPercentUsed AS
(
    select
        DB_ID(p.instance_name) Database_Id
        , p.instance_name Database_Name
        , p.cntr_value LogUsedPercent
        , 100 - p.cntr_value LogFreePercent
    from sys.dm_os_performance_counters p
    where p.object_name like '%databases%'
    and p.counter_name like 'Percent Log Used%'
)
select
    ROW_NUMBER() over(order by DataSize.Database_Name) Seq
    , DataSize.Database_Id
    , DataSize.Database_Name
    , DataSize.DataSizeMB
    , t.DataUsedMB
    , (DataSize.DataSizeMB - t.DataUsedMB) DataFreeSizeMB
    , ROUND(((DataSize.DataSizeMB - t.DataUsedMB) / DataSize.DataSizeMB)*100, 2) DataFreePercent
    , LogSize.LogSizeMB
    , LogUsedSize.LogUsedSizeMB
    , (LogSize.LogSizeMB - LogUsedSize.LogUsedSizeMB) LogFreeSizeMB
    , LogPercentUsed.LogUsedPercent
    , LogPercentUsed.LogFreePercent
from DataSize
join LogSize on DataSize.Database_Id = LogSize.Database_Id
join LogUsedSize on  LogSize.Database_Id = LogUsedSize.Database_Id
join LogPercentUsed on LogSize.Database_Id = LogPercentUsed.Database_Id
join @tbl t on DataSize.Database_Id = t.Database_Id
go

Here is a small sample of procedure result:

 

sample space used

Comments

Posted by Jason Brimhall on 3 December 2011

have you considered using sys.dm_db_file_space_usage in your query for eachdb?

Posted by adeilsonrbrito on 6 December 2011

Hi Jason. I did not consider because sys.dm_db_file_space_usage is applicable only to the tempdb database

Posted by SQLRNNR on 7 December 2011

D'oh.  I knew that too.  Should not comment on no sleep ;)

Leave a Comment

Please register or log in to leave a comment.