Monitoring free space on mounted junction points?

  • 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.

  • 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?

  • 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

  • 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 4 (of 4 total)

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