Monitoring free space in SQL Server data files

Question

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.

Answer 1

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;

• Łukasz Kastelik: 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.
• Michael J Swart: 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.

Answer 2

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 .... :-)

• Łukasz Kastelik: 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 :)
• Kin Shah: You should use delay between responses setting.

Answer 3

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.

Answer 4

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