October 23, 2013 at 9:48 am
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.
October 23, 2013 at 11:04 am
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;
October 24, 2013 at 4:43 am
if i run the above command i am not getting any out put and all the columns are blank.
October 24, 2013 at 5:23 am
What if you run it on the C: drive only? What is your output here?
execute xp_cmdshell 'fsutil volume diskfree c:';
October 24, 2013 at 8:08 am
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
October 24, 2013 at 8:16 am
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.
October 24, 2013 at 8:59 am
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
January 19, 2016 at 9:05 pm
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
January 19, 2016 at 10:11 pm
Use this rather than fsutil
wmic volume get name,capacity,"free space"
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply