3

Can anyone tell me how I can go about getting the physical disk id and drive letters of the local server from within management studio? I know I can grab the drive letters with xp_fixed drives and extrapolate to the full disk size with xp_cmdshell and fsutil, but what I want is something like the below:

╔══════════╦═════════════╦════════╦════════════╗
║ Drive_id ║ Dive Letter ║  Size  ║ Free_Space ║
╠══════════╬═════════════╬════════╬════════════╣
║        0 ║ C           ║  10240 ║       8000 ║
║        0 ║ E           ║ 102400 ║      25000 ║
║        1 ║ F           ║ 102400 ║      22000 ║
╚══════════╩═════════════╩════════╩════════════╝

Any ideas?

EDIT: Just to be clear I have all of the pieces of the puzzle in the below code EXCEPT the physical drive_id. If anyone can think of a way to add it to the code below that would be fantastic - if I need to blow all that away and redo it to get the information I'm after I can live with that too.

Code So Far:

SET NOCOUNT ON
IF OBJECT_ID(N'tempdb..#FreeDiskSize') IS NOT NULL DROP TABLE #FreeDiskSize

CREATE TABLE #FreeDiskSize (name varchar(5), available_mb int)

INSERT #FreeDiskSize(name,available_mb) 
EXEC master..XP_FIXEDDRIVES
DECLARE @Drive VARCHAR(5), @CMD VARCHAR(1000), @pos SMALLINT

IF OBJECT_ID(N'tempdb..#TotalDiskSize') IS NOT NULL DROP TABLE #TotalDiskSize

CREATE TABLE #TotalDiskSize (TotalBytes VARCHAR(1000), Drive VARCHAR(5))

DECLARE Drive_name CURSOR FOR 

SELECT name FROM #FreeDiskSize
OPEN Drive_name
FETCH NEXT FROM Drive_name INTO @Drive
WHILE @@FETCH_STATUS = 0 
BEGIN
SET @CMD='MASTER..XP_CMDSHELL ' + ''''+ 'FSUTIL VOLUME DISKFREE ' + @Drive + ':| find '+ '"Total # of bytes"'+''''
INSERT #TotalDiskSize(TotalBytes) EXEC (@CMD)
UPDATE #TotalDiskSize SET Drive=@Drive WHERE Drive IS NULL
FETCH NEXT FROM Drive_name INTO @Drive
END
CLOSE Drive_name
DEALLOCATE Drive_name
DELETE FROM #TotalDiskSize WHERE TotalBytes IS NULL
SELECT @pos=charindex(':',TotalBytes) FROM #TotalDiskSize

SELECT b.Drive as Drive, CONVERT(BIGINT,(RIGHT(b.TotalBytes,(LEN(b.TotalBytes)-@pos))))/1073741824 AS TOTAL_Drive_SPACE_GB, a.available_mb/1024 AS AVAILABLE_SPACE_GB
FROM #FreeDiskSize a WITH (NOLOCK)
INNER JOIN #TotalDiskSize b WITH (NOLOCK)ON a.name=b.Drive
  • 2
    Using SSMS its not possible. You should use PowerShell that queries WMI e.g. select Name, DriveType, FileSystem, FreeSpace, Capacity, Label from Win32_Volume where DriveType = 2 or DriveType = 3 should help you. – Kin Shah Jun 2 '15 at 1:03
  • What version of SQL Server? Is there at least one data or log file on each drive you care about? Generally though, you're using the wrong tool for the job. – Aaron Bertrand Jun 2 '15 at 1:50
  • Aaron - SQL Server 2008+ - this is part of an internal audit script so may be run against multiple servers. I need all server drives, not just those with SQL files on. Having spend some time already on this I'm aware SSMS is less than ideal for the task, but this is a small part of a larger task and the script will be run out of SSMS. XP_Cmdshell is available and powershell will be in most instances(Enough that where it is not this can be collected manually). – Bemused Jun 2 '15 at 8:16
2

You can use glennberry scripts..

 SELECT DISTINCT vs.volume_mount_point, 
vs.file_system_type, 
    vs.logical_volume_name,
 CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
    CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)],  
    CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] 
    FROM sys.master_files AS f WITH (NOLOCK)
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE);

Below is the sample output,is this what you need..

enter image description here

| improve this answer | |
1

If you really need it you can utilize xp_cmdshell and PowerShell, however you will need to be connected to a SQL Server instance with PowerShell installed. As well you would need to enable xp_cmdshell, which the procedures I use to collect this enables it and then disables it once it is finished. Just my preference.

I built the below based on this article by Laerte Junior here.

DECLARE @XML_string varchar(max)
DECLARE @DriveSpace XML
DECLARE @XML TABLE (XMLitself varchar(2000), orderIt int IDENTITY(1,1) PRIMARY KEY);

INSERT INTO @XML(XMLitself)
EXEC xp_cmdshell 'powershell.exe -noprofile -command "Get-WmiObject Win32_Volume -ErrorAction ''Stop'' | where {$_.DriveType -eq 3 -and $_.DriveLetter } | Select-Object @{Label=''volLetter'';Expression={$_.DriveLetter}},@{Label=''freeSpaceMB'';Expression={$_.FreeSpace/1MB}}, @{Label=''capacityMB'';Expression={$_.Capacity/1MB}} | ConvertTo-XML -As string"'

SELECT @XML_string = COALESCE(@XML_string,'') + XMLitself
FROM @XML
WHERE XMLitself IS NOT NULL

SELECT @DriveSpace = @XML_string

SELECT MAX(CASE WHEN attribute='volLetter' THEN value ELSE '' END) AS volLetter
    ,CAST(MAX(CASE WHEN attribute='freeSpaceMB' THEN value ELSE '' END) AS decimal(15,3)) AS freeSpaceMB
    ,CAST(MAX(CASE WHEN attribute='capacityMB' THEN value ELSE '' END) AS decimal(15,3)) AS capacityMB
    ,@dateCreated
FROM (
    SELECT [property].value('(./text())[1]','varchar(20)') AS [value],
        [property].value('@Name','varchar(20)') AS [attribute],
        DENSE_RANK() OVER (ORDER BY [object]) AS unique_object
    FROM @DriveSpace.nodes('Objects/Object') AS b([object])
    CROSS APPLY b.object.nodes('./Property') AS c(property)
    ) PowerShell
GROUP BY unique_object;

You would get output similar to the below: enter image description here

| improve this answer | |
  • Thanks Shawn, as per my question I can grab everything except the Physical disk id using a combination of xp_fixeddrives, xp_cmdshell and fsutil. The physical disk ID is the bit of the puzzle I'm missing. – Bemused Jun 2 '15 at 8:10
  • The DeviceID is found using win32_DiskDrive class, but there going to be no correlation (or mapping) between that output and win32_volume. – user507 Jun 2 '15 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.