Query Database Size and Unallocated Space from SQL Database

No comments

I created the he following stored procedure to give me a more clear look at the unallocated and unused space used by my databases. The stored procedure takes one variable, the database name, and queries for the database size (data file size plus transaction log file size), unallocated space, index size, and unused space. This stored procedure borrows from the sp_spaceused stored procedure. 

use master
GO
IF EXISTS (SELECT * FROM [dbo].[sysobjects]
	WHERE ID = object_id(N'[dbo].[sp_spaceused_with_size]') AND 
	OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_spaceused_with_size]
GO
-- 1. sp_spaceused_with_size 
CREATE PROC sp_spaceused_with_size
	@db varchar(255)
	-- This stored procedure is used to query for the database
	-- size and unallocated space of a database. This query is similar
	-- to sp_spaceused, but also outputs data and transaction log files 

AS
	declare @pages bigint -- Working variable for size calculation.
			,@dbsize bigint
			,@logsize bigint
			,@reservedpages bigint
			,@usedpages bigint
			,@sql nvarchar (2000)
			,@ParmDefinition nvarchar(2000)

	--SET @db = db_name()
			
	/* unallocated space could not be negative */
	SET @sql = N'use ' + @db + ';
		select 
		@dbsizeOUT = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)), 
		@logsizeOUT = sum(convert(bigint,case when status & 64  0 then size else 0 end)) 
		from dbo.sysfiles;'
	
	SET @ParmDefinition = N'@dbsizeOUT bigint OUTPUT, @logsizeOUT bigint OUTPUT';

	EXECUTE sp_executesql @sql, @ParmDefinition, @dbsizeOUT=@dbsize OUTPUT, @logsizeOUT=@logsize OUTPUT;

	SET @sql = 'use ' + @db + ';
			select @reservedpagesOUT = sum(a.total_pages),
			@usedpagesOUT = sum(a.used_pages),
			@pagesOUT = sum(
					CASE
						-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
						When it.internal_type IN (202,204) Then 0
						When a.type  1 Then a.used_pages
						When p.index_id = @reservedpages then
			(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB'),
		data_file = ltrim(str((convert (dec (15,2),@dbsize)) 
			* 8192 / 1048576,15,2) + ' MB'),
		transaction_log_file = ltrim(str((convert (dec (15,2),@logsize)) 
			* 8192 / 1048576,15,2) + ' MB'),
		reserved = ltrim(str((convert (dec (15,2),@reservedpages)) 
			* 8192 / 1048576,15,2) + ' MB'),
		data = ltrim(str((convert (dec (15,2),@pages)) 
			* 8192 / 1048576,15,2) + ' MB'),
		index_size = ltrim(str((case when @usedpages > @pages then
			(convert (dec (15,2),@usedpages) - convert (dec (15,2),@pages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB'),
		unused = ltrim(str((case when @reservedpages > @usedpages then
			(convert (dec (15,2),@reservedpages) - convert (dec (15,2),@usedpages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB')
GO
-- 2. Setup the stored procedure to run as as a system stored procedure instead of a user stored procedure
EXEC sys.sp_MS_marksystemobject sp_spaceused_with_size
GO
use master
go
exec sp_spaceused_with_size msdb
go
Tags

Leave a Reply