|
|
| Definitions More Info. | | Definition ID | 94 | | Title | SQL | | Category | SCRIPT | | Definition | SORGU_SOUNUCUNU_MAIL_ATMA_DATABASE_SIZE | | Definition Description | USE [DBACHECKDB] GO /****** Object: StoredProcedure [dbo].[DBA_DiskCapacityControler] Script Date: 22.06.2016 10:26:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[DBA_DiskCapacityControler] AS BEGIN SET NOCOUNT ON DECLARE @IP VARCHAR(100), @svrName VARCHAR(255), @sql VARCHAR(400), @tableHTML NVARCHAR(MAX), @subject1 VARCHAR(200), @recipients CHAR(100), @subject CHAR(250), @body CHAR (250) --by default it will take the current server name, we can then set the server name as well SET @svrName = @@SERVERNAME SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace,Label | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''&''+$_.label+''*''}"' -- Mail Konusunu Set etme SELECT @IP= (SELECT TOP(1) local_net_address FROM sys.dm_exec_connections WHERE local_net_address IS NOT NULL) SELECT @subject1=@IP+'_'+@svrName+' NEBIM V3 DISK WARNING!!!' --creating a temporary table DECLARE @output TABLE (line VARCHAR(255)) INSERT @output EXEC xp_cmdshell @sql --PRINT (@sql) DECLARE @DISKS TABLE( id INT IDENTITY ,[DiskName] VARCHAR(10) ,[Capacity(MB)] bigint ,[FreeSpace(MB)] bigint ,[Label] VARCHAR(100) ) INSERT INTO @DISKS SELECT rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)' ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('&',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)' ,rtrim(ltrim(SUBSTRING(line,CHARINDEX('&',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('&',line)) )) as 'Label' FROM @output WHERE line like '[A-Z][:]%' ORDER BY drivename -- Check TempTable if exists IF OBJECT_ID('tempdb..#MonitorDiskCapacity') IS NOT NULL DROP TABLE #MonitorDiskCapacity SELECT [DiskName],[Capacity(MB)],[FreeSpace(MB)],[Label], [Capacity(MB)]/1024 as [Capacity(GB)],[FreeSpace(MB)]/1024 as [FreeSpace(GB)] ,round( [FreeSpace(MB)]*100/[Capacity(MB)],2) as [% Free] INTO #MonitorDiskCapacity FROM @DISKS -- Log Tablosuna Insert Etme INSERT INTO DBACHECKDB.dbo.MonitorDiskCapacity ([DiskName], [Capacity(MB)], [FreeSpace(MB)], [Label], [Capacity(GB)], [FreeSpace(GB)], [% Free]) SELECT [DiskName],[Capacity(MB)],[FreeSpace(MB)],[Label], [Capacity(MB)]/1024 as [Capacity(GB)],[FreeSpace(MB)]/1024 as [FreeSpace(GB)],round( [FreeSpace(MB)]*100/[Capacity(MB)],2) as [% Free] FROM #MonitorDiskCapacity WHERE ([% Free]<=10) BEGIN SELECT @tableHTML = N''+@IP+'_'+@svrName+' DISK UYARILARI' + N'' + N'| DiskName | '+ N'Capacity(MB) | '+ N'FreeSpace(MB) | '+ N'Label | '+ N'Capacity(GB) | '+ N'FreeSpace(GB) | '+ N'% Free | '+ N'LogDate | '+ CAST ( ( SELECT td = [DiskName], '', td = [Capacity(MB)], '', td = [FreeSpace(MB)], '', td = [Label], '', td = [Capacity(GB)], '', td = [FreeSpace(GB)], '', td = [% Free], '', td = [LogDate], '' FROM [MonitorDiskCapacity] WHERE ([% Free]<=10) AND [IsMailed] = 0 FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'
|---|
'+ N'
'+ N'
|
|
|
|
|
|
|
