8

I have manually resized mdf/ndf files to a big size to avoid autogrow operations on SQL Server databases. Since the files are bigger there is very little free space on disk partitions and the sysadmins keep alerting me that I'm running out of space.

Because I resized them, there is a lot of free space in the data files but one can't notice it looking at file sizes/disk free space.

How can I monitor the real % usage of data files? I would prefer using perfmon counters. I am conerned that when the file really runs out of space SQL Server won't be able to allocate enough space and will crash.

11

Not sure why you want to use performance counters for this when you can get it from a simple query. And in fact while you can get this information about log files from performance counters (Log File(s) Size (KB) / Log File(s) Used Size (KB)), there is no such counter for how much space is used in a data file.

;WITH f AS 
(
  SELECT name, size = size/128.0 FROM sys.database_files
),
s AS
(
  SELECT name, size, free = size-CONVERT(INT,FILEPROPERTY(name,'SpaceUsed'))/128.0
  FROM f
)
SELECT name, size, free, percent_free = free * 100.0 / size
FROM s;
  • I was hoping it could be done without using SQL Server. Sure I can query it manually and see the free space but I need an automated solution. A perfmon counter would be perfect because sysadmins could use it with their monitoring software. I might use your query and set up a SQL Agent job + email alert. Also I will try Kin's solution and see which one works the best. – BuahahaXD Apr 10 '15 at 6:26
  • 1
    I have a similar use case. If an off-the-shelf monitoring solution is not SQL Server specific, but knows about windows, then a performance counter would be easier to implement and monitor. – Michael J Swart Jul 16 '18 at 18:18
7

I have another method to proactively monitor data file space and alert if the free space falls below a certain percentage using SQL Alert.

The basics are

  • Create a user defined error message in sys.messages. This will be used by sql agent alert.

    -- User-defined error messages can be an integer between 50001 and 2147483647. 
    EXEC sp_addmessage 
      @msgnum=911421, -- 911DBA
      @severity=1,    -- Informational message not generated by DB Engine       
      @msgtext=N'Data files are %d percent full in database %s.'
    
  • Now create a SQL Agent job. Make sure you change set @threshold = 20 --->>>>>>>>>>>>>>>>> CHANGE HERE <<<<<<<<<<<<<<<<<<<<<--- in below script. I have put his as a very low threshold, only to simulate the alert. Schedule the job to run every 30min (change this as per your needs).

        if object_id('tempdb..#dbserversize') is not null
        DROP TABLE #dbserversize;
    
        create table dbo.#dbserversize (
         [id] int identity (1,1)
         ,[databaseName] sysname
        ,[Drive]    varchar(3)
        ,[Logical Name] sysname
        ,[Physical Name]    varchar(max)
        ,[File Size MB] decimal(38, 2)
        ,[Space Used MB]    decimal(38, 2)
        ,[Free Space]   decimal(38, 2)
        ,[%Free Space]  decimal(38, 2)
        ,[Max Size] varchar(max)
        ,[Growth Rate]  varchar(max)
        )
    
        declare @id int
        declare @threshold int
        declare @dbname sysname
    
        declare @sqltext nvarchar(max)
    
        declare @freespacePct int
    
        set @threshold = 20   --->>>>>>>>>>>>>>>>> CHANGE HERE <<<<<<<<<<<<<<<<<<<<<---
    
        select @dbname = min(name) from sys.databases where database_id > 4 and [state] = 0 
    
        while @dbname is not NULL
    
        begin
            select @dbname = name from sys.databases where name = @dbname and database_id > 4 and [state] = 0 
                --- Modified from Erin's blog : Proactive SQL Server Health Checks, Part 1 : Disk Space
                --- source http://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
            set @sqltext =  ' use '+@dbname+';'+' 
                insert into dbo.#dbserversize
                select '''+@dbname+''' as [databaseName]
                    ,substring([physical_name], 1, 3) as [Drive]
                    ,[name] as [Logical Name]
                    ,[physical_name] as [Physical Name]
                    ,cast(CAST([size] as decimal(38, 2)) / 128.0 as decimal(38, 2)) as [File Size MB]
                    ,cast(CAST(FILEPROPERTY([name], ''SpaceUsed'') as decimal(38, 2)) / 128.0 as decimal(38, 2)) as [Space Used MB]
                    ,cast((CAST([size] as decimal(38, 0)) / 128) - (CAST(FILEPROPERTY([name], ''SpaceUsed'') as decimal(38, 0)) / 128.) as decimal(38, 2)) as [Free Space]
                    ,cast(((CAST([size] as decimal(38, 2)) / 128) - (CAST(FILEPROPERTY([name], ''SpaceUsed'') as decimal(38, 2)) / 128.0)) * 100.0 / (CAST([size] as decimal(38, 2)) / 128) as decimal(38, 2)) as [%Free Space]
                    ,case 
                        when cast([max_size] as varchar(max)) = - 1
                            then ''UNLIMITED''
                        else cast([max_size] as varchar(max))
                        end as [Max Size]
                    ,case 
                        when is_percent_growth = 1
                            then cast([growth] as varchar(20)) + ''%''
                        else cast([growth] as varchar(20)) + ''MB''
                        end as [Growth Rate]
                    from sys.database_files
                    where type = 0 -- for Rows , 1 = LOG'
                --print @sqltext
                exec (@sqltext)
    
    
                select @dbname = min(name) from sys.databases where name > @dbname and database_id > 4 and [state] = 0 
        end
    
    
        --- delete the entries that do not meet the threshold 
    
        delete from dbo.#dbserversize
        where [%Free Space] < @threshold;
    
    
        --select * from dbo.#dbserversize
    
        --- NOW Raise errors for the databases that we got flagged up
    
        while exists (select null from dbo.#dbserversize)
        begin
    
            select top 1 @id = id,
                        @dbname = databaseName,
                        @freespacePct = [%Free Space]
                    from dbo.#dbserversize;
    
    
                RAISERROR(911421, 10,1,@freespacePct, @dbname) with LOG;
    
                delete from dbo.#dbserversize where id = @id;
    
        end
    
  • Now create an alert to respond to 911421 error number.

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_alert @name=N'MDF file alert', 
            @message_id=911421, 
            @severity=0, 
            @enabled=1, 
            @delay_between_responses=1800, 
            @include_event_description_in=0, 
            @job_id=N'019c4770-865b-406b-894e-72a1ff34f732'
    GO
    EXEC msdb.dbo.sp_add_notification @alert_name=N'MDF file alert', @operator_name=N'Notify 911 DBA for MDF files getting full', @notification_method = 1
    GO
    

    enter image description here

    enter image description here

Note: There are other kinds of enhancements that you can do with my idea above.

  • Make the agent job as a stored procedure accepting threshold as an input value.
  • Log the values to a physical table in the DBA database so you can do trend analysis and capacity planning.
  • Any other that you can think .... :-)
  • There is a problem with your query. I think there is an infinite loop somewhere. I configured the alerts, turned off triggering the job by the alert and I keep getting tons of mails about data files :) – BuahahaXD Apr 17 '15 at 8:49
  • 1
    You should use delay between responses setting. – Kin Shah Apr 18 '18 at 11:54
5

Just to build on Aaron's and Kin's answers, you can do it with perf counters, but one of the user settable counters.

I would:

  • create a stored procedure that will use Aaron's query to get the free space in a single file or loop through all files and get the min/max value that's of interest
  • create a job that will periodically run the stored proc

In case you want to be properly notified:

  • create an operator/use an existing one with a valid email address
  • create an alert based on that user counter that will notify the previous operator (make sure you don't flood yourself with emails - set a delay between responses).

The caveats are:

  • you have only 10 settable counters
  • they don't have a meaningful name
  • you need to have the cumbersome job+proc+alert to have a nice picture

But they can be used in Perfmon or another similar tool.

3

Also there is a simple alert settings to monitor data file size:

enter image description here

Additonal 'Low Log Space Alert' will monitor free space for transaction log file (autogrowth is off):

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.