Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can i get all the servers disk space in to one table Expand / Collapse
Author
Message
Posted Wednesday, October 23, 2013 9:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 26, 2014 4:04 AM
Points: 139, Visits: 394
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.
Post #1507713
Posted Wednesday, October 23, 2013 11:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
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
Post #1507760
Posted Thursday, October 24, 2013 4:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 26, 2014 4:04 AM
Points: 139, Visits: 394
if i run the above command i am not getting any out put and all the columns are blank.
Post #1507964
Posted Thursday, October 24, 2013 5:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
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
Post #1507978
Posted Thursday, October 24, 2013 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1508063
Posted Thursday, October 24, 2013 8:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 26, 2014 4:04 AM
Points: 139, Visits: 394
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.
Post #1508075
Posted Thursday, October 24, 2013 8:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1508108
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse