T-SQL – Monitoring DiskSpace of Multiple Servers

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

  1. Enable XP_CMDShell 
  2. Enable Ole automation on all servers

Step by Step procedures to be done on centralized server is as follows

  1. Enable XP_CMDShell 
  2. List all SQL Instances in c:\Server.txt
  3. Enable ole automation on listed servers
  4. Table Creation [TLOG_SpaceUsageDetails]
  5. Copy and Paste T-SQL script in C:\SpaceCheck.sql
  6. Execute dynamic sqlcmd from SSMS
  7. 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' 
Image
 
 
Ouptut:-
Image
 
Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

3 Responses to T-SQL – Monitoring DiskSpace of Multiple Servers

  1. Mohan Kumar says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s