Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Размер локальных дисков на сервере  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Доброго времени суток!

СУБД: Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Есть задача настроить алерты на событие, если размер свободного пространства на дисках станет меньше 10% от общего размера.

На данный момент у меня работает скрипт, который проверяет свободное место на заданное значение(если менее 10 Гб то пишем письмо). Реализовано это следующим образом:

+

CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)

INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives

/* GENERATE THE MESSAGE */

IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@RCPT) > 0 --CHECK THERE IS SOME DATA AND A RECIPIENT
BEGIN
DECLARE @MSG VARCHAR(400),
@DLETTER VARCHAR(5),
@DSPACE INT

SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE < @LIMIT 
ORDER BY DRVLETTER ASC)

SET @DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @DLETTER)

SET @MSG =convert(varchar(10), getdate(), 104) + ' ' + convert(varchar(10), getdate(), 108) + ' On ' + @@SERVERNAME + ' ' + @DLETTER + ' less  ' + CONVERT(VARCHAR,@DSPACE) --PUT THE VARS INTO A MSG
+ 'MB!!!'-- + CHAR(13) + CHAR(10)


WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < @LIMIT AND DRVLETTER > @DLETTER) > 0
BEGIN	 --LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE	
SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 
WHERE DRVSPACE < @LIMIT 
AND DRVLETTER > @DLETTER 
ORDER BY DRVLETTER ASC)

SET @DSPACE = (SELECT DRVSPACE FROM #T1 
WHERE DRVLETTER = @DLETTER)
SET @MSG = @MSG + 'On local disk ' + @DLETTER + ' free space is at ' + CONVERT(VARCHAR,@DSPACE) + 'MB' 
+ CHAR(13) + CHAR(10)
END



Но процедура
xp_fixeddrives 
показывает только свободное место, а как можно узнать сколько всего места на локальных дисках?
2 июл 12, 10:33    [12802428]     Ответить | Цитировать Сообщить модератору
 Re: Размер локальных дисков на сервере  [new]
denisofff
Member

Откуда: Владивосток
Сообщений: 62
andrew shalaev,

Нашел в закромах кода такой скрипт. Проверил, работает. Использует утилиту fsutil, кажется, начиная с 2003 сервера идет в поставке. Правда, использует курсоры и xp_cmdshell... Так что, возможно, можно поискать обходной путь.

DECLARE	@drives TABLE 
		(
			drive CHAR(1), 
			free INT
		)

	INSERT INTO @drives 
		EXEC xp_fixeddrives

	DECLARE	@drive CHAR(1)
		   ,@str VARCHAR(255)
	DECLARE	@tmp TABLE
		(
			cmd_output VARCHAR(255)
		)
	DECLARE	@drives_info TABLE
		(
			drive CHAR(1), 
			free_bytes BIGINT, 
			total_bytes BIGINT
		)

	DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
		SELECT drive 
		FROM @drives 
		ORDER BY drive

	OPEN cur
	FETCH NEXT FROM cur INTO @drive

	WHILE @@fetch_status = 0
	BEGIN
		DELETE FROM @tmp
		SET @str = 'exec xp_cmdshell ''fsutil volume diskfree ' + @drive + ':'''	
		INSERT INTO @tmp EXEC (@str)		

		INSERT INTO @drives_info
			SELECT
				@drive,
				substring(t1.cmd_output, charindex(':', t1.cmd_output) + 2, len(t1.cmd_output) - charindex(':', t1.cmd_output) - 2),
				substring(t2.cmd_output, charindex(':', t2.cmd_output) + 2, len(t2.cmd_output) - charindex(':', t2.cmd_output) - 2)
			FROM @tmp t1, @tmp t2
			WHERE t1.cmd_output like 'Всего свободно байт %' 
				and t2.cmd_output like 'Всего байт%'

		FETCH NEXT FROM cur INTO @drive
	END

	CLOSE cur 
	DEALLOCATE cur 

	DECLARE @res VARCHAR(MAX)

	SET @res = ''

	SELECT @res = @res + 
		'На диске ' + drive + ' осталось ' + CONVERT(VARCHAR(20), [free]) + 
		'% свободного места' + CHAR(10)
	FROM
		(
			SELECT
				drive,
				CAST(free_bytes/1024/1024 AS DECIMAL(10, 2)) AS [FREE_MB],
				CAST(total_bytes/1024/1024 AS DECIMAL(10, 2)) AS [TOTAL_MB],
				CAST(free_bytes*100.0/total_bytes as DECIMAL(6,2)) as [free] 
			FROM @drives_info
		) AS o_o
	WHERE [free] <= 20

	SELECT @res = @res + 
		'На диске ' + drive + ' осталось ' + CONVERT(VARCHAR(20), [free_mb]) + 
		' Мб свободного места' + CHAR(10)
	FROM
		(
			SELECT
				drive,
				CAST(free_bytes/1024/1024 AS DECIMAL(10, 2)) AS [FREE_MB],
				CAST(total_bytes/1024/1024 AS DECIMAL(10, 2)) AS [TOTAL_MB],
				CAST(free_bytes*100.0/total_bytes as DECIMAL(6,2)) as [free] 
			FROM @drives_info
		) AS o_o
	WHERE free_mb <= 2000

        IF @res <> ''
	BEGIN
		EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLMail',
				@recipients = N'admin@admin.admin',
				@body = @RES,				
				@subject='Внимание! Проблемы на сервере SQL'				
	END
2 июл 12, 10:46    [12802518]     Ответить | Цитировать Сообщить модератору
 Re: Размер локальных дисков на сервере  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34727
Блог
Рекомендую не изобретать велосипед:
SSMS -> Агент SQL Server -> Предупреждения
2 июл 12, 11:09    [12802653]     Ответить | Цитировать Сообщить модератору
 Re: Размер локальных дисков на сервере  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
denisofff,

К сожалению не на всех серверах работает, и почему то не верно показывает общее и свободное место на дисках.
2 июл 12, 11:40    [12802849]     Ответить | Цитировать Сообщить модератору
 Re: Размер локальных дисков на сервере  [new]
guest45
Guest
CREATE function [dbo].[af_DrivesInfo] ()
returns @Drives table (Drive char(1), TotalSize bigint, AvailableSpace bigint)
as begin
/*
	select getdate()
	select Drive, 
		TotalSize = cast(TotalSize/1024./1024./1024. as decimal(10,2)), 
		AvailableSpace = cast(AvailableSpace/1024./1024./1024. as decimal(10,2)) 
		from dbo.af_DrivesInfo()
	select getdate()
*/
	declare @hr integer, @fso integer, @objDrive integer, @Drive char(1), @DriveInt int, 
		@DriveType int, @TotalSizeC varchar(1000), @AvailableSpaceC varchar(18), @TotalSize bigint, @AvailableSpace bigint

	exec @hr = sp_OACreate 'Scripting.FileSystemObject', @fso output

	set @DriveInt = 67

	while @DriveInt <= 90 begin
		set @Drive = CHAR(@DriveInt)
		set @TotalSize = null
		set @AvailableSpace = null

		exec @hr = sp_OAMethod @fso, 'GetDrive', @objDrive out, @Drive
		if @hr = 0 begin
			exec @hr = sp_OAGetProperty @objDrive,'DriveType', @DriveType output
			if @DriveType = 2 begin
				exec @hr = sp_OAGetProperty @objDrive, 'TotalSize', @TotalSizeC output
				exec @hr = sp_OAGetProperty @objDrive, 'AvailableSpace', @AvailableSpaceC output

				insert @Drives (Drive, TotalSize, AvailableSpace)
				values (@Drive, convert(bigint, @TotalSizeC), convert(bigint, @AvailableSpaceC))
			end
			exec sp_OADestroy @objDrive
		end

		set @DriveInt = @DriveInt + 1
	end

	exec sp_OADestroy @fso

	return
end
GO
2 июл 12, 12:45    [12803298]     Ответить | Цитировать Сообщить модератору
 Re: Размер локальных дисков на сервере  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
guest45,

Спасибо!
2 июл 12, 15:16    [12804646]     Ответить | Цитировать Сообщить модератору
 Re: Размер локальных дисков на сервере  [new]
Andrey Sribnyak
Member

Откуда: Киев
Сообщений: 599
andrew shalaev
Доброго времени суток!

СУБД: Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Есть задача настроить алерты на событие, если размер свободного пространства на дисках станет меньше 10% от общего размера.




Все уже придумано до нас

http://www.sqlmag.com/blog/practical-sql-server-45/sql-server-2012/disk-space-monitoring-143552
2 июл 12, 16:10    [12805088]     Ответить | Цитировать Сообщить модератору
 Re: Размер локальных дисков на сервере  [new]
denisofff
Member

Откуда: Владивосток
Сообщений: 62
Критик
Рекомендую не изобретать велосипед:
SSMS -> Агент SQL Server -> Предупреждения

Вот спасибо, даже не знал о таком :-)
буду активно пользоваться.
3 июл 12, 01:07    [12807154]     Ответить | Цитировать Сообщить модератору
 Re: Размер локальных дисков на сервере  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
В итоге для решения задачи сделано следующее, может кому пригодится:

Создаем функцию:

+

CREATE function [dbo].[af_DrivesInfo] ()
returns @Drives table (Drive char(1), TotalSize bigint, AvailableSpace bigint)
as begin
/*
	select getdate()
	select Drive, 
		TotalSize = cast(TotalSize/1024./1024./1024. as decimal(10,2)), 
		AvailableSpace = cast(AvailableSpace/1024./1024./1024. as decimal(10,2)) 
		from dbo.af_DrivesInfo()
	select getdate()
*/
	declare @hr integer, @fso integer, @objDrive integer, @Drive char(1), @DriveInt int, 
		@DriveType int, @TotalSizeC varchar(1000), @AvailableSpaceC varchar(18), @TotalSize bigint, @AvailableSpace bigint

	exec @hr = sp_OACreate 'Scripting.FileSystemObject', @fso output

	set @DriveInt = 67

	while @DriveInt <= 90 begin
		set @Drive = CHAR(@DriveInt)
		set @TotalSize = null
		set @AvailableSpace = null

		exec @hr = sp_OAMethod @fso, 'GetDrive', @objDrive out, @Drive
		if @hr = 0 begin
			exec @hr = sp_OAGetProperty @objDrive,'DriveType', @DriveType output
			if @DriveType = 2 begin
				exec @hr = sp_OAGetProperty @objDrive, 'TotalSize', @TotalSizeC output
				exec @hr = sp_OAGetProperty @objDrive, 'AvailableSpace', @AvailableSpaceC output

				insert @Drives (Drive, TotalSize, AvailableSpace)
				values (@Drive, convert(bigint, @TotalSizeC), convert(bigint, @AvailableSpaceC))
			end
			exec sp_OADestroy @objDrive
		end

		set @DriveInt = @DriveInt + 1
	end

	exec sp_OADestroy @fso

	return
end



далее создаем процедуру:
+

CREATE procedure [dbo].[sp_diskalert]
@RCPT VARCHAR(500), --Кому отсылать 
@PROCENT FLOAT --Лимит в процентах всего места от свободного (0.1-0.9)

AS
BEGIN
SET NOCOUNT ON
/*
CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)

INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives
*/

--используя функцию заполняем времянку

select Drive as DRVLETTER,( TotalSize/1024/1024) as TOTALDRVSRACE, (AvailableSpace /1024/1024) as DRVSPACE
into #T1
from  dbo.af_DrivesInfo()

--генерируем сообщение

IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@RCPT) > 0 --CHECK THERE IS SOME DATA AND A RECIPIENT
BEGIN
DECLARE @MSG VARCHAR(400),
@DLETTER VARCHAR(5),
@DSPACE INT

SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE <  TOTALDRVSRACE*@PROCENT
ORDER BY DRVLETTER ASC)

SET @DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @DLETTER)

SET @MSG =convert(varchar(10), getdate(), 104) + ' ' + convert(varchar(10), getdate(), 108) + ' On ' + @@SERVERNAME + ' ' + @DLETTER + ' less  ' + CONVERT(VARCHAR,@DSPACE) --PUT THE VARS INTO A MSG
+ 'MB!!!'-- + CHAR(13) + CHAR(10)


WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < TOTALDRVSRACE*@PROCENT AND DRVLETTER > @DLETTER) > 0
BEGIN	 --LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE	
SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 
WHERE DRVSPACE < TOTALDRVSRACE*@PROCENT
AND DRVLETTER > @DLETTER 
ORDER BY DRVLETTER ASC)

SET @DSPACE = (SELECT DRVSPACE FROM #T1 
WHERE DRVLETTER = @DLETTER)
SET @MSG = @MSG + 'On local disk ' + @DLETTER + ' free space is at ' + CONVERT(VARCHAR,@DSPACE) + 'MB' 
+ CHAR(13) + CHAR(10)
END



--посылаем сообщение

IF CHARINDEX('@',@RCPT) > 0 --THERE IS AN @ SYMBOL IN THE RECIPIENT - SEND EMAIL
BEGIN
DECLARE @EMAIL VARCHAR(600)


--=====================================================

SET @EMAIL = 'EXEC msdb.dbo.sp_send_dbmail
@profile_name = admin, 
@recipients = ''' + @RCPT + ''', 
@body = ''' + @MSG + ''', 
@subject = ''!! LOW FREE DISK SPACE ON ' + @@SERVERNAME + ' !!'''
EXEC (@EMAIL)
END


END

DROP TABLE #T1

END



ну и создаем джоб который раз в какое то время запускает процедуру.
5 июл 12, 10:17    [12820351]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить