Databases Programming Tips

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]
	DECLARE @CurrentSizeMB int
	DECLARE @MaxSizeMB int
	DECLARE @PercentageFull int
	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 */
		RETURN 0 /* False */

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.

Databases Errors

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.


ReportViewerWebControl and HTTP 404 Not Found

I was digging through some old notes today and found this tip that might be helpful to someone in the future.  If you’re getting a HTTP 404 Not Found or HTTP Handler related error with the ASP.NET Report Viewer Control, you can try to changing the Application Pool Pipeline Mode from Integrated to Classic to fix the problem.