/*Execute the below T-SQL on Centralized Server --*/
/*Centralized server is a place, where the scripts are stored and executed to capture all its corresponding data*/
/*************************
--Enable XP_CMDShell –SSMS only on centralized Server
*****************************/
sp_configure'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure'xp_cmdShell', 1;
GO
RECONFIGURE;
GO
/*************************
--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
/*************************
--Table Creation –SSMS –Centralized Server
*****************************/
CREATETABLE [dbo].[TLOG_SpaceUsageDetails](
[space_id] [int] IDENTITY(1,1)NOTNULL,
[servername] [varchar](100)NULL,
[LogDate] [varchar](10)NULL,
[drive] [char](1)NULL,
[FreeSpaceMB] [int] NULL,
[TotalSizeMB] [int] NULL,
[percentageOfFreeSpace] [int] NULL
)ON [PRIMARY]
GO
SETANSI_PADDINGOFF
GO
ALTERTABLE [dbo].[TLOG_SpaceUsageDetails] ADD DEFAULT (CONVERT([varchar](10),getdate(),(112)))FOR [LogDate]
--*************************************************************
-- create a file C:\SpaceCheck.sql. Copy and Paste the below codeSpaceCheck.sql – Centralized Server
--**************************************************************
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)
SETNOCOUNTON
------
--Table to Store Drive related information
------
CREATETABLE #drives
(
id INTIDENTITY(1,1)PRIMARYKEY,
drive CHAR(1),
FreeSpaceMB INT,
TotalSizeMB INTNULL,
percentageOfFreeSpace INT
)
------
--Inserting the output of xp_fixeddrives to #SpaceSize Table
------
INSERT #drives(drive,FreeSpaceMB)EXECmaster.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
droptable #drives
/**********************************
-Execute it through SSMS – replace below code to valid centralized server name. Replace AQDBSQL01.
**************/
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 AQDBSQL01 -i c:\spacedetails.sql -E'
/***************/
--Data Validation --SSMS
*************
select*from dbo.TLOG_SpaceUsageDetails