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:-
Prashanth Jayaram 
HI Prashanth
I have 2 questions:
1. Where do we provide remote server credentials (username / password) ?
2. Will this script work if the servers are in different locations and different networks?
Regards
Mohan Kumar VS
LikeLike
Hi Mohan,
Sorry for the late response.
The current script doesn’t have a code to accept the credentials.
You can refer my other article where have used credentials
if the credentials have access and other network is under trusted domain then it’s possible to get the data from different network
Let me know if you need a code for your requirement
–Prashanth
LikeLike
Thanks Prashanth. WIll check and let you know by tomorrow.
Regards
Mohan Kumar VS
LikeLike