Technical Article

Monitor database size with dbcc showfilestats

,

Monitor the databases except master, tempdb,model and save the evolution in a table msdb.dbo.tb_ocupacaoBD.

/***********************************************************Desc.: Monitor database size with dbcc showfilestats.
Autor: Landry D. Salles Filho
***********************************************************/
--Create table tb_ocupacaoBD in MSDB 
--drop table msdb.dbo.tb_ocupacaoBD
create table msdb.dbo.tb_ocupacaoBD 
(data datetime default getdate(),nomebd varchar(100),tamanho_mb bigint,espacoutil_mb bigint,
 espacolivre_mb as (tamanho_mb - espacoutil_mb))

-- Create Stored Procedure Evolucao_Ocupacao_BD in MSDB
create procedure dbo.Evolucao_Ocupacao_BD
as
declare @nome sysname
create table #tab_tmp(Fileid int,FileGroup int,
                      TotalExtents bigint, UsedExtents bigint,
                      [Name] varchar(100),[FileName] varchar(260) )

DECLARE vcursor CURSOR FOR
select name from master..sysdatabases where name not in ('Northwind'
,'pubs'
,'distribution','master','model','tempdb')

OPEN vcursor
FETCH NEXT FROM vcursor into @nome

WHILE @@FETCH_STATUS = 0
BEGIN
   insert #tab_tmp exec('use ' + @nome + ' dbcc showfilestats')

   insert msdb.dbo.tb_ocupacaoBD (nomebd,tamanho_mb,espacoutil_mb) 
   select @nome nomedb,(sum(TotalExtents) * 64),(sum(UsedExtents) * 64) 
   from #tab_tmp
   
   truncate table #tab_tmp
   FETCH NEXT FROM vcursor into @nome
END

CLOSE vcursor
DEALLOCATE vcursor
drop table #tab_tmp
go

-- ******************* The end ****************************

truncate table msdb.dbo.tb_ocupacaoBD
select * from msdb.dbo.tb_ocupacaoBD

Rate

3.33 (3)

Share

Share

Rate

3.33 (3)