Recuperando o tamanho dos dados e log de todos os bancos

A sp abaixo recupera o tamanho dos dados e log de todos os bancos da instância.  A sp inclui colunas informando a percentagem livre, MB utilizados, etc. Vale conferir (gastei uns 30 minutos para elaborá-la). Bom proveito.

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

Veja uma amostra do resultado da SP: