Couple different ways to get block size on windows.

Traditionally I used fsutil, previous blog post on that here, but recently I've been using powershell, and then using TSQL to run the powershell script and return it as a result set, which I then keep in a tracking system.

Here is the powershell script:

Get-WmiObject win32_volume | select SystemName, Label, name,
@{N="BlockSize";E={($_.BlockSize/1024)}},@{N="Capacity";E={[int]$a=$_.Capacity/(1024*1024*1024);$a}}

Here is the same script, embedded in TSQL (yes it uses xp_cmdshell).

Declare @Is_XP_CMDSHELL_Enabled Int

Set @Is_XP_CMDSHELL_Enabled = (
    SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
    FROM  sys.configurations
    WHERE  name = 'xp_cmdshell');

-- not enabled
If @Is_XP_CMDSHELL_Enabled = 0 
BEGIN
    -- To allow advanced options to be changed.
    EXEC sp_configure 'show advanced options', 1
    -- To update the currently configured value for advanced options.
    RECONFIGURE
    -- To enable the feature.
    EXEC sp_configure 'xp_cmdshell', 1
    -- To update the currently configured value for this feature.
    RECONFIGURE
END
 
CREATE TABLE #output
(line varchar(255))
INSERT #output
EXEC xp_cmdshell 'Powershell –command "get-wmiobject Win32_volume |select Name,Capacity,Freespace,BlockSize,Label | ConvertTo-Xml -NoTypeInformation -As string"'

-- only disable xp_cmdshell if it was found this way
If @Is_XP_CMDSHELL_Enabled = 0 
begin
    -- immediately disable xp_cmdshell
    EXEC master.dbo.sp_configure 'show advanced options', 1
    RECONFIGURE
    EXEC master.dbo.sp_configure 'xp_cmdshell', 0
    RECONFIGURE
end
 
DELETE #output WHERE line IS NULL
 
DECLARE @doc varchar(max)
SET @doc = ''
DECLARE @line varchar(255)
DECLARE xml_cursor CURSOR
FOR SELECT line FROM #output
OPEN xml_cursor
FETCH NEXT FROM xml_cursor INTO @line
WHILE @@FETCH_STATUS = 0
BEGIN
SET @doc = @doc + @line
FETCH NEXT FROM xml_cursor INTO @line
END
CLOSE xml_cursor
DEALLOCATE xml_cursor
DROP TABLE #output
 
SELECT
item.ref.value('(Property/text())[1]', 'nvarchar(128)') AS VolumeName
,item.ref.value('(Property/text())[2]', 'nvarchar(128)') AS SizeBytes
,item.ref.value('(Property/text())[3]', 'nvarchar(128)') AS FreeBytes
,item.ref.value('(Property/text())[4]', 'nvarchar(128)') AS [BlockSize]
,item.ref.value('(Property/text())[5]', 'nvarchar(128)') AS Label
FROM (SELECT CAST(@doc AS XML) AS feedXml) feeds(feedXml)
CROSS APPLY feedXml.nodes('/Objects/Object') AS item(ref)

http://www.dotnetfiddle.net
http://www.motifinvesting.com
http://www.twilio.com