TSQL



TSQL---- Allow advanced options to be changed.sp_configure 'show advanced options', 1;GO-- To update the currently configured value for advanced options.RECONFIGURE;GO-- To enable the feature.sp_configure 'Ole Automation Procedures', 1;GoEXEC sp_configure 'xp_cmdshell', 1GO-- To update the currently configured value for this feature without restarting of your sql instanceRECONFIGURE;GODECLARE @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)----------------------------------------------------------------------------------------------- --Table to Store Drive related information ----------------------------------------------------------------------------------------------- CREATE TABLE #drives ( id INT IDENTITY(1,1) PRIMARY KEY, drive CHAR(1), FreeSpaceMB INT , TotalSizeMB INT NULL, percentage 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 Percentage=(@FreeSpace/(TotalSizeMB*1.0))*100.0 WHERE drive=@drive END SET @RowId_1 = @RowId_1 + 1 END SELECT drive,FreeSpaceMB/1024.00 FreeSpaceGB,TotalSizeMB/1024.00 TotalSizeGB,Percentage [FreeSpace %] FROM #drivesDROP TABLE #drivesOutputUsing Get-WMIObject$serverName='HQDBSP18'Get-WmiObject win32_logicalDisk -ComputerName $ServerName | select DeviceID,VolumeName,@{Expression={$_.Size /1Gb -as [int]};Label="Total Size(GB)"},@{Expression={($_.Size /1Gb -as [int]) - ($_.Freespace / 1Gb -as [int])};Label="InUse Size (GB)"} ,@{Expression={$_.Freespace / 1Gb -as [int]};Label="Free Size (GB)"},@{Expression={(($_.Freespace /1Gb -as [float]) / ($_.Size / 1Gb -as [float]))*100};Label="FreeSpace (%)"} |ft -AutoSizeInvoke Get-Counters cmdlet$serverName='HQDBSP18'get-counter -computername $serverName -counter "\LogicalDisk(*)\% free space" ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download