SQL Server Monitoring Disk Space With WMI And OLE Automation Objects

Introduction

This article talks about various SQL methods to fetch disk usage details and the use of T-SQL in conjunction with invoking OLE automation procedures and WMI class libraries using the SQL Windows Shell interface.

The step by step details about the OLE automation and WMI Query approach to capture disk space and the use of SQL cmd with a looping construct, data storage for querying, and reporting are discussed below.

Method 1 Using OLE Automation and Extended SP

In this method, OLE automation and xp_cmdshell configuration components are enabled on the SQL server. The sp_OACreate creates an instance of a Scripting.FileSystemObject OLE class. The sp_OAMethod gets a unique ID for each volume attached to filesystem object and sp_OAGetProperty retrieves properties of each drive and filesystem.

The reason for enabling xp_cmdshell is to execute xp_fixeddrives, an extended stored procedure. The output is stored in a temporary table. The records are traversed with a while loop construct for the %free space calculation. The sp_OAMethod requires a drive letter as its input. The below SQL has a hard coded value for the F drive. You can change the SQL as per your requirements.

  1. DECLARE @hr INT,  
  2. @fso INT,  
  3. @drive CHAR(1) = 'F'/* To demonstrate have hard coded the drive letter to F */  
  4.     @odrive INT,  
  5.     @TotalSize BIGINT,  
  6.     @freespace BIGINT,  
  7.     @AvailableSpace BIGINT,  
  8.     @DriveCollection INT,  
  9.     @DriveCount INT,  
  10.     @FileSystemInstance INT,  
  11.     @volumeName varchar(128),  
  12.     @IsReady VARCHAR(5)  
  13. EXEC sp_OACreate 'Scripting.FileSystemObject', @fso OUT  
  14. EXEC sp_OAMethod @fso, 'GetDrive', @odrive OUT, @drive  
  15. EXEC sp_OAGetProperty @odrive, 'TotalSize', @TotalSize OUT  
  16. EXEC sp_OAGetProperty @odrive, 'freespace', @freespace OUT  
  17. EXEC sp_OAGetProperty @odrive, 'AvailableSpace', @AvailableSpace OUT  
  18. EXEC sp_OAGetProperty @fso, 'Drives', @DriveCollection OUT  
  19. EXEC sp_OAGetProperty @DriveCollection, 'Count', @DriveCount OUT  
  20. EXEC sp_OAGetProperty @odrive, 'VolumeName', @volumeName OUT  
  21. EXEC sp_OAGetProperty @odrive, 'isReady', @IsReady OUT  
  22. SELECT  
  23. @DriveCount driveCount, @Drive DriveLetter, (@AvailableSpace / 1048576) / 1024 FreeSpace,  
  24. @volumeName Volumne,  
  25. @IsReady DriveStatus, (@TotalSize / 1048576) / 1024.00 TotalSize, (@freespace / 1048576) / 1024.00 FreeSpace  
Diagram

Data Flow Diagram

This process requires you to enable OLE automation procedures and xp_cmdshell SQL Windows Shell components on all the servers. The servers are traversed one by one via batch scripting, along with SQL cmd, and the result is stored in a shared file. This file has a collection of insert SQL statements. Later, the insert statements are executed on the centralized server. The repository can be queried, used for forecasting, capacity planning, and reporting.

Diagram

OLE Capturing Disk Space of Multiple Servers

The section describes the requirement and briefs about every configuration required for successful execution of the code. The major issue is enabling OLE automation procedures and xp_cmdshell on all the servers. There is a risk of exposing SQL server to threats. The workaround is adding the configure and de-configure SQL statements in the SQL file. Enable the configuration value in the beginning of the code and disable it at the end.

Prerequisites

We can check the disk space by executing a T-SQL script using xp_cmdshell from SSMS. In order to do this, you need to make sure that xp_cmdshell is enabled on the SQL instance. You can execute the below script to check and enable xp_cmdshell . To enable xp_cmdshell you must have at least the ALTER SETTINGS server-level permission.

 

  1. Enable xp_cmdshell on Centralized Server.
  2. Enable OLE automation on all the listed servers.
  3. Shared path for SQL files.
  4. Sharex path for output filse to prepare insert statements.
  5. Requires membership in the sysadmin fixed server role.

Step By Step Details

Below are the steps to store the data in a central repository.

  1. Enable xp_cmdshell.
  2. List all Servers in c\Server.txt.
  3. Enable OLE automation.
  4. Table Creation [TLOG_SpaceUsageDetails].
  5. Save T-SQL script in SpaceCheck.sql.
  6. Execute dynamic SQL cmd from SSMS.
  7. Select the output by querying TLOG_SpaceUsageDetails.

Enable xp_cmdshell

The xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.

  1. sp_configure 'show advanced options', 1;  
  2. GO  
  3. RECONFIGURE;  
  4. GO  
  5. sp_configure 'xp_cmdShell', 1;  
  6. GO  
  7. RECONFIGURE;  
  8. GO  
Enable OLE Automation

Use the OLE Automation Procedures option to specify whether OLE Automation objects can be instantiated within Transact-SQL batches
  1. sp_configure 'show advanced options', 1;  
  2. GO  
  3. RECONFIGURE;  
  4. GO  
  5. sp_configure 'Ole Automation Procedures', 1;  
  6. GO  
  7. RECONFIGURE;  
  8. GO  
List Servers in a Text File

List all the servers in a text file (c\server.txt) and enable OLE automation across all servers. The other way to get around this problem is to include the sp_configure commands in the SQL file and take it out after it's been executed across all the servers

ABC
DEF
EFG

Create SQL Table

Create TLOG_SpaceUsageDetails on the centralized server
  1. CREATE TABLE[dbo].[TLOG_SpaceUsageDetails](  
  2.     [ID][int] IDENTITY(1, 1) NOT NULL, [ServerName][VARCHAR](100) NULL, [LogDate][VARCHAR](10) DEFAULT(CONVERT([varchar](10), getdate(), (112))), [Drive][CHAR](3) NULL, [FreeSpaceGB][INT] NULL, [TotalSizeGB][INT] NULL, [percentageOfFreeSpace] DECIMAL(5, 2) NULL) ON[PRIMARY]  
Create SQL File

Save the below content to a SQL file and place it on the shared path so that SQL cmd can read the file while traversing across the listed Servers. For our example, the content is saved under Spacecheck.sql on \\abcd\hq\. The full path of the file is going to be \\abcd\hq\spacecheck.sql
  1. DECLARE @hr INT,  
  2. @fso INT,  
  3. @drive CHAR(1),  
  4.     @odrive INT,  
  5.     @TotalSize VARCHAR(20),  
  6.     @MB NUMERIC,  
  7.     @FreeSpace INT,  
  8.     @free INT,  
  9.     @RowId_1 INT,  
  10.     @LoopStatus_1 SMALLINT,  
  11.     @TotalSpace VARCHAR(10),  
  12.     @Percentage VARCHAR(3),  
  13.     @drive1 varchar(2),  
  14.     @TotalSizeMB varchar(10),  
  15.     @FreeSpaceMB varchar(10),  
  16.     @percentageOfFreeSpace varchar(10),  
  17.     @RowId_2 INT,  
  18.     @LoopStatus_2 SMALLINT,  
  19.     @DML nvarchar(4000)  
  20. SET NOCOUNT ON  
  21. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  22. --Table to Store Drive related information  
  23. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  24. CREATE TABLE# drives(id INT IDENTITY(1, 1) PRIMARY KEY, drive CHAR(1), FreeSpaceMB INT, TotalSizeMB INT NULL, percentageOfFreeSpace INT)  
  25.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  26.     --Inserting the output of xp_fixeddrives to# SpaceSize Table  
  27.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  28.     INSERT# drives(drive, FreeSpaceMB) EXEC master.dbo.xp_fixeddrives  
  29.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  30.     --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 */   
  31.             --Creates an instance of the OLE object  
  32.             -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  33.             EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT  
  34.         SET @MB = 1048576  
  35.     SET @RowId_1 = 1  
  36. SET @LoopStatus_1 = 1  
  37.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  38.     --To Get Drive total space  
  39.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  40.     WHILE(@LoopStatus_1 < > 0) BEGIN  
  41. SELECT  
  42. @drive = drive,  
  43.     @FreeSpace = FreeSpaceMB  
  44. FROM# drives  
  45. WHERE(ID = @RowId_1)  
  46. IF(@ @ROWCOUNT = 0)  
  47. BEGIN  
  48. SET @LoopStatus_1 = 0  
  49. END  
  50. ELSE  
  51. BEGIN  
  52. EXEC @hr = sp_OAMethod @fso, 'GetDrive', @odrive OUT, @drive  
  53. EXEC @hr = sp_OAGetProperty @odrive, 'TotalSize', @TotalSize OUT  
  54. UPDATE# drives SET TotalSizeMB = @TotalSize / @MB  
  55. WHERE  
  56. drive = @drive  
  57. UPDATE# drives SET percentageOfFreeSpace = (@FreeSpace / (TotalSizeMB * 1.0)) * 100.0  
  58. WHERE drive = @drive  
  59. END  
  60. SET @RowId_1 = @RowId_1 + 1  
  61. END  
  62. SELECT @RowId_2 = 1, @LoopStatus_2 = 1  
  63.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  64.     --To prepare insert statement  
  65.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
  66.     WHILE(@LoopStatus_2 < > 0) BEGIN  
  67. SET @DML = ''  
  68. SELECT  
  69. @drive1 = drive,  
  70.     @FreeSpace = FreeSpaceMB,  
  71.     @TotalSizeMB = TotalSizeMB,  
  72.     @FreeSpaceMB = FreeSpaceMB,  
  73.     @percentageOfFreeSpace = percentageOfFreeSpace  
  74. FROM# drives  
  75. WHERE(ID = @RowId_2)  
  76. IF(@ @ROWCOUNT = 0)  
  77. BEGIN  
  78. SET @LoopStatus_2 = 0  
  79. END  
  80. ELSE  
  81. BEGIN  
  82. SET @DML = @DML + 'insert into TLOG_SpaceUsageDetails(servername,drive,TotalSizeMB,FreeSpaceMB,percentageOfFreeSpace)values(' + ''  
  83. '' + @ @servername + ''  
  84. '' + ',' + ''  
  85. '' + @drive1 + ''  
  86. '' + ',' + ''  
  87. '' + @TotalSizeMB + ''  
  88. '' + ',' + ''  
  89. '' + @FreeSpaceMB + ''  
  90. '' + ',' + ''  
  91. '' + @percentageOfFreeSpace + ''  
  92. '' + ')'  
  93. END  
  94. PRINT @DML  
  95. SET @RowId_2 = @RowId_2 + 1  
  96. END  
  97. drop table# drives  
Diagram

Execute SQL

Make sure the SQL file and output path is a shared path. We are going to write a concatenated output to spaceDetails.sql.

The following three parameters are important to loop through all the listed servers.3

 

  • Input server list.
  • Shared path where you can place the query file.
  • Shared output path — the prepared insert statements are written into the file.

 

  1. MASTER..XP_CMDSHELL 'for /f %j in ( f\servers.txt) do sqlcmd -S %j -i "\\share\hq\SpaceCheck.sql" -E >> "\\share\hq\SpaceDetails.sql"'  
  2. GO  
  3. MASTER..XP_CMDSHELL 'sqlcmd -S ABCD -i "\\share\hq\SpaceDetails.sql"'  
Output

The output is selected by querying the TLOG table,

select * from TLOG_SpaceUsageDetails

Diagram

Method 2 Querying WMI Objects

We could also query the WMI Win32_volume class to gather volume names, free space, and total size (capacity) into a temporary table the using xp_cmdshell Windows Shell. Using xp_cmdshell, the PowerShell.exe is being invoked to gather the required details, which are stored in a temporary table for manipulation. You can pass the server name, but by default, it will take the current server name.
  1. DECLARE @svrName VARCHAR(255)  
  2. DECLARE @sql varchar(400)  
  3.     --by  
  4. default it will take the current server name, we can the set the server name as well  
  5. SET @svrName = @ @SERVERNAME  
  6. SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName, ''  
  7.     '') + ' -Class Win32_Volume -Filter '  
  8. 'DriveType = 3'  
  9. ' | select name,capacity,freespace | foreach{$_.name+'  
  10. '|'  
  11. '+$_.capacity/1048576+'  
  12. '%'  
  13. '+$_.freespace/1048576+'  
  14. '*'  
  15. '}"'  
  16. --creating a temporary table  
  17. CREATE TABLE# output(line varchar(255))  
  18.     --inserting disk name, total space and free space value in to temporary table  
  19. INSERT# output EXEC xp_cmdshell @sql  
  20. --script to retrieve the values in GB from PS Script output  
  21. SELECT rtrim(ltrim(SUBSTRING(line, 1, CHARINDEX('|', line) - 1))) as drivename, round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('|', line) + 1, (CHARINDEX('%', line) - 1) - CHARINDEX('|', line)))) as Float) / 1024, 0) as 'capacity(GB)', round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('%', line) + 1, (CHARINDEX('*', line) - 1) - CHARINDEX('%', line)))) as Float) / 1024, 0) as 'freespace(GB)', cast(round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('|', line) + 1, (CHARINDEX('%', line) - 1) - CHARINDEX('|', line)))) as Float) / 1024, 0) / round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('%', line) + 1, (CHARINDEX('*', line) - 1) - CHARINDEX('%', line)))) asFloat) / 1024, 0) as decimal(5, 2)) as '%Free'  
  22. FROM# output  
  23. WHERE line like '[A-Z][]%'  
  24. ORDER by drivename  
  25. --script to drop the temporary table  
  26. DROP TABLE# output  
Diagram

The output of the temp table is then parsed to get the required values using string functions. Using the charindex string function, the index is found for the characters |,* and %. Based on the index, the respective portion of the string is fetched from the main string.

Diagram

The advantage of this method is that xp_cmdshell is enabled only on the centralized machine. You don’t have to enable it across all the servers. Plus, non-SQL database servers' metrics can be gathered, as we are querying WMI class.

Prerequisites

We can check the disk space by executing a T-SQL script using xp_cmdshell from SSMS. In order to do this, you need to make sure that xp_cmdshell is enabled on the SQL instance. You can execute the below script to check and enable XP_Cmdshell. To enable XP_Cmdshell you must have at least the ALTER SETTINGS server-level permission.

 

  1. Enable xp_cmdshell on the centralized server.

Data Flow Diagram

This process requires you to enable xp_cmdshell Windows Shell components only on the centralized servers. The servers are traversed one by one via batch scripting along with SQL cmd, and the result is stored in a file. This file has a collection of insert SQL statements. Later, the insert statements are executed on the centralized server. The repository can be queried, used for forecasting and capacity planning, and reporting.

Diagram

WMI Capturing Disk Space of Multiple Servers

The section describes the requirements and briefs about every configuration required for successful execution of the code.

Step by Step Details

  1. Enable XP_CMDShell.
  2. List all Servers in c\Server.txt.
  3. Table Creation [TLOG_SpaceUsageDetails].
  4. Save the T-SQL script in SpaceCheck.sql.
  5. Execute dynamic SQL cmd from SSMS.
  6. Select the output by querying TLOG_SpaceUsageDetails

Enable xp_cmdshell

The xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.

  1. sp_configure 'show advanced options', 1;  
  2. GO  
  3. RECONFIGURE;  
  4. GO  
  5. sp_configure 'xp_cmdShell', 1;  
  6. GO  
  7. RECONFIGURE;  
  8. GO  
List Servers in a Text File

List all the servers in a text file c\server.txt and enable the OLE automation across all the servers. The other way to get around this problem is to include the sp_configure commands in the SQL file and take it out after it's been executed across all the servers.

ABC
DEF
EFG

Create SQL Table

Create TLOG_SpaceUsageDetails on the centralized server
  1. CREATE TABLE[dbo].[TLOG_SpaceUsageDetails](  
  2.     [ID][int] IDENTITY(1, 1) NOT NULL, [ServerName][VARCHAR](100) NULL, [LogDate][VARCHAR](10) DEFAULT(CONVERT([varchar](10), getdate(), (112))), [Drive][CHAR](3) NULL, [FreeSpaceGB][INT] NULL, [TotalSizeGB][INT] NULL, [percentageOfFreeSpace] DECIMAL(5, 2) NULL) ON[PRIMARY]  
  3. GO  
Create SQL File

Save the below content to a SQL file. For example, the content is saved under Spacecheck_v1.sql

The SQL file has two input parameters that are fed through SQL cmd.

 

  • ServerName
    The server name is used to query the respective server using the WMI win32_volume computername parameter. Also, ServerName is used to prepare insert statements. The output of the insert statements is written to a SQL file.

  • DBName
    DBName used in conjunction with preparing a fully qualified name for the insert statements.
    1. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    2. --variable declaration  
    3. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    4. DECLARE @svrName varchar(255),  
    5.     @sql varchar(400),  
    6.     @drive varchar(3),  
    7.     @TotalSizeGB varchar(10),  
    8.     @FreeSpaceGB varchar(10),  
    9.     @percentageOfFreeSpace varchar(10),  
    10.     @RowId_1 INT,  
    11.     @LoopStatus_1 SMALLINT,  
    12.     @DML nvarchar(4000)  
    13.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    14.     --To stop the message that shows the count of number of rows affected.  
    15.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    16.     SET NOCOUNT ON  
    17.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    18.     --The servername parameter are fed through sqlcmd  
    19.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    20.     SET @svrName = '$(servername)'  
    21.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --–Querying WMI class win32_volume and store the result into temporary table——————————————————  
    22. SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName, ''  
    23.     '') + ' -Class Win32_Volume -Filter '  
    24. 'DriveType = 3'  
    25. ' | select name,capacity,freespace | foreach{$_.name+'  
    26. '|'  
    27. '+$_.capacity/1048576+'  
    28. '%'  
    29. '+$_.freespace/1048576+'  
    30. '*'  
    31. '}"'  
    32. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    33. --creating a temporary table  
    34. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    35. CREATE TABLE# output(line varchar(255))  
    36.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    37.     --inserting disk name, total space and free space value in to temporary table  
    38.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    39.     INSERT# output  
    40. EXEC xp_cmdshell @sql  
    41. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    42. --Table to Store Drive related information  
    43. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    44. CREATE TABLE# drives(ID INT IDENTITY(1, 1), drive CHAR(5), FreeSpaceGB INT, TotalSizeGB INT NULL, percentageOfFreeSpace decimal(5, 2))  
    45.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    46.     --To Get Drive capacity and percentage of free space  
    47.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    48.     select * from# output  
    49. INSERT INTO# drives(drive, TotalSizeGB, FreeSpaceGB, percentageOfFreeSpace)  
    50.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    51.     --script to retrieve the values in GB from PS Script output  
    52.     --Find the string  
    53. for | , % and * using string  
    54. function -CHARINDEX  
    55. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    56. SELECT rtrim(ltrim(SUBSTRING(line, 1, CHARINDEX('|', line) - 1))) as drivename, round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('|', line) + 1, (CHARINDEX('%', line) - 1) - CHARINDEX('|', line)))) asFloat) / 1024, 0) as 'capacity(GB)', round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('%', line) + 1, (CHARINDEX('*', line) - 1) - CHARINDEX('%', line)))) asFloat) / 1024, 0) as 'freespace(GB)', cast(round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('%', line) + 1, (CHARINDEX('*', line) - 1) - CHARINDEX('%', line)))) as Float) / 1024, 0) / round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('|', line) + 1, (CHARINDEX('%', line) - 1) - CHARINDEX('|', line)))) as Float) / 1024, 0) * 100 as decimal(5, 2))  
    57. '%Free'  
    58. FROM# output  
    59. WHERE line like '[A-Z][]%'  
    60. ORDER BY drivename  
    61. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    62. -- - select the output  
    63. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    64. --select * from# drives  
    65.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    66.     --Initialize the counters  
    67.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    68.     SELECT @RowId_1 = 1, @LoopStatus_1 = 1  
    69.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    70.     --To prepare insert statement, Have used a logic to concatenate the string into a @DML variable  
    71.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    72.     WHILE(@LoopStatus_1 < > 0) BEGIN  
    73. SET @DML = ''  
    74. SELECT  
    75. @drive = drive,  
    76.     @TotalSizeGB = TotalSizeGB,  
    77.     @FreeSpaceGB = FreeSpaceGB,  
    78.     @percentageOfFreeSpace = percentageOfFreeSpace  
    79. FROM# drives  
    80. WHERE(ID = @RowId_1)  
    81. IF(@ @ROWCOUNT = 0)  
    82. BEGIN  
    83. SET @LoopStatus_1 = 0  
    84. END  
    85. ELSE  
    86. BEGIN  
    87. SET @DML = @DML + 'INSERT INTO$(dbname).dbo.TLOG_SpaceUsageDetails(servername,drive,TotalSizeGB,FreeSpaceGB,percentageOfFreeSpace)values(' + ''  
    88. '' + @svrName + ''  
    89. '' + ',' + ''  
    90. '' + @drive + ''  
    91. '' + ',' + ''  
    92. '' + @TotalSizeGB + ''  
    93. '' + ',' + ''  
    94. '' + @FreeSpaceGB + ''  
    95. '' + ',' + ''  
    96. '' + @percentageOfFreeSpace + ''  
    97. '' + ')'  
    98. END  
    99. PRINT @DML  
    100. SET @RowId_1 = @RowId_1 + 1  
    101. END  
    102. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    103. --script to drop the temporary table  
    104. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --  
    105. DROP TABLE# output  
    106. DROP TABLE# drives  

The below SQL generates insert statement and it is written to file. The @DML concatenates the values into string. The looping construct use pointer to traverse through each record one by one.

  1. SET @DML = @DML + 'INSERT INTO   
  2. $(dbname).dbo.TLOG_SpaceUsageDetails(servername, drive, TotalSizeGB, FreeSpaceGB, percentageOfFreeSpace) values('+'  
  3.         ''  
  4.         '+@svr  
Diagram

Execute SQL

The below SQL requires

 

  1. Inputfile
    contains the list of the servers.

  2. SQL file
    contains the SQL code to be executed across listed servers. This doesn't need to be in a shared path.

  3. Output file
    The output of the SQL file written into this file.

  4. ServerName
    parameter for SQL cmd, which is an input to WMI query

  5. DBName
    have used a static value. This depends on where you intend to create the SQL table. This parameter is used to prepare SQL insert statements.
    1. MASTER..XP_CMDSHELL 'for /f %j in ( f\PowerSQL\servers.txt) do sqlcmd -i "\\share\hq\SQL Server\SpaceCheck_v1.sql" -E -v servername=%j dbname="tempdb">> "\\share\hq\SQL Server\SpaceDetails_v1.sql"'  
    2. GO  
    3. MASTER..XP_CMDSHELL 'sqlcmd -S <CentralServerName>-i "\\share\hq\SQL Server\SpaceDetails_v1.sql"'  
    4. GO  
    5. SELECT * FROM TLOG_SpaceusedDetails  
    Diagram

HTML Reporting

This requires database mail profile to be configured on the central server.

  1. DECLARE @tableHTML NVARCHAR(MAX);  
  2. SET @tableHTML = N '<H1>DiskSpace Report</H1>' + N '<table border="1">' + N '<tr><th>Server Name</th> < th > Drive < /th> < th > TotalSizeGB < /th> < th > FreeSpaceGB < /th> < th > [ % Free] < /th> < th > LogDate < /th> < /tr>' +  
  3. CAST((Select td = ServerName, ' ', td = Drive, ' ', td = TotalSizeGB, ' ', td = FreeSpaceGB, ' ', td = percentageOfFreeSpace, ' ', td = LogDate, ' '  
  4.     FROM[TLOG_SpaceUsageDetails] where LogDate = CONVERT(varchar(10), getdate() - 2, 112) FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N '</table>';  
  5. EXEC msdb.dbo.sp_send_dbmail @recipients = 'pjayaram@appvion.com',  
  6.     @subject = 'Database Backup',  
  7.     @body = @tableHTML,  
  8.     @profile_name = '<ProfileName>',  
  9.     @body_format = 'HTML'// This is just a sample script. Paste your real code (javascript or HTML) here.  
  10. if ('this_is' == /an_example/) {  
  11.     of_beautifier();  
  12. else {  
  13.     var a = b ? (c % d)  e[f];  
  14. }  
Diagram

Highlights

 

  • The workings of sp_OA* OLE automation and xp_* extended stored procedures.
  • The use of xp_cmdshell – Windows SQL shell to execute the WMI query and invoke the extended stored procedure.
  • Invoke PowerShell.exe using Windows shell in SQL.
  • Batch programming with SQL cmd in SQL – the use of a for loop construct to traverse each server.
  • Dynamic SQL to prepare the insert SQL file.
  • Parameter passing for SQL cmd – server and central database repository for data storage and manipulation.
  • Database mail configuration and HTML reporting.

Conclusion

BlogImages

The OLE automation stored procedures provides access to the Component Object Model (COM), which grants Visual Basic or ASP scripting functionality to T-SQL scripts. It could be used to manipulate documents, utilize other COM-compatible code, or send e-mails etc

Warning

By default, access to the OLE Automation stored procedures is disabled. If enabled, it allows any SQL script to invoke any OLE Automation object on the computer (such as the Windows Shell). It is a significant security risk and should not be done lightly. For example, you need to be extra careful to protect against SQL injection attacks. It is basically the same as allowing xp_cmdshell.

The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. Nowadays, there is not much need for this particular command. I prefer doing these kinds of admin tasks using PoSH. The PoSH provides flexibility in many ways to handle such operations. However, there was a time when PoSH did not exist and we had to do a lot of tasks with the help of the command shell.

Note

The intention behind this article is not to expose the server to a security risk. It's up to you to know the implications of using and enabling OLE automation.