ディスク容量とデータベースサイズの電子メールアラート

ジョブに添付できる電子メールを介してディスク容量とデータベースアラートを送信するストアドプロシージャを作成します

約

以前のクライアントは、SQLServerの1つでディスク容量に苦労していました。潜在的なエラーが発生する可能性のある場所を早期に把握したいと考え、オンラインでいくつかのサンプルを探して、これら2つの記事に導きました。

適合したコードは、スペース不足のアラート、ドライブスペースのステートメント、およびデータベースサイズのリストを送信し、ジョブの一部として1日のさまざまな時間に実行するようにスケジュールしました。

サーバーで使用する電子メールアカウントを設定する必要があります。この記事を自分で追加しようとしますが、それまでの間、 SQLAuthority.comのPinalDaveによるこの記事またはこの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

ご注意ください！

誰かが手順全体をコピーして、ディスク容量の詳細を記載したポルトガル語の電子メールを送信したため、上記のコードを適応させる必要がありました。そのため、電子メールアドレスを正しく入力してください。