July 20, 2015 at 8:06 am
I'd like some T-SQL to monitor the available disk space on folders that the data and log files exist.
We have multiple databases instances set up in clusters, each database uses just one drive for its data, log and backup files. However the sub-folders off the main drive are actually hard linked junctions to different spaces on a SAN disk. This means that the root folder has a different amount of space to each subfolder.
So for example the N drive has the following free space on different folders.
N:\ 880 MB free
N:\Backup 41 GB free
N:\Data 117 GB Free
N:\Logs 129 GB Free
N:\Replication 33 GB Free
N:\TempDB 37 GB Free
N:\SystemDB 31 GB Free
I am trying to write a query to get the free space available on the Data and Logs folders. I was using EXEC master..xp_fixeddrives to get the free space but this just gives me the 880 MB that's free on the root and no information on the subfolders.
Is there any T-SQL to monitor the amount of free space on the folders that the data and log files reside on? I would like to use this for both SQL 2005 and 2008 so if possible some SQL that will work on both environments.
July 20, 2015 at 11:18 am
I've written a script which works on both SQL Server 2005 and 2008. I'm running it from a registered server group. It all works but for some reason I'm getting an error:
[font="Courier New"]An error occurred while executing batch. Error message is: The result set could not be merged because the result schema did not match the schema from the first responding server.[/font]
I've tried making all the queries return identical results so I'm not sure why I'm getting this error?
Here's the code I'm running:
DECLARE @ServerVersion varchar(100)
SET @ServerVersion =CONVERT(varchar,SERVERPROPERTY('productversion'))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion)-1)
--PRINT @ServerVersion
DECLARE @command nvarchar(4000)
IF CONVERT(int,@ServerVersion) < 10 --–2000,2005
BEGIN
SET @command = '
USE [?];
IF OBJECT_ID(''tempdb..#xp_fixeddrives'') IS NOT NULL
DROP TABLE #xp_fixeddrives;
CREATE TABLE #xp_fixeddrives(Drive varchar(100), MBFree int);
INSERT INTO #xp_fixeddrives
EXEC master..xp_fixeddrives;
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHostOrNodeName],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS CluserNodes,
CONVERT(varchar(250), DB_NAME()) COLLATE Latin1_General_CI_AS DB,
CONVERT(varchar(250), f.Name) COLLATE Latin1_General_CI_AS Name,
CONVERT(varchar(250), LEFT(f.FileName, 3)) COLLATE Latin1_General_CI_AS Drive,
CONVERT(varchar(250), f.FileName) COLLATE Latin1_General_CI_AS FileName,
CONVERT(Decimal(15,2), ROUND(f.Size/128.000, 2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) AS [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) AS [Available Space In File (MB)],
CONVERT(int, b.MBFree) [Drive Free Space (MB)]
FROM [?].dbo.sysfiles f WITH (NOLOCK)
LEFT JOIN tempdb..#xp_fixeddrives b
ON LEFT(f.FileName, 1) COLLATE Latin1_General_CI_AS = b.Drive COLLATE Latin1_General_CI_AS;'
END
ELSE
BEGIN
SET @command = '
USE [?];
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHostOrNodeName],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS CluserNodes,
CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS DB,
CONVERT(varchar(250), f.name) COLLATE Latin1_General_CI_AS Name,
CONVERT(varchar(250), v.volume_mount_point) COLLATE Latin1_General_CI_AS AS Drive,
CONVERT(varchar(250), f.[Filename]) COLLATE Latin1_General_CI_AS [Filename],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000, 2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) AS [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) AS [Available Space In File (MB)],
CONVERT(int, v.available_bytes/1048576.0) AS [Drive Free Space (MB)]
FROM [?].sys.sysfiles f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v;'
END
-- END IF
--PRINT @command
EXEC sp_MSforeachdb @command
Any ideas?
July 20, 2015 at 11:28 am
I've been using this script for some time and it has worked well for me. I hope it solves your needs.
DBCC SQLPERF(logspace);
GO
SELECT DISTINCT
volume_mount_point
, CAST( ( total_bytes / 1073741824. ) as numeric(18,4)) AS [Total_GB]
, CAST( ( available_bytes / 1073741824. ) as numeric(18,4)) AS [Available_GB]
, CAST(
( CAST( ( available_bytes / 1073741824. ) as numeric(18,4)) /
CAST( ( total_bytes / 1073741824. ) as numeric(18,4))
) * 100.0000
as numeric(18,4)) AS [PercentFree]
FROM sys.master_files AS f CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.FILE_ID)
ORDER BY volume_mount_point;
GO
SELECT name AS [FileName]
, CAST( size/128. as numeric(18,4)) AS [FileSizeMB]
, CAST( (size/128.) - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS FLOAT)/128.) as numeric(18,4)) AS [FreeSpaceMB]
, CAST(
( CAST( (size/128.) - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS FLOAT)/128.) as numeric(18,4)) /
CAST( size/128. as numeric(18,4))
) * 100.0000
as numeric(18,4)) AS [PercentFree]
, LEFT( physical_name, LEN( physical_name ) - CHARINDEX( '\',REVERSE( physical_name ) ) +1) AS [Path]
, CASE WHEN max_size != -1 THEN CAST( (max_size/128) as varchar ) + ' MB' Else 'Unlimited' End AS [MaxGrowthSetting]
, CASE WHEN growth = 0 THEN 'Fixed'
WHEN is_percent_growth = 0 THEN CAST( (growth/128) AS varchar ) + ' MB'
WHEN is_percent_growth = 1 Then Cast( growth as varchar) + '%'
End As [GrowthSetting]
FROM sys.database_files
ORDER BY [type_desc]
, [file_id]
, [FileName]
GO
SELECT name
, user_access_desc
, state_desc
, recovery_model_desc
, log_reuse_wait_desc
FROM sys.databases;
GO
July 21, 2015 at 9:41 am
Thanks for the script, it looks useful particularly the queries that return the growth settings. I'll use it when I need that information. Unfortunately, sys.dm_os_volume_stats doesn't exist in versions of SQL Server below 2008 and the script needs to run on both 2005 and 2008. The reason I need this script is to get readings of the database disk/file usage on all our servers. I've updated the script I had earlier and solved the problem with the error message I was getting by changing, under SSMS menu "Tools\Options\Query Results\SQL Server\Multiserver Results", the option of "Merge Results" to "False". This outputs a lot of rowsets and is a bit of a pain to paste into Excel but it achieves the result I need. Here's my updated script:
DECLARE @ServerVersion varchar(100)
SET @ServerVersion =CONVERT(varchar,SERVERPROPERTY('productversion'))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion)-1)
--PRINT @ServerVersion
DECLARE @command nvarchar(4000)
IF CONVERT(int, @ServerVersion) < 10 --–2000, 2005
BEGIN
SET @command = '
USE [?];
IF OBJECT_ID(''tempdb..#xp_fixeddrives'') IS NOT NULL
DROP TABLE #xp_fixeddrives;
CREATE TABLE #xp_fixeddrives(Drive varchar(250), MBFree int);
INSERT INTO #xp_fixeddrives
EXEC master..xp_fixeddrives;
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
CONVERT(varchar(250), DB_NAME()) COLLATE Latin1_General_CI_AS [DB],
CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],
CONVERT(varchar(250), f.Name) COLLATE Latin1_General_CI_AS [Name],
CONVERT(varchar(250), LEFT(f.FileName, 3)) COLLATE Latin1_General_CI_AS [VolumeOrDrive],
CONVERT(varchar(250), f.FileName) COLLATE Latin1_General_CI_AS [FileName],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000, 2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) AS [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) AS [Available Space In File (MB)],
CONVERT(Decimal(15,2), d.MBFree) [Drive Free Space (MB)]
FROM [?].dbo.sysfiles f WITH (NOLOCK)
INNER JOIN [?].sys.database_files df ON df.file_id = f.fileid
LEFT JOIN tempdb..#xp_fixeddrives d
ON LEFT(f.FileName, 1) COLLATE Latin1_General_CI_AS = d.Drive COLLATE Latin1_General_CI_AS;'
END
ELSE -- SQL 2008+
BEGIN
SET @command = 'USE [?];
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), ''Not Clustered'')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS [DB],
CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],
CONVERT(varchar(250), f.name) COLLATE Latin1_General_CI_AS [Name],
CONVERT(varchar(250), v.volume_mount_point) COLLATE Latin1_General_CI_AS [VolumeOrDrive],
CONVERT(varchar(250), f.[Filename]) COLLATE Latin1_General_CI_AS [Filename],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000,2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],
CONVERT(Decimal(15,2), v.available_bytes/1048576.0) AS [Drive Free Space (MB)]
FROM [?].sys.sysfiles f WITH (NOLOCK)
INNER JOIN [?].sys.database_files df ON df.file_id = f.fileid
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v;'
END
-- END IF
--PRINT @command
EXEC sp_MSforeachdb @command
Once it's in Excel it's easy to create multiple pivot tables and I can summaries the data to get all the information I need.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply