How can i get all the servers disk space in to one table

Question

How can i get all the servers disk space in to one table

New persopn

SSCarpal Tunnel

Points: 4019
More actions
October 23, 2013 at 9:48 am

#284897

Hi All,
here i need some help and how can i get all the server disk space information in one table.
here we have to run the job and it has to load the disk information in to table.
how can i create it using powershell scripts and batch files.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

I don't know about getting it into a table with power shell and batch files, but here's an approach that would work in SQL. It'll fire the fsutil DOS command for every drive from a: to z:. If you want to fire this for a known list of drives instead, you could probably modify it. This does require that you can use xp_cmdshell and the SELECT statement could use a little tweaking.

DECLARE @intDrive Integer,

@strSQL Varchar(100);

SET @intDrive = 97;

DECLARE @Drives TABLE (

Drive Char(1),

Info Varchar(80));

WHILE @intDrive <= 122

BEGIN

SET @strSQL = 'execute xp_cmdshell ''fsutil volume diskfree ' + CHAR(@intDrive) + ':''';

INSERT @Drives(Info)

EXEC (@strSQL);

UPDATE @Drives

SET Drive = CHAR(@intDrive)

WHERE Drive IS NULL;

SET @intDrive = @intDrive + 1;

END;

SELECT Drive, TotalBytes, FreeBytes

FROM (SELECT Drive,

SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS Bigint) ELSE CAST(0 AS Bigint) END) TotalBytes,

SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS Bigint) ELSE CAST(0 AS Bigint) END) FreeBytes,

SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS Bigint) ELSE CAST(0 AS Bigint) END) AvailFreeBytes

FROM (SELECT Drive, Info

FROM @Drives

WHERE Info LIKE 'Total # of %') useless_alias

GROUP BY Drive) useless_alias_2

ORDER BY Drive;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 2

if i run the above command i am not getting any out put and all the columns are blank.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

What if you run it on the C: drive only? What is your output here?

execute xp_cmdshell 'fsutil volume diskfree c:';

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

If you're on 2008R2 or 2012 you can use sys.dm_os_volume_stats to query disk information.

It would look something like this:

SELECT DISTINCT d.logical_volume_name

,d.volume_mount_point

,CAST(d.total_bytes / 1024 / 1024.0 AS INT) AS TotalMB

,CAST(d.available_bytes / 1024 / 1024.0 AS INT) AS FreeMB

FROM sys.master_files f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) d

One small caveat: it extracts information only for the disks that contain a data or log file.

Hope this helps

Gianluca

-- Gianluca Sartori

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 5

SELECT DISTINCT d.logical_volume_name

,d.volume_mount_point

,CAST(d.total_bytes / 1024 / 1024.0 AS INT) AS TotalMB

,CAST(d.available_bytes / 1024 / 1024.0 AS INT) AS FreeMB

FROM sys.master_files f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) d

---------------------------------------------------

Now above query is working. example if we have 5 servers i need all the servers disk space information in one query.

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Powershell will do the trick:

sl c:$qry = "

SELECT DISTINCT d.logical_volume_name

,d.volume_mount_point

,CAST(d.total_bytes / 1024 / 1024.0 AS INT) AS TotalMB

,CAST(d.available_bytes / 1024 / 1024.0 AS INT) AS FreeMB

FROM sys.master_files f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) d

"

$servers = ("SERVER1","SERVER2","SERVER3","SERVER4","SERVER5")

$results = @()

$servers | ForEach-Object {

$data = Invoke-Sqlcmd -ServerInstance $_ -Query $qry

$data | ForEach-Object {

$results += $_

}

$results

-- Gianluca Sartori

Robert Sterbal SSChampion Points: 11067 More actions · Answer 7

This update of the script outputs 2 formatted columns in addition to the raw numbers:

DECLARE @intDrive Integer,

@strSQL Varchar(100);

SET @intDrive = 97;

DECLARE @Drives TABLE (

Drive Char(1),

Info Varchar(80));

WHILE @intDrive <= 122

BEGIN

SET @strSQL = 'execute xp_cmdshell ''fsutil volume diskfree ' + CHAR(@intDrive) + ':''';

INSERT @Drives(Info)

EXEC (@strSQL);

UPDATE @Drives

SET Drive = CHAR(@intDrive)

WHERE Drive IS NULL;

SET @intDrive = @intDrive + 1;

END;

SELECT Drive,

convert(varchar, cast((left(TotalBytes,len(TotalBytes)-2)) as money)/1000000000, 1) 'Total Space (GB)',

convert(varchar, cast((left(FreeBytes,len(FreeBytes)-2)) as money)/1000000000, 1) 'Free Space (GB)',

TotalBytes,

FreeBytes

FROM (SELECT Drive,

SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS Bigint) ELSE CAST(0 AS Bigint) END) TotalBytes,

SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS Bigint) ELSE CAST(0 AS Bigint) END) FreeBytes,

SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS Bigint) ELSE CAST(0 AS Bigint) END) AvailFreeBytes

FROM (SELECT Drive, Info

FROM @Drives

WHERE Info LIKE 'Total # of %') useless_alias

GROUP BY Drive) useless_alias_2

ORDER BY Drive;

412-977-3526 call/text

Perry Whittle SSC Guru Points: 234040 More actions · Answer 8

Use this rather than fsutil

wmic volume get name,capacity,"free space"

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"

How can i get all the servers disk space in to one table

Cookies on SQLServerCentral