SP to find the database usage

,

Create this SP and then start using it

Syntax: EXEC proc_DBUsage <dbname>

CREATE PROCEDURE proc_DBUsage
 @dbname varchar(50)
AS

if(@dbname not in(select name from master..sysdatabases))
    print 'The database '''+@dbname+''' doesnot exist on the server '''+@@servername+''''

else
 begin
    SET NOCOUNT ON
    
    create table model..DBUsage
    (
        FileID int,
        [FileGroup] int,
        TotalExtents int,
        UsedExtents int,
        [FileName] varchar(50),
        PhyFileName varchar(250)
    )
    
    create table model..LogUsage
    (
        DBName varchar(50),
        [LogSize(MB)] numeric(7,2),
        [LogUsage(%)] numeric(5,3),
        Status bit
    )
    
    declare @cmd1 nvarchar(50)
    set @cmd1='use '+@dbname+'; dbcc showfilestats with no_infomsgs'
    
    declare @cmd2 nvarchar(50)
    set @cmd2='dbcc sqlperf(logspace) with no_infomsgs'
    
    insert into model..DBUsage
    exec sp_executesql @cmd1
    
    insert into model..LogUsage
    exec sp_executesql @cmd2
    
    declare @a int, @b int
    select @a=sum(TotalExtents) from model..DBUsage
    select @b=sum(UsedExtents) from model..DBUsage
    insert into model..DBUsage(TotalExtents,UsedExtents,PhyFileName)
    values(@a,@b,'Total')
    
    select 
        FileID,
        [FileGroup],
        [FileName],
        PhyFileName,
        cast(TotalExtents*64.0/1024 as numeric(7,3)) as [FileSize(MB)],
        cast(UsedExtents*64.0/1024 as numeric(7,3)) as [Usage(MB)],
        cast(UsedExtents*100.0/TotalExtents as numeric(5,3)) as [Usage(%)]
    from model..DBUsage
    
    select
        DBName,
        [LogSize(MB)],
        cast([LogSize(MB)]*[LogUsage(%)]/100.0 as numeric(7,3)) as [LogUsage(MB)],
        [LogUsage(%)]
    from model..LogUsage
    where DBName=@dbname
    
    drop table model..DBUsage
    drop table model..LogUsage
    
    SET NOCOUNT ON
 end

GO

Rate

2.4 (5)

Share

Share

Rate

2.4 (5)