July 13, 2022 at 7:00 pm
yes. do it as a SQL Server Agent Job - powershell script and load results to a table on SQL.
July 13, 2022 at 7:25 pm
Here's a stand-alone version of the proc I created for work. Naturally adjust is as needed to match your requirements. The proc goes into the master db (and is marked as a "system" proc) so that it can be exec'd from any db by just running: "EXEC dbo.sp_drive_space"
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_drive_space
AS
SET NOCOUNT ON;
;WITH cte_drive_space AS (
SELECT
GETDATE() AS date_captured,
mf.drive, ovs.logical_volume_name AS drive_name,
CAST(ovs.available_bytes / 1024.0 / 1024.0 / 1024.0 AS decimal(9, 1)) AS free_gb,
CAST(ovs.total_bytes / 1024.0 / 1024.0 / 1024.0 AS decimal(9, 1)) AS total_gb,
CAST(mf.data_pages / 128.0 / 1024.0 AS decimal(9, 1)) AS data_gb,
CAST(mf.log_pages / 128.0 / 1024.0 AS decimal(9, 1)) AS log_gb,
mf.data_file_count, mf.log_file_count
FROM (
SELECT
LEFT(physical_name, 2) AS drive,
SUM(CASE WHEN type_desc = 'LOG' THEN 0 ELSE 1 END) AS data_file_count,
SUM(CASE WHEN type_desc = 'LOG' THEN 1 ELSE 0 END) AS log_file_count,
SUM(CASE WHEN type_desc = 'LOG' THEN 0 ELSE size END) AS data_pages,
SUM(CASE WHEN type_desc = 'LOG' THEN size ELSE 0 END) AS log_pages,
MAX(database_id) AS database_id
FROM sys.master_files mf1
GROUP BY LEFT(mf1.physical_name, 2)
) AS mf
CROSS APPLY (
SELECT TOP (1) mf2.file_id
FROM sys.master_files mf2
WHERE mf2.state_desc = N'ONLINE' AND
LEFT(mf2.physical_name, 2) = mf.drive AND
mf2.database_id = mf.database_id
) AS ca1
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, ca1.file_id) AS ovs
)
SELECT
drive, drive_name,
free_gb, total_gb, (total_gb - free_gb) AS used_gb,
data_gb, log_gb,
data_file_count, log_file_count
FROM (
SELECT
0 AS sort1, drive AS sort2,
drive, drive_name,
free_gb, total_gb, (total_gb - free_gb) AS used_gb,
data_gb, log_gb,
data_file_count, log_file_count
FROM cte_drive_space
UNION ALL
SELECT
1, '*All*',
'**','*All*',
SUM(free_gb), SUM(total_gb), SUM(total_gb - free_gb),
SUM(data_gb) AS data_gb, SUM(log_gb) AS log_gb,
SUM(data_file_count) AS data_file_count, SUM(log_file_count) AS log_file_count
FROM cte_drive_space
) AS d
ORDER BY sort1, drive
/*end of proc*/GO
EXEC sys.sp_MS_marksystemobject 'dbo.sp_drive_space'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 13, 2022 at 7:49 pm
You could try this script: https://www.sqlservercentral.com/scripts/get-file-sizes-of-database-files-and-free-space-on-disk
July 13, 2022 at 7:57 pm
You could try this script: https://www.sqlservercentral.com/scripts/get-file-sizes-of-database-files-and-free-space-on-disk
If you've got the time to wait for it to run 🙂
It really makes no sense to send every db file thru the sys.dm_os_volume_stats function.
I send just one file per unique drive, since that's all you need to retrieve the drive info.
Now, I'll admit, you may get better individual db file stats using the view in the db rather than master.sys.master_files.
If that effects you significantly, then run through each db to get the individual db sizees, but don't call vol stats for every file.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 14, 2022 at 5:28 pm
Thank you !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply