Auto-collection of data about database files and logical disks of the operating system in MS SQL Server
Foreword
It is important for the database administrator to know when the disk space will run out. And in order not to do this manually on each server, it is better to automate this process.
In this article I will give an implementation of the automatic daily collection of information about logical disks and database files.
Decision
Algorithm:
1) create a table to store information:
1.1) for database files:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON 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) for logical drives:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON 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 The table with logical disks must be filled in advance as follows:
server name - volume label (MyServer - C :).
2) create the necessary view to collect information about database files:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON 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 It uses the sys.master_files system view .
3) create a stored procedure that returns information on the logical drive:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [srv].[sp_DriveSpace] @DrivePath varchar(1024) -- ( 'C:') , @TotalSpace float output -- , @FreeSpace float output -- as begin DECLARE @fso int , @Drive int , @DriveName varchar(255) , @Folder int , @Drives int , @source varchar(255) , @desc varchar(255) , @ret int , @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 This stored procedure was taken from the following article. T-SQL Disk Size
4) create a stored procedure to collect information:
4.1) for database files:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON 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) for logical drives:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON 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) else 0 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) create views to display information:
5.1) for database files:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON 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) for logical drives:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON 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) create a task in the Agent and run it once a day:
USE [__]; GO exec srv.MergeDBFileInfo; exec srv.MergeDriverInfo; 7) to collect all the information received from the servers (it is also possible through the task of the agent or by other means)
8) create a stored procedure for generating a report and sending it to administrators. You can implement in different ways. Therefore, I will give only an example:
Code
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON 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 ,@UpdateUTCDate datetime ,@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 This stored procedure generates an HTML report on logical disks that have either less than 15% of free space left, or less than 5% of free space per day. The latter indicates the strange activity of the recordings (someone writes too often and on this disc a lot). The first may be due to one of several reasons:
1) it's time to increase the disk
2) it is necessary to remove unused space on the logical disk
3) clean and compress log files, as well as informational and other tables.
Result
This article has reviewed an example of the implementation of the system of daily automatic collection of information about local disks and database files. This information allows you to know in advance on which disk free space is getting smaller, as well as which database files are growing significantly. This allows you to prevent the case when there is no disk space left, and also to identify the reason why a process takes up a significant part of the disk space with its own records, etc.
Sources:
')
Source: https://habr.com/ru/post/314522/
All Articles