Hoje com o aumento da capacidade de memória do servidor e com os discos SSDs temos ganhos consideráveis em performance mas os antigos problemas continuam ocorrendo nos novos recursos.
Contudo o conceito de disco ainda é chave no desempenho do servidor sql. Por exemplo, o fato de colocar os recursos do sql em discos diferentes ( dados, indices, log, tempdb ) incrementa muito o desempenho dos servidores.
O maior problema é o esgotamento do espaço dos discos e com isso o sistema para de funcionar. É fundamental que 'algo' nos alerte sobre o problema ANTES que ele ocorra.
Este recurso é muito útil quando colocado no log diário de manutenção do servidor. Ele 'emite' um alerta informando os administradores do sistema sobre uma condição que poderá trazer problemas em futuro próximo.
This procedure will send a notification if the free disk space on any of the drives
SQL Server resides on is lower than the specified limit.
The alert can either be an email or netsend.
usage: exec master.dbo.sp_diskalert 'harry@foo.com', 1000
Will send an email to harry@foo.com if the free disk space is less than 1000mb
NB more than one email address can be specified, separate using semi colons
USAGE: EXEC master.dbo.sp_diskalert 'HARRY PARKINSON', 250
Will send the alert via net send to user harry parkinson if the free disk space is less than 250mb
NB this could also be a computer name, normal net send rules apply
Supports sql server 7 or 2000
You need sql mail configured to send email!
If xp_cmdshell doesn't exist it will be added and dropped as needed
*/
USE master
GO
if exists
(select * from sysobjects where id = object_id(N'[dbo].[sp_diskalert]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_diskalert]
GO
create procedure sp_diskalert
@RCPT VARCHAR(500),
@LIMIT INT
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)
INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives
/* GENERATE THE MESSAGE */
IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@RCPT) > 0 --CHECK THERE IS SOME DATA AND A RECIPIENT
BEGIN
DECLARE @MSG VARCHAR(400),
@DLETTER VARCHAR(5),
@DSPACE INT
SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE < @LIMIT
ORDER BY DRVLETTER ASC)
SET @DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @DLETTER)
SET @MSG = @DLETTER + ' is at ' + CONVERT(VARCHAR,@DSPACE) --PUT THE VARS INTO A MSG
+ 'MB' + CHAR(13) + CHAR(10)
WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < @LIMIT AND DRVLETTER > @DLETTER) > 0
BEGIN --LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE
SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1
WHERE DRVSPACE < @LIMIT
AND DRVLETTER > @DLETTER
ORDER BY DRVLETTER ASC)
SET @DSPACE = (SELECT DRVSPACE FROM #T1
WHERE DRVLETTER = @DLETTER)
SET @MSG = @MSG + @DLETTER + ' is at ' + CONVERT(VARCHAR,@DSPACE) + 'MB'
+ CHAR(13) + CHAR(10)
END
/* SEND THE MESSAGE */
IF CHARINDEX('@',@RCPT) > 0 --THERE IS AN @ SYMBOL IN THE RECIPIENT - SEND EMAIL
BEGIN
DECLARE @EMAIL VARCHAR(600)
SET @EMAIL = 'EXEC master.dbo.xp_sendmail
@recipients = ''' + @RCPT + ''',
@message = ''' + @MSG + ''',
@subject = ''!! LOW FREE DISK SPACE ON ' + @@SERVERNAME + ' !!'''
EXEC (@EMAIL)
END
ELSE IF CHARINDEX('@',@RCPT) = 0 --THERE IS NO @ SYMBOL IN THE RECIPIENT - NET SEND
BEGIN
--DETERMINE IF XP_CMDSHELL EXISTS
DECLARE @FLAG BIT
SET @FLAG = 1
IF NOT EXISTS(SELECT NAME FROM master..sysobjects WHERE NAME = 'XP_CMDSHELL')
SET @FLAG = 0
--IF NOT RECREATE IT
IF @FLAG = 0
BEGIN
EXEC sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
PRINT 'ADDING XP_CMDSHELL'
END
--NET SEND MSG
DECLARE @NETSEND VARCHAR(600)
SET @MSG = 'ALERT - LOW FREE DISK SPACE ON ' + @@SERVERNAME + ' : ' + @MSG
SET @NETSEND = 'xp_cmdshell ''net send "' + RTRIM(@RCPT) + '" '
+ LEFT(RTRIM(REPLACE(@MSG,CHAR(13) + CHAR(10),', ')),LEN(@MSG)-2) + ''''
EXEC (@NETSEND)
--DROP XP_CMDSHELL IF IT DIDN'T EXIST
IF @FLAG = 0
BEGIN
EXEC sp_dropextendedproc 'xp_cmdshell'
PRINT 'DROPPING XP_CMDSHELL'
END
END
END
/* CLEANUP */
DROP TABLE #T1
END
GO
alter PROCEDURE sp_monit_disco AS
--CREATE PROCEDURE sp_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
)
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)
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 @mbTotal = 0
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
--exec sp_monit_disco