-- Query:
--	sp_showSpace.sql
--
-- Description:
--	Stored procedure to display amount of database and log space 
--	for the current database
--
-- Comments:
--	1. Invoke the procedure like this:
--		sp_showSpace
--		   -or-
--		sp_showSpace log
--	   Use the optional log parameter to cause the messages to be logged 
--	   to the application event log.  (Default is no logging.)
--
-- Note:
--	This procedure should be created in master. It must be run from
-- 	the desired database, however, as it references sysindexes
--
use master
go

-- Drop the procedure if it already exists
if exists (select * from sysobjects where 
	id = object_id('dbo.sp_showSpace') and sysstat & 0xf = 4)
	drop procedure dbo.sp_showSpace
GO

CREATE PROCEDURE sp_showSpace @sPutInEventLog  char(8)='' AS

-- Run a CheckPoint to insure statistics are updated
CHECKPOINT

-- Declare variables
set nocount on

declare @iErrorMark	int
declare @iWarningMark	int

-- Change these two lines to modify at what levels messages are
-- logged in the event log
select @iErrorMark = 90		-- Greater than 90% is an error
select @iWarningMark =70	-- Greater than 70% is a warning

declare @iDataSegment 	int
declare @iLogSegment	int
declare @sLogEventMsg	varchar(127)

-- *** The following lines assume that the data segment has a segmap 
-- *** value of 3, and the log segment has a segmap value of 4 in 
-- *** master..sysusages.  If this changes, the next two lines will 
-- *** have to be modified!!
select @iDataSegment = 3
select @iLogSegment = 4

-- Variables for the data portion of the database
declare @iDataSize 	dec(15,1)
declare @iDataFreeSpace	dec(15,1)
declare @iDataUsedSpace	dec(15,1)
declare @iDataCapacity	dec(15,1)
declare @sDataSize	varchar(20)
declare	@sDataFreeSpace	varchar(20)
declare @sDataUsedSpace	varchar(20)
declare @sDataCapacity	varchar(30)
declare @sDataMsg 	varchar(255)

-- Variables for the log portion of the database
declare @iLogSize	dec(15,1)
declare @iLogFreeSpace	dec(15,1)
declare @iLogUsedSpace	dec(15,1)
declare @iLogCapacity	dec(15,1)
declare @sLogSize	varchar(20)
declare	@sLogFreeSpace	varchar(20)
declare @sLogUsedSpace	varchar(20)
declare @sLogCapacity	varchar(30)
declare @sLogMsg 	varchar(255)

-- Go get the statistics about the database
select @iDataSize = sum(convert(dec(15,1),size))
	from master..sysusages
	where dbid = db_id() and segmap=3
select @iDataSize = @iDataSize / 512

select @iDataUsedSpace = sum(convert(dec(15,1),reserved))
	from sysindexes
	where indid in (0, 1, 255) and id != object_id('syslogs')
select @iDataUsedSpace = @iDataUsedSpace / 512

select @iDataFreeSpace = @iDataSize - @iDataUsedSpace	

select @iDataCapacity = (@iDataUsedSpace * 100) / @iDataSize

-- Convert the statistics to strings
select @sDataSize = str(@iDataSize,7,0) + ' MB'

select @sDataUsedSpace = str(@iDataUsedSpace,7,0) + ' MB'

select @sDataFreeSpace = str(@iDataFreeSpace,7,0) + ' MB'

select @sDataCapacity = db_name() +' data: ' + str(@iDataCapacity,3,0) + '% full  '

-- Create and display a message
select @sDataMsg = @sDataCapacity  +
	'(Total: ' + @sDataSize +
	'    Used: ' + @sDataUsedSpace + 
	'    Free: ' + @sDataFreeSpace + 
	')'
--print @sDataMsg

-- Do the log space calculations
select @iLogSize= sum(convert(dec(15,1),size))
	from master..sysusages
	where dbid = db_id() and segmap=4
select @iLogSize= @iLogSize/ 512

select @iLogUsedSpace = sum(convert(dec(15,1),reserved))
	from sysindexes
	where indid in (0, 1, 255) and id =  object_id('syslogs')
select @iLogUsedSpace = @iLogUsedSpace / 512

select @iLogFreeSpace = @iLogSize - @iLogUsedSpace	

select @iLogCapacity = (@iLogUsedSpace * 100) / @iLogSize

-- Convert the statistics to strings
select @sLogSize = str(@iLogSize,7,0) + ' MB'

select @sLogUsedSpace = str(@iLogUsedSpace,7,0) + ' MB'

select @sLogFreeSpace = str(@iLogFreeSpace,7,0) + ' MB'

select @sLogCapacity = db_name() + ' log:  ' + str(@iLogCapacity,3,0) + '% full  '

-- Create and display a message
select @sLogMsg = @sLogCapacity  +
	'(Total: ' + @sLogSize +
	'    Used: ' + @sLogUsedSpace + 
	'    Free: ' + @sLogFreeSpace + 
	')'
--print @sLogMsg

declare @sBothMsg 	varchar(255)
select @sBothMsg = @sDataMsg + char(13) + char(10) + @sLogMsg
print @sBothMsg

-- Put events in the application log if so desired
if UPPER(@sPutInEventLog) = 'LOG'
    Begin
	select @sLogEventMsg = @sDataCapacity + '    ' + @sLogCapacity
	if (@iDataCapacity > @iErrorMark) or (@iLogCapacity > @iErrorMark)

	    EXEC master.dbo.xp_logevent 50003, @sLogEventMsg, "Error"
	else
	    Begin
		If (@iDataCapacity > @iWarningMark) or (@iLogCapacity > @iWarningMark)	
		    EXEC master.dbo.xp_logevent 50002, @sLogEventMsg, "Warning"
		else
		    EXEC master.dbo.xp_logevent 50001, @sLogEventMsg, "Informational"
	    End
     End

GO