Introduction
xp_fixeddrives only provide how much disk space is free on each drive but not the maximum space of the drive & percentage of free space. If you need to find out Drive Space for SQL server with Drive Space, Free space and percentage of free space then we need to create a stored procedure ‘sp_diskspace‘.
Ole Automation Procedures
But !!!! before that, you need to enable Ole Automation Procedures feature. Ole Automation Procedures feature must be disabled unless specifically required and approved. To more about visit: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ole-automation-procedures-server-configuration-option?view=sql-server-2017
To enable Ole Automation Procedures feature we may follow below steps
|
1 2 3 4 5 6 7 8 |
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO |
Ole Automation Procedures is enabled.
sp_diskspace
Now we will create ‘sp_diskspace’ with the below script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
use master go CREATE PROCEDURE sp_diskspace AS /* Displays the free space,free space percentage plus total drive size for a server */ SET NOCOUNT ON DECLARE @hr int DECLARE @fso int DECLARE @drive char(1) DECLARE @odrive int DECLARE @TotalSize varchar(20) DECLARE @MB bigint ; SET @MB = 1048576 CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL, TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR SELECT drive from #drives ORDER by drive OPEN dcur FETCH NEXT FROM dcur INTO @drive WHILE @@FETCH_STATUS=0 BEGIN EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE drive=@drive FETCH NEXT FROM dcur INTO @drive END CLOSE dcur DEALLOCATE dcur EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso SELECT drive, FreeSpace as 'Free(MB)', TotalSize as 'Total(MB)', CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' FROM #drives ORDER BY drive DROP TABLE #drives RETURN go |
Download sp_diskspace in txt & sp_diskspace in zip
After successfully execution below messages will appear.
And we can also find the stored procedure in the MASTER database.
To run that stored procedure we need to execute below query
|
1 |
exec sp_diskspace |
The result will be displayed like below snapshot.
Same output by a script
Now if you don’t want all this HUNGAMA!! of creating store procedures and enabling Ole Automation Procedures feature then another simple solution is waiting for you. We can also get our desire result by running a simple script also. Simply run the below script. But the disadvantage is that below script will display only that disk status which contains only SQL Server files/data.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT DISTINCT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, volume_mount_point [Disk], file_system_type [File System], logical_volume_name as [Logical Drive Name], CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB], CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] FROM sys.master_files CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) |
Good
We expected more..!!! 🙂
Thanks, dude, keep visiting us daily !!
Thanks for your precious comment.