Данный материал является переводом оригинальной статьи "MSSQLTips : SQL Server Disk Space Monitoring for all Instances with PowerShell Script".
В первой части этой серии было рассмотрено то, как собирать значения для мониторинга использования процессора и памяти для ваших экземпляров SQL Server. В этой статье мы сконцентрируемся на информации о дисках, где находятся файлы данных вашей базы данных, чтобы убедиться, что имеется достаточно свободного места. Здесь будет представлен вариант сценария PowerShell для сбора информации из всех экземпляров SQL Server, а также приведена хранимая процедура для получения информации о каждом отдельно взятом файле данных.
Предполагается, что у вас есть централизованный экземпляр SQL Server, где вы будете хранить всю информацию, собранную сценарием PowerShell. Этот экземпляр также будет содержать хранимую процедуру, которая возвращает код T-SQL для извлечения информации о файле данных.
Представленные сценарии должны быть немного изменены, чтобы соответствовать вашему конкретному случаю. Этого можно добиться, в основном, заменив "XXX" на значения из вашего окружения.
Хранимая процедура SQL Server для анализа утилизации дисков
Лучший вариант - создать базу данных, которую вы используете для мониторинга всех ваших экземпляров. Создадим базу данных под названием "monitoring".
CREATE DATABASE [monitoring]
GO USE monitoring
GO
Создадим схему под названием "monitoring", чтобы все было организованно и чисто.
CREATE SCHEMA [monitoring]
GO
Также создадим таблицу с именем "monitoring.thresholds", которая определяет, когда возникла проблема, и загрузим значение для тестирования.
CREATE TABLE [monitoring].[thresholds](
[item] [varchar](50) NULL,
[warning_value] [char](3) NULL
) ON [PRIMARY]
GO INSERT INTO [monitoring].[thresholds]
VALUES ( 'disk_space', 75 ) -- 75% or less free
GO
Вот код для хранимой процедуры:
/****** Object: StoredProcedure [Monitoring].[disk_space] Script Date: 2/1/2019 7:02:07 PM ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO -- =============================================
-- Author: Alejandro Cobar
-- Create date: 12/01/2018
-- Description: Grabs disk related information for one or multiple SQL Server instances
-- =============================================
CREATE PROCEDURE [Monitoring].[disk_space] AS
BEGIN
SET NOCOUNT ON; DECLARE @query VARCHAR(MAX);
DECLARE @threshold CHAR(3); SET @threshold = (SELECT warning_value FROM Monitoring.Thresholds WHERE item = 'disk_space'); SET @query = '
DECLARE @xp_cmdshell bit
DECLARE @flipped bit SET @flipped = 0 /* Check if xp_cmdshell is enabled or not */
SELECT @xp_cmdshell = (CONVERT(INT, ISNULL(value, value_in_use)))
FROM sys.configurations
WHERE name = '+CHAR(39)+'xp_cmdshell'+CHAR(39)+'; IF @xp_cmdshell = 0
BEGIN
EXEC SP_CONFIGURE '+CHAR(39)+'show advanced options'+CHAR(39)+', 1
RECONFIGURE
EXEC SP_CONFIGURE '+CHAR(39)+'xp_cmdshell'+CHAR(39)+', 1
RECONFIGURE
SET @flipped = 1
END DECLARE @SQL NVARCHAR(1000) CREATE TABLE #DrvLetter (Drive VARCHAR(500))
CREATE TABLE #DrvInfo (
Drive VARCHAR(500) null,
[MB free] DECIMAL(20,2),
[MB TotalSize] DECIMAL(20,2),
[Volume Name] VARCHAR(64)
) INSERT INTO #DrvLetter
EXEC xp_cmdshell '+CHAR(39)+'wmic volume where drivetype="3" get caption, freespace, capacity, label'+CHAR(39)+'
DELETE FROM #DrvLetter WHERE drive IS NULL OR len(drive) < 4 OR Drive LIKE '+CHAR(39)+'%Capacity%'+CHAR(39)+' OR Drive LIKE '+CHAR(39)+'%\\%\Volume%'+CHAR(39)+' DECLARE @STRLine VARCHAR(8000)
DECLARE @Drive varchar(500)
DECLARE @TotalSize REAL
DECLARE @Freesize REAL
DECLARE @VolumeName VARCHAR(64) WHILE EXISTS(SELECT 1 FROM #DrvLetter)
BEGIN
SET ROWCOUNT 1
SELECT @STRLine = drive FROM #DrvLetter /* Get TotalSize */
SET @TotalSize= CAST(LEFT(@STRLine,CHARINDEX('+CHAR(39)+' '+CHAR(39)+',@STRLine)) AS REAL)/1024/1024 /* Remove Total Size */
SET @STRLine = REPLACE(@STRLine, LEFT(@STRLine,CHARINDEX('+CHAR(39)+' '+CHAR(39)+',@STRLine)),'+CHAR(39)+CHAR(39)+')
SET @Drive = LEFT(LTRIM(@STRLine),CHARINDEX('+CHAR(39)+' '+CHAR(39)+',LTRIM(@STRLine)))
SET @STRLine = RTRIM(LTRIM(REPLACE(LTRIM(@STRLine), LEFT(LTRIM(@STRLine),CHARINDEX('+CHAR(39)+' '+CHAR(39)+',LTRIM(@STRLine))),'+CHAR(39)+CHAR(39)+')))
SET @Freesize = LEFT(LTRIM(@STRLine),CHARINDEX('+CHAR(39)+' '+CHAR(39)+',LTRIM(@STRLine)))
SET @STRLine = RTRIM(LTRIM(REPLACE(LTRIM(@STRLine), LEFT(LTRIM(@STRLine),CHARINDEX('+CHAR(39)+' '+CHAR(39)+',LTRIM(@STRLine))),'+CHAR(39)+CHAR(39)+')))
SET @VolumeName = @STRLine INSERT INTO #DrvInfo
SELECT @Drive, @Freesize/1024/1024 , @TotalSize, @VolumeName DELETE FROM #DrvLetter
END SET ROWCOUNT 0 /* POPULATE TEMP TABLE WITH LOGICAL DISKS */
SET @SQL ='+CHAR(39)+'wmic /FailFast:ON logicaldisk where (drivetype ="3" and volumename!="RECOVERY" AND volumename!="System Reserved") get deviceid,volumename /Format:csv'+CHAR(39)+' if object_id('+CHAR(39)+'tempdb..#output1'+CHAR(39)+') is not null drop table #output1
CREATE TABLE #output1 (Col1 VARCHAR(2048))
INSERT INTO #output1
EXEC master..xp_cmdshell @SQL DELETE #output1 where ltrim(col1) is null or len(col1) = 1 or Col1 like '+CHAR(39)+'Node,DeviceID,VolumeName%'+CHAR(39)+' if object_id('+CHAR(39)+'tempdb..#logicaldisk'+CHAR(39)+') is not null drop table #logicaldisk
CREATE TABLE #logicaldisk (DeviceID varchar(128),VolumeName varchar(256)) DECLARE @NodeName varchar(128)
SET @NodeName = (SELECT TOP 1 LEFT(Col1, CHARINDEX('+CHAR(39)+','+CHAR(39)+',Col1)) FROM #output1) /* Clean up server name */
UPDATE #output1 SET Col1 = REPLACE(Col1, @NodeName, '+CHAR(39)+CHAR(39)+') INSERT INTO #logicaldisk
SELECT LEFT(Col1, CHARINDEX('+CHAR(39)+','+CHAR(39)+',Col1)-2), SUBSTRING(COL1, CHARINDEX('+CHAR(39)+','+CHAR(39)+',Col1)+1, LEN(col1))
FROM #output1 UPDATE dr
SET dr.[Volume Name] = ld.VolumeName
FROM #DrvInfo dr RIGHT OUTER JOIN #logicaldisk ld ON left(dr.Drive,1) = ld.DeviceID
WHERE LEN([Volume Name]) = 1 CREATE TABLE #DBInfo2 (
ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime) DECLARE @command VARCHAR(5000) DECLARE @instance VARCHAR(100)
SELECT @instance = CONVERT(VARCHAR(100),SERVERPROPERTY(''ServerName'')) SELECT @command = '+CHAR(39)+'Use [?]
SELECT
'+CHAR(39)+'+'+CHAR(39)+CHAR(39)+CHAR(39)+CHAR(39)+'+'+'@instance'+'+'+CHAR(39)+CHAR(39)+CHAR(39)+CHAR(39)+'+'+CHAR(39)+' AS ServerName,
'+CHAR(39)+CHAR(39)+'?'+CHAR(39)+CHAR(39)+' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx('+CHAR(39)+CHAR(39)+'?'+CHAR(39)+CHAR(39)+','+CHAR(39)+CHAR(39)+'Status'+CHAR(39)+CHAR(39)+')) AS Status,
CONVERT(sysname,DatabasePropertyEx('+CHAR(39)+CHAR(39)+'?'+CHAR(39)+CHAR(39)+','+CHAR(39)+CHAR(39)+'Updateability'+CHAR(39)+CHAR(39)+')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx('+CHAR(39)+CHAR(39)+'?'+CHAR(39)+CHAR(39)+','+CHAR(39)+CHAR(39)+'Recovery'+CHAR(39)+CHAR(39)+')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, '+CHAR(39)+CHAR(39) +'SpaceUsed'+CHAR(39)+CHAR(39)+') AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, '+CHAR(39)+CHAR(39)+'SpaceUsed'+CHAR(39)+CHAR(39)+') AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + '+CHAR(39)+CHAR(39)+CHAR(39)+CHAR(39)+' AS FreeSpacePct
FROM dbo.sysfiles' + CHAR(39) + ' INSERT INTO #DBInfo2 (
ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct) EXEC sp_MSForEachDB @command SELECT
db.ServerName Instance,
db.DatabaseName AS DBName,
db.PhysicalFileName AS PhysicalFileLocation,
CASE
WHEN LEN(dr.drive) = 3 THEN LEFT(dr.drive,1)+'+CHAR(39)+':\'+CHAR(39)+'
ELSE dr.drive+'+CHAR(39)+':\'+CHAR(39)+'
END AS Drive,
db.FileSizeMB AS DBFileSizeMB,
dr.[MB TotalSize] AS TotalSpaceInMB,
dr.[MB free] AS FreeSpaceInMB,
CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) AS PercentFreeSpace
FROM #DBInfo2 db
JOIN #DrvInfo dr ON LEFT(db.PhysicalFileName,LEN(dr.drive)) = LEFT(dr.drive,LEN(dr.drive))
WHERE db.DatabaseName not in (
SELECT DatabaseName
FROM #DBInfo2 DB
JOIN (SELECT drive FROM #DrvInfo WHERE LEN(drive) > 3) DR
ON LEFT(db.PhysicalFileName, LEN(drive)) = DR.drive)
AND CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) < '+@threshold+'
UNION ALL
SELECT
db.ServerName Instance,
db.DatabaseName AS DBName,
db.PhysicalFileName AS PhysicalFileLocation,
CASE
WHEN LEN(dr.drive) = 3 THEN LEFT(dr.drive,1)+'+CHAR(39)+':\'+CHAR(39)+'
ELSE dr.drive+'+CHAR(39)+':\'+CHAR(39)+'
END AS Drive,
db.FileSizeMB AS DBFileSizeMB,
dr.[MB TotalSize] AS TotalSpaceInMB,
dr.[MB free] AS FreeSpaceInMB,
CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) AS PercentFreeSpace
FROM #DBInfo2 db
JOIN #DrvInfo dr ON LEFT(db.PhysicalFileName,LEN(dr.drive)) = LEFT(dr.drive,LEN(dr.drive))
WHERE LEN(dr.drive) > 3 AND CAST((dr.[MB free]/dr.[MB TotalSize]) * 100 AS NUMERIC(5,2)) < '+@threshold+' DROP TABLE #DBInfo2
DROP TABLE #logicaldisk
DROP TABLE #DrvLetter
DROP TABLE #DrvInfo IF @flipped = 1
BEGIN
EXEC SP_CONFIGURE '+CHAR(39)+'xp_cmdshell'+CHAR(39)+', 0
RECONFIGURE
END EXEC SP_CONFIGURE '+CHAR(39)+'show advanced options'+CHAR(39)+', 0
GO
RECONFIGURE
GO'; SELECT @query AS tsql;
END GO
Скрипт PowerShell, вызывающий хранимую процедуру
Вот код, который извлекает информацию о дисках из набора указанных вами экземпляров.
- Скрипт вызывает хранимую процедуру, описанную выше.
- Отделяя хранимую процедуру от сценария PowerShell, вы получаете более управляемый код с обеих сторон, не сводя все в один сценарий.
$server = "XXX"
$inventoryDB = "XXX" #Create the DiskSpace Table if it doesn't exist in your centralized instance
#You can remove the 'Monitoring' schema if you want.
$diskSpaceTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'DiskSpace' AND xtype = 'U')
CREATE TABLE [Monitoring].[DiskSpace](
[Instance] [nvarchar](50) NULL,
[DBName] [nvarchar](255) NULL,
[PhysicalFileLocation] [nvarchar](500) NULL,
[Drive] [nvarchar](50) NULL,
[DBFileSizeMB] [int] NULL,
[TotalSpaceInMB] [int] NULL,
[FreeSpaceInMB] [int] NULL,
[PercentFreeSpace] [float] NULL
) ON [PRIMARY]
"
Invoke-Sqlcmd -Query $diskSpaceTableCreationQuery -Database $inventoryDB -ServerInstance $server #Clean the DiskSpace table
Invoke-Sqlcmd -Query "TRUNCATE TABLE Monitoring.DiskSpace" -Database $inventoryDB -ServerInstance $server #Create the thresholds Table if it doesn't exist in your centralized instance
$thresholdsTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'thresholds' AND xtype = 'U')
CREATE TABLE [Monitoring].[thresholds](
[id] [tinyint] IDENTITY(1,1) NOT NULL,
[item] [varchar](25) NOT NULL,
[warning_value] [tinyint] NOT NULL,
[critical_value] [tinyint] NOT NULL,
CONSTRAINT [PK_thresholds] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
"
Invoke-Sqlcmd -Query $thresholdsTableCreationQuery -Database $inventoryDB -ServerInstance $server #Insert the threshold value to set the upper cap that the SP will use to limit the result set (the value won't be inserted if it already exists.
#This is designed this way so that you can use this table to store threshold values for other purposes (CPU, RAM, etc.)
$thresholdValueInsertQuery = "
IF NOT EXISTS (SELECT item FROM Monitoring.thresholds
WHERE item = 'disk_space')
BEGIN
INSERT INTO Monitoring.thresholds VALUES ('disk_space', 50, 10)
END
"
Invoke-Sqlcmd -Query $thresholdvalueInsertQuery -Database $inventoryDB -ServerInstance $server #Fetch all the instances with the respective SQL Server Version
<#
This is an example of the result set that your query must return
##################################################
# name # instance #
##################################################
# server1.domain.net,45000 # server1 #
# server1.domain.net,45001 # server1\MSSQLSERVER1#
# server2.domain.net,45000 # server2 #
# server3.domain.net,45000 # server3 #
# server4.domain.net # server4\MSSQLSERVER2#
##################################################
#> #If you don't have such table in your environment, the following block of code will create it for you. You just simply have to make sure to populate it accordingly.
$instancesTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'instances' AND xtype = 'U')
CREATE TABLE instances(
[name] [nvarchar](128) NULL,
[instance] [nvarchar](128) NULL
) ON [PRIMARY]
"
Invoke-Sqlcmd -Query $instancesTableCreationQuery -Database $inventoryDB -ServerInstance $server $instanceLookupQuery = "SELECT name, instance FROM instances" $instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery #For each instance, grab the disk space information
foreach ($instance in $instances){
$diskSpaceQuery = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query "EXEC Monitoring.disk_space" -MaxCharLength 8000 #Go grab the disks information for the instance
Write-Host "Fetching Disk information for instance" $instance.instance
$results = Invoke-Sqlcmd -Query $diskSpaceQuery.tsql -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30 #Perform the INSERT in the DiskSpace table only if it returned at least 1 row
if($results.Length -ne 0){
#Build the insert statement
$insert = "INSERT INTO Monitoring.DiskSpace VALUES"
foreach($result in $results){
$insert += "
(
'"+$result.Instance+"',
'"+$result.DBName+"',
'"+$result.PhysicalFileLocation+"',
'"+$result.Drive+"',
"+$result.DBFileSizeMB+",
"+$result.TotalSpaceInMB+",
"+$result.FreeSpaceInMB+",
"+$result.PercentFreeSpace+"
),
"
} #Store the results in the local DiskSpace table in your central instance
Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
}
}
Write-Host "Done!"
После выполнения вышеизложенного вы получите набор результатов, который выглядит следующим образом:
- Instance - экземпляр, где были собраны данные
- DBName - база данных, где были собраны данные
- PhysicalFileLocation – физическое расположение файла базы данных.
- Disk - буква диска для расположения файла базы данных
- DBFileSizeMB - размер физического файла
- TotalSpaceMB - общее пространство для диска
- FreeSpaceMB - свободное место на диске
- PercentFreeSpace - процент свободного места на диске
Для каждой базы данных вы увидите 2 записи: 1 для файла *.mdf и 1 для файла *.ldf. Если в базе данных есть дополнительные файлы данных или дополнительные файлы журнала, вы также увидите их.
Замечание
Я заметил, что во время тестирования на некоторых компьютерах может возникнуть проблема, где команда "wmic /FailFast:ON …" в хранимой процедуре SQL Server вызывает сбой процесса. Чтобы исправить эту проблему для этой машины, была изменена строка в хранимой процедуре:
Из варианта:
SET @SQL = 'wmic /FailFast:ON logicaldisk where (drivetype ="3" and volumename!="RECOVERY" AND volumename!="System Reserved") get deviceid,volumename /Format:csv'
К варианту:
SET @SQL = 'wmic logicaldisk where (drivetype ="3" and volumename!="RECOVERY" AND volumename!="System Reserved") get deviceid,volumename /Format:csv'
Следующие шаги
Из полученного набора результатов вы можете построить механизм отчетности/оповещения. Вы можете настроить оповещения, которые будут отправляться вам в соответствии с определенными вами пороговыми значениями.
В идеале вам нужно создать задание агента SQL Server, которое будет собирать эту информацию на регулярной основе, чтобы вы всегда могли быть в курсе происходящего в вашей среде. Таким образом, вы сможете вовремя реагировать на проблему и начинать действовать ещё до того, как системный администратор постучится в вашу дверь.
RSS - Записи
Добавить комментарий