Szabad tárhely az SQL szerveren – Run SQL Server BLOG

Tárolt eljárás, amely ütemezhető rendszeres ellenőrzésre:

USE [master]
GO

CREATE PROC dbo.usp_AlertOnDBSpace (@minFreeDrvPctAllowed DECIMAL(5, 1) = 30.0)
AS
BEGIN
 SET NOCOUNT ON

 DECLARE @localDrivesSpaceInfo TABLE (
  idx INT IDENTITY
  ,DriveLetter CHAR(1)
  ,FreeSpaceGB FLOAT
  ,TotSizeGB FLOAT
  ,pctFree AS round(100.0 * FreeSpaceGB / TotSizeGB, 1)
  );
 DECLARE @infoFromPS TABLE (data VARCHAR(200));
 DECLARE @i INT = 0
 DECLARE @tblen INT
 DECLARE @currDrv CHAR(1)
 DECLARE @currDrvPct DECIMAL(5, 1)
 DECLARE @freeGB FLOAT
 DECLARE @totGB FLOAT
 DECLARE @warnMsg VARCHAR(128)

 INSERT INTO @infoFromPS
 EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID,FreeSpace,Size"';

 DELETE
 FROM @infoFromPS
 WHERE data IS NULL
  OR data LIKE '%DeviceID%'
  OR data LIKE '%----%'
  OR len(rtrim(ltrim(data))) < 3;

 INSERT INTO @localDrivesSpaceInfo
 SELECT LEFT(data, 1) AS driveLetter
  ,ltrim(rtrim(substring(data, 3, len(data) - 2 - charIndex(' ', REVERSE(data), 1)))) AS FreeSpaceGB
  ,right(data, charIndex(' ', REVERSE(data), 1) - 1) AS TotSizeGB
 FROM @infoFromPS

 SELECT @tblen = count(*)
 FROM @localDrivesSpaceInfo

 SET @i = @i + 1

 WHILE @i <= @tblen
 BEGIN
  SELECT @currDrv = DriveLetter
   ,@currDrvPct = pctFree
   ,@freeGB = ROUND(FreeSpaceGB / POWER(1024, 3), 1)
   ,@totGB = ROUND(TotSizeGB / POWER(1024, 3), 1)
  FROM @localDrivesSpaceInfo
  WHERE @i = idx;

  IF (
    @currDrv IN (
     SELECT DISTINCT left(upper(rtrim(ltrim(t.physical_name))), 1) AS DB_Drive
     FROM sys.master_files t
     )
    )
   AND (@currDrvPct < @minFreeDrvPctAllowed)
  BEGIN
   SET @warnMsg = CONCAT (
     'Warning! - Drive "'
     ,@currDrv
     ,'" has '
     ,rtrim(ltrim(Str(@currDrvPct)))
     ,'% of disk space left!'
     ,' ('
     ,rtrim(ltrim(Str(@freeGB)))
     ,' GB free out of '
     ,rtrim(ltrim(Str(@totGB)))
     ,' GB ) '
     )

   SELECT @warnMsg AS msg

   -- log it in viewer and in srv error logs 
   EXEC xp_logevent 60000
    ,@warnMsg
    ,warning
  END

  SET @i = @i + 1
 END
END
GO

USE [master]

CREATE PROC dbo.usp_AlertOnDBSpace (@minFreeDrvPctAllowed DECIMAL(5, 1) = 30.0)

BEGIN

SET NOCOUNT ON

DECLARE @localDrivesSpaceInfo TABLE (

idx INT IDENTITY

,DriveLetter CHAR(1)

,FreeSpaceGB FLOAT

,TotSizeGB FLOAT

,pctFree AS round(100.0 * FreeSpaceGB / TotSizeGB, 1)

);

DECLARE @infoFromPS TABLE (data VARCHAR(200));

DECLARE @i INT = 0

DECLARE @tblen INT

DECLARE @currDrv CHAR(1)

DECLARE @currDrvPct DECIMAL(5, 1)

DECLARE @freeGB FLOAT

DECLARE @totGB FLOAT

DECLARE @warnMsg VARCHAR(128)

INSERT INTO @infoFromPS

EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID,FreeSpace,Size"';

DELETE

FROM @infoFromPS

WHERE data IS NULL

OR data LIKE '%DeviceID%'

OR data LIKE '%----%'

OR len(rtrim(ltrim(data))) < 3;

INSERT INTO @localDrivesSpaceInfo

SELECT LEFT(data, 1) AS driveLetter

,ltrim(rtrim(substring(data, 3, len(data) - 2 - charIndex(' ', REVERSE(data), 1)))) AS FreeSpaceGB

,right(data, charIndex(' ', REVERSE(data), 1) - 1) AS TotSizeGB

FROM @infoFromPS

SELECT @tblen = count(*)

FROM @localDrivesSpaceInfo

SET @i = @i + 1

WHILE @i <= @tblen

BEGIN

SELECT @currDrv = DriveLetter

,@currDrvPct = pctFree

,@freeGB = ROUND(FreeSpaceGB / POWER(1024, 3), 1)

,@totGB = ROUND(TotSizeGB / POWER(1024, 3), 1)

FROM @localDrivesSpaceInfo

WHERE @i = idx;

IF (

@currDrv IN (

SELECT DISTINCT left(upper(rtrim(ltrim(t.physical_name))), 1) AS DB_Drive

FROM sys.master_files t

)

AND (@currDrvPct < @minFreeDrvPctAllowed)

BEGIN

SET @warnMsg = CONCAT (

'Warning! - Drive "'

,@currDrv

,'" has '

,rtrim(ltrim(Str(@currDrvPct)))

,'% of disk space left!'

,' ('

,rtrim(ltrim(Str(@freeGB)))

,' GB free out of '

,rtrim(ltrim(Str(@totGB)))

,' GB ) '

)

SELECT @warnMsg AS msg

-- log it in viewer and in srv error logs

EXEC xp_logevent 60000

,@warnMsg

,warning

END

SET @i = @i + 1

END

Az eredeti szkript:
https://www.mssqltips.com/sqlservertip/4670/simple-free-drive-space-alert-for-sql-server-databases/

Leave a Reply Cancel reply