Monitorando o Espaço em Disco do Servidor SQL Server

É muito comum encontrar em fórumns DBA perguntando sobre como fazer para monitorar pelo SQL Server o tamanho dos discos e seus respectivos espaço livre.

Pensando nisso, apresento a você a procedure usp_monit_disco. Esta procedure deve ser criada no banco de dados master e ao executá-la retorna informações como:

a) tamanho total do disco
b) Espaço usado e espaço livre (MB)
c) b) Espaço usado e espaço livre (%)
d) Espaço ocupado pelo SQL Server (MB)
e) A data de coleta das informações.

Uma vez que a procedure retorna a data da coleta, fica muito fácil manter por exemplo um histórico desta informações, permitindo que você tenha um acompanhamento do espaço em seus discos. Para isso, basta você alterar a procedure trocando a tabela temporária #espacodisco por uma tabela real em seu banco de dados.

O código completo da procedure está abaixo e ao ser executado você terá o seguinte resultado:

Drive Tamanho (MB) Usado (MB) Livre (MB) Livre (%) Usado (%) Ocupado SQL (MB) Data
—– ———— ———– ———– ——— ——— —————- ———————–
C: 6134 5527 607 9% 91% 581 2007-12-31 13:15:00
D: 14323 7914 6409 44% 56% NULL 2007-12-31 13:15:00

PROCEDURE

USE Master
GO
CREATE PROCEDURE usp_monit_disco
AS
SET NOCOUNT ON

CREATE TABLE #dbspace (
name sysname,
caminho varchar(200),
tamanho varchar(10),
drive Varchar(30))

CREATE TABLE [#espacodisco] (
Drive varchar (10) ,
[Tamanho (MB)] Int,
[Usado (MB)] Int,
[Livre (MB)] Int,
[Livre (%)] Varchar(100),
[Usado (%)] Varchar(100),
[Ocupado SQL (MB)] Int,
[Data] smalldatetime)

Exec SP_MSForEachDB ‘Use ? Insert into #dbspace Select Convert(Varchar(25),DB_Name())”Database”,Convert(Varchar(60),FileName),Convert(Varchar(8),Size/128)”Size in MB”,Convert(Varchar(30),Name) from SysFiles’

DECLARE @hr int,@fso int,@mbtotal int,
@TotalSpace int,@MBFree int,
@Percentage int,@SQLDriveSize int,
@size float
DECLARE @drive Varchar(1),@fso_Method varchar(255)

SET @mbTotal = 0
EXEC @hr = master.dbo.sp_OACreate ‘Scripting.FilesystemObject’, @fso OUTPUT

CREATE TABLE #space (drive char(1), mbfree int)
INSERT INTO #space EXEC master.dbo.xp_fixeddrives
Declare CheckDrives Cursor For Select drive,MBfree From #space
Open CheckDrives
Fetch Next from CheckDrives into @Drive,@MBFree
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @fso_Method = ‘Drives(“‘ + @drive + ‘:”).TotalSize’
SELECT @SQLDriveSize=sum(Convert(Int,tamanho)) from #dbspace where Substring(caminho,1,1)=@drive
EXEC @hr = sp_OAMethod @fso, @fso_method, @size OUTPUT
SET @mbtotal = @mbtotal + @size / (1024 * 1024)
INSERT INTO #espacodisco VALUES(@Drive+‘:’,@MBTotal,@MBTotal–@MBFree,@MBFree,
Convert(Varchar,100 * round(@MBFree,2) / round(@MBTotal,2))+‘%’,
Convert(Varchar,100 – 100 * round(@MBFree,2) / round(@MBTotal,2))+‘%’,@SQLDriveSize, getdate())

FETCH NEXT FROM CheckDrives INTO @drive,@mbFree
END
CLOSE CheckDrives
DEALLOCATE CheckDrives

SELECT * FROM #espacodisco
DROP TABLE #dbspace
DROP TABLE #space
DROP TABLE #espacodisco
GO

Nota: Para executar esta procedure no SQL Server 2005 você deve ativar a opção de OLE Automation na sp_configure. O script abaixo permite que você ative esta opção.

sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
sp_configure ‘Ole Automation Procedures’, 1
RECONFIGURE
GO
sp_configure ‘show advanced options’, 0
RECONFIGURE
GO

Abraços
Nilton Pinheiro

Avaliação: