/*
Script to send an alert through mail, with information that how many drive
space is required from next databases growth on a specific instance and how many
space is available.
Script By: Amna Asif for ConnectSQL.blogspot.com
*/
DECLARE @dbName varchar[200],
@Qry Nvarchar[max]
DECLARE @dbsize VARCHAR[50],
@logsize VARCHAR[50],
@reservedpages VARCHAR[50],
@usedpages VARCHAR[50],
@pages VARCHAR[50]
SET @dbName = ''
---Get LOG File Spaces of All Databases--
CREATE TABLE #LogSpaceStats
[
RowID INT IDENTITY
PRIMARY KEY,
dbName SYSNAME,
Totallogspace DEC[20, 2],
UsedLogSpace DEC[20, 2],
Status CHAR[1]
]
INSERT #LogSpaceStats
[ dbName, Totallogspace, UsedLogSpace, Status ]
EXEC [ 'DBCC sqlperf[logspace] WITH NO_INFOMSGS'
]
--Get Info of All Drives
DECLARE @ServerDrives TABLE
[
RowID int IDENTITY
PRIMARY KEY,
Drive char,
DriveSpace varchar[100],
Required_Space varchar[100]
]
INSERT INTO @ServerDrives
[ Drive, DriveSpace ]
EXEC master.sys.xp_fixeddrives
--Temporary Table to hold requried data
CREATE TABLE #ServerFileStats
[
RowID INT IDENTITY
PRIMARY KEY,
dbName SYSNAME,
Database_DSize varchar[100],
Allocated_Space varchar[100],
Unallocated_Space varchar[100],
Unused varchar[100],
Database_LSize varchar[100],
UsedLogSpace DEC[20, 2],
FreeLogSpace DEC[20, 2],
FDataFileGrowth DEC[20, 2],
FLogFileGrowth DEC[20, 2],
DataFileDrive char,
LogFileDrive char
]
--Cursor Used to get each database size on given instance
DECLARE cur_dbName CURSOR
FOR SELECT NAME
FROM SYS.DATABASES
WHERE state_desc = 'ONLINE'
AND is_read_only = 0
OPEN cur_dbName
FETCH NEXT FROM cur_dbName into @dbName
WHILE @@FETCH_Status = 0
BEGIN
SELECT @Qry = ' SELECT @dbsizeOUT = sum[convert[bigint,
case when status & 64 = 0 then size
else 0 end]]
,@logsizeOUT = sum[convert[bigint,
case when status & 64 0 then size
else 0 end]]
FROM [' + @dbName + '].dbo.sysfiles '
EXEC sp_executesql @Qry,
N'@dbsizeOUT nvarchar[50] OUTPUT,@logsizeOUT nvarchar[50] OUTPUT',
@dbsizeOUT = @dbsize OUTPUT, @logsizeOUT = @logsize OUTPUT ;
SELECT @Qry = ' SELECT @reservedpagesOUT = sum[a.total_pages]
,@usedpagesOUT = sum[a.used_pages]
FROM [' + @dbName + '].sys.partitions p join [' + @dbName
+ '].sys.allocation_units a on p.partition_id = a.container_id
LEFT JOIN [' + @dbName
+ '].sys.internal_tables it on p.object_id = it.object_id'
EXEC sp_executesql @Qry,
N'@reservedpagesOUT nvarchar[50] OUTPUT,@usedpagesOUT nvarchar[50] OUTPUT',
@reservedpagesOUT = @reservedpages OUTPUT,
@usedpagesOUT = @usedpages OUTPUT ;
SELECT @Qry = ' INSERT INTO #ServerFileStats
SELECT DB_size.Database_Name
, DB_size.Database_DSize
, DB_size.Allocated_Space
, DB_size.Unallocated_Space
, DB_size.Unused
, DB_size.Database_LSize
, [lss.TotalLogSpace*[lss.UsedLogSpace/100]] UsedLogSpace
, [TotalLogSpace-[TotalLogSpace*[UsedLogSpace/100]]] FreeLogSpace
,CASE mfD.is_percent_growth
WHEN 0 THEN CONVERT[DEC[15,2],[mfD.growth* 8192 / 1048576]]
ELSE CONVERT[DEC[15,2],[CONVERT[DEC[15,2],REPLACE[DB_size.Database_DSize,'' MB'','''']]
*mfD.growth/100]] END FDataFileGrowth
,
CASE mfL.is_percent_growth WHEN 0 THEN CONVERT[DEC[15,2],[mfL.growth* 8192 / 1048576]]
ELSE CONVERT[DEC[15,2],[CONVERT[DEC[15,2],REPLACE[DB_size.Database_DSize,'' MB'','''']]
*mfL.growth/100]] END FLogFileGrowth
,LEFT[mfD.physical_name,1] DataFileDrive
,LEFT[mfL.physical_name,1] LogFileDrive
FROM
[
SELECT Database_Name = ''' + @dbName
+ '''
, Database_DSize = ltrim[str[[convert [dec [15,2],'
+ @dbsize
+ ']]* 8192 / 1048576,15,2] + '' MB'']
, ''Allocated_Space''=ltrim[str[[CASE WHEN '
+ @dbsize + ' >= ' + @reservedpages
+ '
THEN convert [DEC [15,2],'
+ @reservedpages
+ ']* 8192 / 1048576
ELSE 0 end],15,2] + '' MB'']
, ''Unallocated_Space'' = ltrim[str[[CASE WHEN '
+ @dbsize + ' >= ' + @reservedpages
+ '
THEN [convert [DEC [15,2],'
+ @dbsize + '] - convert [DEC [15,2],' + @reservedpages
+ ']]* 8192 / 1048576
ELSE 0 end],15,2] + '' MB'']
, ''Unused'' =ltrim[str[[CAST[['
+ @reservedpages + ' - ' + @usedpages
+ ']AS BIGINT] * 8192 / 1024.]/1024,15,2] + '' MB'']
, Database_LSize = ltrim[str[[convert [dec [15,2],'
+ @logsize
+ ']]* 8192 / 1048576,15,2] + '' MB'']
]DB_size LEFT JOIN #LogSpaceStats AS lss on lss.dbName=DB_size.Database_Name
INNER JOIN ' + @dbName
+ '.sys.databases db ON DB.name=DB_size.Database_Name
INNER JOIN ' + @dbName
+ '.sys.master_files mfD on mfD.database_id=DB.database_id AND mfD.type_desc=''ROWS''
INNER JOIN ' + @dbName
+ '.sys.master_files mfL on mfL.database_id=DB.database_id AND mfL.type_desc=''LOG'''
EXEC [ @Qry
]
FETCH NEXT FROM cur_dbName into @dbName
END
CLOSE cur_dbName
DEALLOCATE cur_dbName
UPDATE @ServerDrives
SET Required_Space = SumDriveS.sumofdrivespcae
FROM [ SELECT SUM[CONVERT[DEC[20, 2], sumofdrivespcae]] sumofdrivespcae,
DRIVE AS DRIVE
FROM [ SELECT SUM[CONVERT[DEC[20, 2], REPLACE[fss.FDataFileGrowth, ' MB', '']]]
sumofdrivespcae,
fss.DataFileDrive AS DRIVE
FROM #ServerFileStats fss
GROUP BY fss.DataFileDrive
UNION
SELECT SUM[CONVERT[DEC[20, 2], REPLACE[fss.FLogFileGrowth, ' MB', '']]]
sumofdrivespcae,
fss.LogFileDrive AS DRIVE
FROM #ServerFileStats fss
GROUP BY fss.LogFileDrive ] SumDrive
GROUP BY SumDrive.DRIVE ] SumDriveS
LEFT OUTER JOIN @ServerDrives sd on SumDriveS.Drive = sd.Drive
------------------------------------------------------------------------------
-----------------------------------------Report Mailing-----------------------
DECLARE @Loop int
DECLARE @Subject varchar[100]
DECLARE @strMsg varchar[4000]
SELECT @Subject = 'SQL Monitor Alert: ' + @@SERVERNAME + ' '
+ Convert[varchar, GETDATE[]]
Declare @Body varchar[max],
@TableHead varchar[1000],
@TableTail varchar[1000],
@TableHead2 varchar[1000],
@Body2 varchar[3000]
Set NoCount On ;
-- Create HTML mail body
Set @TableTail = '' ;
Set @TableHead = '' + ''
+ 'td {border: solid black 1px;padding-left:3px;padding-right:3px;padding-top:2px;padding-bottom:2px;font-size:10pt;} '
+ '' + ''
+ ''
+ '
Row ID '
+ 'Database Name '
+ 'File Group '
+ 'DF Total Space '
+ 'DF Allocated Space '
+ 'DF Unallocated Space '
+ 'DF Unused '
+ 'LF Total Space '
+ 'LF Used Space '
+ 'LF Unused Space '
+ 'DF FileGrowth '
+ 'LF FileGrowth '
+ 'DF Drive '
+ ' LF Drive 1', ' ']
Set @Body = Replace[@Body, '0', '']
DECLARE @flag BIT
SELECT @flag = 1
FROM @ServerDrives
WHERE convert[dec[15, 2], DriveSpace] Chủ Đề