Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Thursday, 06 October 2016

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)

 

Thursday, 06 October 2016 17:03:45 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server | Web_Blog#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Agent will not start when a use...
Calculate Stock Break Even Price
Useable Space
Recent Posts
Archive
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll