Here’s a stored procedure I developed years ago to see if a database has reached a certain capacity (basically to check if it was full) and returns “true” if it has exceeded that capacity.
CREATE PROCEDURE [dbo].[IsDatabaseFull] AS BEGIN DECLARE @CurrentSizeMB int DECLARE @MaxSizeMB int DECLARE @PercentageFull int SET NOCOUNT ON SET @PercentageFull = 0.10 /* Change this value */ SELECT @CurrentSizeMB = size*8/1024, @MaxSizeMB = max_size*8/1024 FROM sys.database_files WHERE [file_id] = 1 IF @CurrentSizeMB >= (@MaxSizeMB * @PercentageFull) RETURN 1 /* True */ ELSE RETURN 0 /* False */ END
In the example above, the capacity is set to 10% of the maximum size of the database. By changing the capacity to 90%-95% and combining it with an email script, it can used to send notifications to you prior to a database becoming full so that you can increase its size preemptively.