SQL Server Script to Check to See if a Database has Reached a Certain Capacity

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.