Автозбір даних у файлах баз даних і логічних дисках операційної системи MS SQL Server

Передмова
Адміністратора баз даних важливо знати коли закінчиться місце на диску. І щоб цього не робити в ручну на кожному сервері, краще цей процес автоматизувати.
У даній статті наведу реалізацію автоматичного щоденного збору інформації про логічних дисках у файлах баз даних.

Рішення
Алгоритм:
1) створити таблиці для зберігання інформації:
1.1) для файлів баз даних:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

CREATE TABLE [srv].[DBFile](
[DBFile_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Server] [nvarchar](255) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Drive] [nvarchar](10) NOT NULL,
[Physical_Name] [nvarchar](255) NOT NULL,
[Ext] [nvarchar](255) NOT NULL,
[Growth] [int] NOT NULL,
[IsPercentGrowth] [int] NOT NULL,
[DB_ID] [int] NOT NULL,
[DB_Name] [nvarchar](255) NOT NULL,
[SizeMb] [float] NOT NULL,
[DiffSizeMb] [float] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
[UpdateUTCdate] [datetime] NOT NULL,
[File_ID] [int] NOT NULL,
CONSTRAINT [PK_DBFile] PRIMARY KEY CLUSTERED 
(
[DBFile_GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [srv].[DBFile] ADD CONSTRAINT [DF_DBFile_DBFile_GUID] DEFAULT (newid()) FOR [DBFile_GUID]
GO

ALTER TABLE [srv].[DBFile] ADD CONSTRAINT [DF_DBFile_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

ALTER TABLE [srv].[DBFile] ADD CONSTRAINT [DF_DBFile_UpdateUTCdate] DEFAULT (getutcdate()) FOR [UpdateUTCdate]
GO

1.2) для логічних дисків:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

CREATE TABLE [srv].[Drivers](
[Driver_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Server] [nvarchar](255) NOT NULL,
[Name] [nvarchar](8) NOT NULL,
[TotalSpace] [float] NOT NULL,
[FreeSpace] [float] NOT NULL,
[DiffFreeSpace] [float] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
[UpdateUTCdate] [datetime] NOT NULL,
CONSTRAINT [PK_Drivers] PRIMARY KEY CLUSTERED 
(
[Driver_GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_Driver_GUID] DEFAULT (newid()) FOR [Driver_GUID]
GO

ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_Server] DEFAULT (@@servername) FOR [Server]
GO

ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_TotalSpace] DEFAULT ((0)) FOR [TotalSpace]
GO

ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_FreeSpace] DEFAULT ((0)) FOR [FreeSpace]
GO

ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_DiffFreeSpace] DEFAULT ((0)) FOR [DiffFreeSpace]
GO

ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_UpdateUTCdate] DEFAULT (getutcdate()) FOR [UpdateUTCdate]
GO

Таблицю з логічними дисками потрібно заповнити заздалегідь наступним чином:
назва сервера — мітка тому (MyServer — C:).
2) створити необхідне уявлення для збору інформації про файли баз даних:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

CREATE view [inf].[ServerDBFileInfo] as
SELECT @@Servername AS Server ,
File_id ,--Ідентифікатор файлу в базі даних. Основне значення file_id завжди дорівнює 1
Type_desc ,--Опис типу файлу
Name as [FileName] ,--Логічне ім'я файлу у базі даних
LEFT(Physical_Name, 1) AS Drive ,--Мітка тому, де розташований файл БД
Physical_Name ,--Повне ім'я файлу в операційній системі
RIGHT(physical_name, 3) AS Ext ,--Розширення файлу
Size as CountPage, --Поточний розмір файлу сторінках по 8 КБ
round((cast(Size*8 as float))/1024,3) as SizeMb, --Розмір файлу МБ
Growth, --Приріст
is_percent_growth, --Ознака процентного приросту
database_id,
DB_Name(database_id) as [DB_Name]
FROM sys.master_files--database_files
GO

Тут використовується системне уявлення sys.master_files
3) створити збережену процедуру, яка повертає інформацію по логічному диску:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

create procedure [srv].[sp_DriveSpace] 
@DrivePath varchar(1024) --пристрій (можна передати мітку томи 'C:')
, @TotalSpace float output --всього ємність в байтах
, @FreeSpace float output --вільного простору в байтах
as
begin

DECLARE @int fso
, @Drive int
, @DriveName varchar(255)
, @Folder int
, @Int Drives
, @source varchar(255)
, @desc varchar(255)
, @int ret
, @Object int
-- Створюємо об'єкт файлової системи
exec @ret = sp_OACreate 'Scripting.FileSystemObject', @fso output
set @Object = @fso
if @ret != 0
goto ErrorInfo

-- Отримуємо папку на заданому шляху
exec @ret = sp_OAmethod @fso, 'GetFolder', @Folder output, @DrivePath 
set @Object = @fso
if @ret != 0
goto ErrorInfo

-- Отримуємо пристрій
exec @ret = sp_OAmethod @Folder, 'Drive', @Drive output
set @Object = @Folder
if @ret != 0
goto ErrorInfo

-- Визначаємо повний розмір пристрою
exec @ret = sp_OAGetProperty @Drive, 'TotalSize', @TotalSpace output
set @Object = @Drive
if @ret != 0
goto ErrorInfo

-- Визначаємо вільне місце не пристрої
exec @ret = sp_OAGetProperty @Drive, 'AvailableSpace', @FreeSpace output
set @Object = @Drive
if @ret != 0
goto ErrorInfo

DestroyObjects:
if @Folder is not null
exec sp_OADestroy @Folder
if @Drive is not null
exec sp_OADestroy @Drive
if @fso is not null
exec sp_OADestroy @fso

return (@ret)

ErrorInfo:
exec sp_OAGetErrorInfo @Object, @source output, @desc output
print 'Source error:' + isnull( @source, 'n/a' ) + char(13) + 'Description:' + isnull( @desc, 'n/a' )
goto DestroyObjects;
end
GO

Дана збережена процедура була взята з наступної статті Обсяг дисків на T-SQL
4) створити збережену процедуру для збору інформації:
4.1) для файлів баз даних:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

CREATE PROCEDURE [srv].[MergeDBFileInfo]
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;merge [srv].[DBFile] as f
using [inf].[ServerDBFileInfo] as ff
on f.File_ID=ff.File_ID and f.DB_ID=ff.[database_id] and f.[Server]=ff.[Server]
when matched then
update set UpdateUTcDate = getUTCDate()
,[Name] = ff.[FileName] 
,[Drive] = ff.[Drive] 
,[Physical_Name] = ff.[Physical_Name] 
,[Ext] = ff.[Ext] 
,[Growth] = ff.[Growth] 
,[IsPercentGrowth] = ff.[is_percent_growth] 
,[SizeMb] = ff.[SizeMb] 
,[DiffSizeMb] = round(ff.[SizeMb]-f.[SizeMb],3) 
when not matched by target then
insert (
[Server]
,[Name]
,[Drive]
,[Physical_Name]
,[Ext]
,[Growth]
,[IsPercentGrowth]
,[DB_ID]
,[DB_Name]
,[SizeMb]
,[File_ID]
,[DiffSizeMb]
)
values (
ff.[Server]
,ff.[FileName]
,ff.[Drive]
,ff.[Physical_Name]
,ff.[Ext]
,ff.[Growth]
,ff.[is_percent_growth]
,ff.[database_id]
,ff.[DB_Name]
,ff.[SizeMb]
,ff.[File_id]
,0
)
when not matched by source and f.[Server]=@@SERVERNAME then delete;
END

GO

4.2) для логічних дисків:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

CREATE PROCEDURE [srv].[MergeDriverInfo]
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

declare @Drivers table (
[Server] nvarchar(255),
Name nvarchar(8),
TotalSpace float,
FreeSpace float,
DiffFreeSpace float NULL
);
insert into @Drivers (
[Server],
Name,
TotalSpace,
FreeSpace
)
select [Server],
Name,
TotalSpace,
FreeSpace
from srv.Drivers
where [Server]=@@SERVERNAME;

declare @TotalSpace float;
declare @FreeSpace float;
declare @DrivePath nvarchar(8);

while(exists(select top(1) 1 from @Drivers where DiffFreeSpace is null))
begin
select top(1)
@DrivePath=Name
from @Drivers
where DiffFreeSpace is null;

exec srv.sp_DriveSpace @DrivePath = @DrivePath
, @TotalSpace = @TotalSpace out
, @FreeSpace = @FreeSpace out;

update @Drivers
set TotalSpace=@TotalSpace
,FreeSpace=@FreeSpace
,DiffFreeSpace=case when FreeSpace>0 then round(FreeSpace-@FreeSpace,3) 0 else end
where Name=@DrivePath;
end

;merge [srv].[Drivers] as d
using @Drivers as dd
on d.Name=dd.Name and d.[Server]=dd.[Server]
when matched then
update set UpdateUTcDate = getUTCDate()
,[TotalSpace] = dd.[TotalSpace] 
,[FreeSpace] = dd.[FreeSpace] 
,[DiffFreeSpace]= dd.[DiffFreeSpace];
END

GO

5) створити подання для виведення інформації:
5.1) для файлів баз даних:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

create view [srv].[vDBFiles] as
SELECT [DBFile_GUID]
,[Server]
,[Name]
,[Drive]
,[Physical_Name]
,[Ext]
,[Growth]
,[IsPercentGrowth]
,[DB_ID]
,[File_ID]
,[DB_Name]
,[SizeMb]
,[DiffSizeMb]
,round([SizeMb]/1024,3) as [SizeGb]
,round([DiffSizeMb]/1024,3) as [DiffSizeGb]
,round([SizeMb]/1024/1024,3) as [SizeTb]
,round([DiffSizeMb]/1024/1024,3) as [DiffSizeTb]
,round([DiffSizeMb]/([SizeMb]/100), 3) as [DiffSizePercent]
,[InsertUTCDate]
,[UpdateUTCdate]
FROM [srv].[DBFile];

GO

5.2) для логічних дисків:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

create view [srv].[vDrivers] as
select
[Driver_GUID]
,[Server]
,[Name]
,[TotalSpace] as [TotalSpaceByte]
,[FreeSpace] as [FreeSpaceByte]
,[DiffFreeSpace] as [DiffFreeSpaceByte]
,round([TotalSpace]/1024, 3) as [TotalSpaceKb]
,round([FreeSpace]/1024, 3) as [FreeSpaceKb]
,round([DiffFreeSpace]/1024, 3) as [DiffFreeSpaceKb]
,round([TotalSpace]/1024/1024, 3) as [TotalSpaceMb]
,round([FreeSpace]/1024/1024, 3) as [FreeSpaceMb]
,round([DiffFreeSpace]/1024/1024, 3) as [DiffFreeSpaceMb]
,round([TotalSpace]/1024/1024/1024, 3) as [TotalSpaceGb]
,round([FreeSpace]/1024/1024/1024, 3) as [FreeSpaceGb]
,round([DiffFreeSpace]/1024/1024/1024, 3) as [DiffFreeSpaceGb]
,round([TotalSpace]/1024/1024/1024/1024, 3) as [TotalSpaceTb]
,round([FreeSpace]/1024/1024/1024/1024, 3) as [FreeSpaceTb]
,round([DiffFreeSpace]/1024/1024/1024/1024, 3) as [DiffFreeSpaceTb]
,round([FreeSpace]/([TotalSpace]/100), 3) as [FreeSpacePercent]
,round([DiffFreeSpace]/([TotalSpace]/100), 3) as [DiffFreeSpacePercent]
,[InsertUTCDate]
,[UpdateUTCdate]
FROM [srv].[Drivers]
GO

6) створити завдання Агента і запускати раз на добу:
USE [ІМ'Я_БАЗИ_ДАНИХ];
GO

exec srv.MergeDBFileInfo;
exec srv.MergeDriverInfo;

7) зібрати всю отриману інформацію з серверів (також можна через завдання агента або іншим іншим способом)
8) створити збережену процедуру для формування звіту і відправлення адміністраторам. Реалізувати можна по різному. Тому наведу лише приклад:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
GO

CREATE PROCEDURE [srv].[GetHTMLTableShortInfoDrivers]
@body nvarchar(max) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

declare @tbl table (
Driver_GUID uniqueidentifier
,[Name] nvarchar(255)
,[TotalSpaceGb] float
,[FreeSpaceGb] float
,[DiffFreeSpaceMb] float
,[FreeSpacePercent] float
,[DiffFreeSpacePercent] float
,UpdateUTCDate datetime
,[Server] nvarchar(255)
ID int identity(1,1)
);

declare
@Driver_GUID uniqueidentifier
,@Name nvarchar(255)
,@TotalSpaceGb float
,@FreeSpaceGb float
,@DiffFreeSpaceMb float
,@FreeSpacePercent float
,@DiffFreeSpacePercent float
,@Datetime UpdateUTCDate
,@Server nvarchar(255)
,@ID int;

insert into @tbl(
Driver_GUID 
,[Name] 
,[TotalSpaceGb] 
,[FreeSpaceGb] 
,[DiffFreeSpaceMb] 
,[FreeSpacePercent] 
,[DiffFreeSpacePercent] 
,UpdateUTCDate 
,[Server] 
)
select Driver_GUID 
,[Name] 
,[TotalSpaceGb] 
,[FreeSpaceGb] 
,[DiffFreeSpaceMb] 
,[FreeSpacePercent] 
,[DiffFreeSpacePercent] 
,UpdateUTCDate 
,[Server]
from srv.vDrivers
where [DiffFreeSpacePercent]<=-5
or [FreeSpacePercent]<=15
order by [Server] asc, [Name] asc;

if(exists(select top(1) 1 from @tbl))
begin
set @body='У ході аналізу були виявлені наступні носії інформації, у яких або вільного об'єму залишилося менше 15%, або вільне місце зменшується понад 5% за день:<br><br>'+'<TABLE BORDER=5>';

set @body=@body+'<TR>';

set @body=@body+'<TD>';
set @body=@body+'№ п/п';
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+'ГУИД';
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+'СЕРВЕР';
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+'ТЕ';
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+'ЄМНІСТЬ, ГБ.';
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+'ВІЛЬНО, ГБ.';
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+'ЗМІНА ВІЛЬНОГО МІСЦЯ, МБ.';
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+'ВІЛЬНО, %';
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+'ЗМІНА ВІЛЬНОГО МІСЦЯ, %';
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+'UTC ЧАС ВИЯВЛЕННЯ';
set @body=@body+'</TD>';

set @body=@body+'</TR>';

while((select top 1 1 from @tbl)>0)
begin
set @body=@body+'<TR>';

select top 1
@Driver_GUID = Driver_GUID 
,@Name = Name 
,@TotalSpaceGb = TotalSpaceGb 
,@FreeSpaceGb = FreeSpaceGb 
,@DiffFreeSpaceMb = DiffFreeSpaceMb 
,@FreeSpacePercent = FreeSpacePercent 
,@DiffFreeSpacePercent = DiffFreeSpacePercent 
,@UpdateUTCDate = UpdateUTCDate 
,@Server = [Server] 
,@ID = [ID] 
from @tbl;

set @body=@body+'<TD>';
set @body=@body+cast(@ID as nvarchar(max));
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+cast(@Driver_GUID as nvarchar(255));
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+coalesce(@Server");
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+coalesce(@Name,");
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+cast(@TotalSpaceGb as nvarchar(255));
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+cast(@FreeSpaceGb as nvarchar(255));
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+cast(@DiffFreeSpaceMb as nvarchar(255));
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+cast(@FreeSpacePercent as nvarchar(255));
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+cast(@DiffFreeSpacePercent as nvarchar(255));
set @body=@body+'</TD>';

set @body=@body+'<TD>';
set @body=@body+rep.GetDateFormat(@UpdateUTCDate, default)+' '+rep.GetTimeFormat(@UpdateUTCDate, default);
set @body=@body+'</TD>';

delete from @tbl
where ID=@ID;

set @body=@body+'</TR>';
end

set @body=@body+'</TABLE>';

set @body=@body+'<br><br>Для більш детальної інформації зверніться до подання SRV.srv.vDrivers<br><br>Для перегляду інформації файлів баз даних, зверніться до подання ІМ'Я_БАЗИ_ДАНИХ.srv.vDBFiles';
end
END

GO

Дана збережена процедура формує HTML-звіт про логічних дисках, у яких або вільного об'єму залишилося менше 15%, або вільне місце зменшується понад 5% за день. Останнє свідчить про дивну активності записів (хтось занадто часто і багато пише на даний диск). Перше ж свідчить про те, що потрібно розібратися-небудь диск пора збільшувати або видалити невикористовуване зайняте місце на логічному диску, або почистити і стиснути файли лода, а також інформаційних та інших таблиць.
Результат
У даній статті було розглянуто приклад реалізації системи щоденного автоматичного збору інформації про локальних дисках і файлах баз даних. Дана інформація дозволяє дізнатися заздалегідь на якому диску вільного місця стає все менше, а також які файли баз даних значно зростають. Це дозволяє запобігти випадок, коли місця на диску не залишиться, а також виявити причину того, чому якийсь процес займає значну частину місця на диску своїми записами і т. д.
Джерела:
» sys.master_files
» Обсяг дисків на T-SQL
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.