磁盘空间和数据库大小的电子邮件警报

创建一个存储过程，通过可以附加到作业的电子邮件发送磁盘空间和数据库警报

关于

以前的客户端正在其SQL Server之一上遇到磁盘空间。我们希望及早了解潜在的错误可能出现的位置，并在线查看一些样本，这些样本导致了我们这两篇文章;

我们调整后的代码将向您发送低空间警报，驱动器空间声明和数据库大小列表，然后将其作为在一天中的不同时间运行的工作的一部分进行安排。

您将需要为您的服务器设置一个电子邮件帐户以使用，我将尝试为自己添加一篇文章，但同时请看Pinal Dave在SQL Authority.com或此MSDN文章的这篇文章。

SQL Code

USE [utilities]
GO
CREATE PROCEDURE [maint].[DiskFreeSpaceAlert]
@DriveCBenchmark int= 1024,
@OtherDataDriveBenchmark int= 10240,
@ProfileName sysname='DatabaseEmailProfileName',
@Recipients NVARCHAR(1000)='youremail@yourdomain'
AS BEGIN

IF EXISTS(SELECT * FROM tempdb..sysobjects
WHERE id =object_id(N'[tempdb]..[##disk_free_space]'))
DROP TABLE ##disk_free_space
CREATE TABLE ##disk_free_space(
DriveLetter CHAR(1) NOT NULL,
FreeMB INTEGER NOT NULL)

DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)

INSERT INTO ##disk_free_space
EXEC master..xp_fixeddrives

SELECT @DiskFreeSpace = FreeMB FROM ##disk_free_space where DriveLetter ='C'

SET @MailSubject =''+@@SERVERNAME+' Disk Analysis'
EXEC msdb..sp_send_dbmail @profile_name=@ProfileName,
@recipients = @Recipients,
@subject = @MailSubject,
@query=N'SELECT DriveLetter,CAST(CAST(CAST(FreeMB ASDECIMAL(18,2))/CAST(1024 AS DECIMAL(18,2)) AS DECIMAL(18,2)) AS VARCHAR(20)) +'' GB '' SpaceAvailable FROM ##disk_free_space'

/* Get db sizes */
DECLARE @dbName sysname
CREATE TABLE ##TempDBNames(
DatabaseName sysname,
DATABASE_SIZE int,
REMARKS varchar(254))
INSERT INTO ##TempDBNames
EXEC sp_databases

CREATE TABLE ##DBInfo(
dbName sysname,
name sysname,
filepath sysname,
dbType VARCHAR(10),
dbSize INT,
DataModified DATETIME
)

DECLARE dbs CURSOR FOR
SELECT DatabaseName FROM ##TempDBNames

open dbs
fetch next from dbs into @dbName
WHILE (@@FETCH_STATUS= 0)
Begin
EXEC ('USE '+ @dbName +' INSERT INTO ##DBInfo SELECT '''+ @dbName +''',name,physical_name,type_desc,size,(SELECTMAX(modify_date) FROM sys.tables) LastModified FROM sys.database_files')
fetch next from dbs into @dbName
End
close dbs
deallocate dbs

SET @MailSubject =''+@@SERVERNAME+' Database Analysis'
EXEC msdb..sp_send_dbmail @profile_name=@ProfileName,
@recipients = @Recipients,
@subject = @MailSubject,
@query=N'SELECT dbName,dbType,(dbSize*8)/1024dbSize,DataModified FROM ##DBInfo'

DROP TABLE ##DBInfo
DROP TABLE ##TempDBNames
/* End get dbsizes */

IF @DiskFreeSpace < @DriveCBenchmark
Begin
SET @MailSubject ='Drive C free space is low on '+@@SERVERNAME
SET @AlertMessage ='Drive C on '+@@SERVERNAME+' has only '+ CAST(@DiskFreeSpace AS VARCHAR)+' MB left. Please freeup space on this drive. C drive usually has OS installed on it. Lower space onC could slow down performance of the server'

EXEC msdb..sp_send_dbmail @profile_name=@ProfileName,
@recipients = @Recipients,
@subject = @MailSubject,
@body = @AlertMessage
End

DECLARE DriveSpace CURSOR FAST_FORWARD FOR
select DriveLetter, FreeMB from ##disk_free_space where DriveLetter not in('C')

open DriveSpace
fetch next from DriveSpace into @DriveLetter,@DiskFreeSpace

WHILE (@@FETCH_STATUS= 0)
Begin
if @DiskFreeSpace <@OtherDataDriveBenchmark
Begin
set @MailSubject ='Drive '+ @DriveLetter +' free space is low on '+@@SERVERNAME
set @AlertMessage = @DriveLetter +' has only '+cast(@DiskFreeSpace as varchar)+' MB left. Please increase free space for thisdrive immediately to avoid production issues'

EXEC msdb..sp_send_dbmail @profile_name=@ProfileName,
@recipients = @Recipients,
@subject = @MailSubject,
@body = @AlertMessage
End
fetch next from DriveSpace into @DriveLetter,@DiskFreeSpace
End
close DriveSpace
deallocate DriveSpace
DROP TABLE ##disk_free_space
END

请注意！

我们必须调整我们的代码，因为有人复制了整个程序，并以其磁盘空间详细信息向葡萄牙语发送了一封电子邮件，所以请确保您正确填写电子邮件地址。