Category: Databases

Articles related to databases.

Posted on

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.

Posted on

SQL Server AutoClose and Errors

Rummaging through my notes again, I found that if you run into these errors:

  1. “Fatal Error 9001”
  2. “Database is in transition”
  3. The log for database ‘database_name’ is not available.

You might want to check to see if the Auto-Close property on the database was set to “true”.  Setting it to “false” can correct the problem.

Posted on

SQL Error involving Auto Close

This was a solution I discovered years ago when troubleshooting a customer’s database problem.  The error thrown was:

The log for database ‘database_name’ is not available. Check the event log for related error messages. Resolve any errors and restart the database.

It was caused by the Auto Close database property being set to true.  To rectify the issue, you will need to bring the database offline, bring it back online again, and then set the Auto Close property to false.