Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

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
 

Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...