This post talks about step by step approach to capture the disk space using T-SQL. This is a request from one of SQL enthusiast. The requirement is to do with T-SQL to monitor disk space of remote servers.
Pre-requisites are
- Enable XP_CMDShell
- Enable Ole automation on all servers
Step by Step procedures to be done on centralized server is as follows
- Enable XP_CMDShell
- List all SQL Instances in c:\Server.txt
- Enable ole automation on listed servers
- Table Creation [TLOG_SpaceUsageDetails]
- Copy and Paste T-SQL script in C:\SpaceCheck.sql
- Execute dynamic sqlcmd from SSMS
- select the output by querying TLOG_SpaceUsageDetails
The details are as follows
Enable XP_CMDSHELL on Centralized Server
/************************* --Enable XP_CMDShell -SSMS *****************************/ sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'xp_cmdShell', 1; GO RECONFIGURE; GO
Enable Ole Automation in all the server from where you wanted to collected the data
/************************* --Enable Ole Automation on all the listed servers –SSMS. In this example ABC,DEF,EFG *****************************/ sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO
/************************* --List all SQL Instances in c:\Server.txt *****************************/ ABC DEF EFG
Centralized Server – Table Creation
/************************* --Table Creation --SSMS *****************************/ CREATE TABLE [dbo].[TLOG_SpaceUsageDetails]( [space_id] [int] IDENTITY(1,1) NOT NULL, [servername] [varchar](100) NULL, [LogDate] [varchar](10) NULL, [drive] [char](1) NULL, [FreeSpaceMB] [int] NULL, [TotalSizeMB] [int] NULL, [percentageOfFreeSpace] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[TLOG_SpaceUsageDetails] ADD DEFAULT (CONVERT([varchar](10),getdate(),(112))) FOR [LogDate]
Centralized Server – Create a file spacecheck.sql and copy and paste the below code
DECLARE @hr INT ,
@fso INT,
@drive CHAR(1),
@odrive INT,
@TotalSize VARCHAR(20),
@MB NUMERIC ,
@FreeSpace INT,
@free INT,
@RowId_1 INT,
@LoopStatus_1 SMALLINT,
@TotalSpace VARCHAR(10),
@Percentage VARCHAR(3),
@drive1 varchar(2),
@TotalSizeMB varchar(10),
@FreeSpaceMB varchar(10),
@percentageOfFreeSpace varchar(10),
@RowId_2 INT,
@LoopStatus_2 SMALLINT,
@DML nvarchar(4000)
SET NOCOUNT ON
-----------------------------------------------------------------------------------------------
--Table to Store Drive related information
-----------------------------------------------------------------------------------------------
CREATE TABLE #drives
(
id INT IDENTITY(1,1) PRIMARY KEY,
drive CHAR(1),
FreeSpaceMB INT ,
TotalSizeMB INT NULL,
percentageOfFreeSpace INT
)
-----------------------------------------------------------------------------------------------
--Inserting the output of xp_fixeddrives to #SpaceSize Table
-----------------------------------------------------------------------------------------------
INSERT #drives(drive,FreeSpaceMB) EXEC master.dbo.xp_fixeddrives
-----------------------------------------------------------------------------------------------
--Using the sp_OACreate, sp_OAMethod and sp_OAGetProperty system stored procedures to create Ole Automation (ActiveX) applications that can do everything an ASP script can do*/
--Creates an instance of the OLE object
-----------------------------------------------------------------------------------------------
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
SET @MB = 1048576
SET @RowId_1 = 1
SET @LoopStatus_1 = 1
-----------------------------------------------------------------------------------------------
--To Get Drive total space
-----------------------------------------------------------------------------------------------
WHILE (@LoopStatus_1 <> 0) BEGIN
SELECT
@drive=drive,
@FreeSpace=FreeSpaceMB
FROM
#drives
WHERE
( ID = @RowId_1 )
IF ( @@ROWCOUNT = 0 )
BEGIN
SET @LoopStatus_1 = 0
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
EXEC @hr =sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
UPDATE #drives SET TotalSizeMB=@TotalSize/@MB
WHERE
drive=@drive
UPDATE #drives SET percentageOfFreeSpace=(@FreeSpace/(TotalSizeMB*1.0))*100.0
WHERE drive=@drive
END
SET @RowId_1 = @RowId_1 + 1
END
SELECT @RowId_2=1,@LoopStatus_2=1
--SELECT @@servername,drive,TotalSizeMB,FreeSpaceMB,percentageOfFreeSpace FROM #drives
WHILE (@LoopStatus_2 <> 0) BEGIN
SET @DML=''
SELECT
@drive1=drive,
@FreeSpace=FreeSpaceMB,
@TotalSizeMB=TotalSizeMB,
@FreeSpaceMB=FreeSpaceMB,
@percentageOfFreeSpace=percentageOfFreeSpace
FROM
#drives
WHERE
( ID = @RowId_2 )
IF ( @@ROWCOUNT = 0 )
BEGIN
SET @LoopStatus_2 = 0
END
ELSE
BEGIN
SET @DML=@DML+ 'insert into TLOG_SpaceUsageDetails(servername,drive,TotalSizeMB,FreeSpaceMB,percentageOfFreeSpace)values('+''''+@@servername+''''+','+''''+@drive1+''''+','+''''+@TotalSizeMB+''''+','+''''+@FreeSpaceMB+''''+','+''''+@percentageOfFreeSpace+'''' +')'
END
PRINT @DML
SET @RowId_2 = @RowId_2 + 1
END
drop table #drives
Centralized Server - Execute the below code in SSMS – replace below code to valid centralized server name.
SQL
MASTER..XP_CMDSHELL 'for /f %j in (c:\server.txt ) do sqlcmd -S %j -i c:\SpaceCheck.sql -E > c:\SpaceDetails.sql' GO MASTER..XP_CMDSHELL 'sqlcmd -S <CentralizedServerName> -i c:\spacedetails.sql -E'
Ouptut:-
