Motherofallgeeks

I recently had someone ask me how to I see my actual usage of data and index files without using the GUI.  Well I am going to tell you that and I am also going to tell you how to set up a monitor job to alert  you when your file is approaching it’s capacity.  Why would you want to do that when you have AutoGrow? The first reason is that Auto grow will trigger an ALTER Database statement. You have no control over the time of day that this alter will fire.  Alter will Lock your system up until it has completed. This type of maintenance should be monitored and performed at a time when system impact will be less.  If auto grow is enabled and you are not monitoring your database space you could eat up all of the drive space and crash the server. DBA’s should be aware of how fast your systems are growing and proactively add the space as needed.

That being said. This script was developed for SQL Server 2005. It uses undocumented processes which are not recommended for use by microsoft because they may be removed at anytime.

There is an undocumented DBCC command which has been around for quite some time. We will use this command dbcc showfilestats as the basis of our script.

We are going to create a system stored procedure. That way you can run this proc from any database without having to create it in all databases. It makes maintenance much easier. You can schedule it to run from SQL Agent every 30 minutes and it will send you a mail based on your file reaching 90%.

Enjoy!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*******************************************************************************************
* proc_checkspace
* Created By: MotherofallGeeks
* Created Date: March 2008
* Purpose check available Free space in database and send alert
* exec proc_CheckSpace ‘MyMailProfile’ , ‘Mother@motherofallgeeks.com’
*/

CREATE PROC [dbo].[sp_CheckSpace]

@MailProfile varchar(max),
@recipients varchar(max)
AS
BEGIN
SET NOCOUNT ON

DECLARE @DataUsedPercent float
DECLARE @IndexUsedPercent float
DECLARE @FileName varchar(max)
DECLARE @Size numeric
DECLARE @IndexSize float
DECLARE @UsedSpace float

DECLARE @tmpspc table
(Fileid int, FileGroup int,
TotalExtents int,
UsedExtents int,
Name sysname,
FileName nchar(520))

INSERT @tmpspc EXEC (‘dbcc showfilestats’)

SELECT
@FileName = s.physical_name ,
@Size = convert (dec (15,2),s.size) * 8192 / 1048576,
@UsedSpace =CAST(tspc.UsedExtents*convert(float,64) /1024 AS float) ,
@DataUsedPercent =(CAST(tspc.UsedExtents*convert(float,64) /1024 AS float) / (convert (dec (15,2),s.size) * 8192 / 1048576) ) * 100
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN @tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N’PRIMARY’)

IF (@DataUsedPercent > 90)
BEGIN
DECLARE @Body varchar(max)
SET @Body = db_name()+’ Free Space is < 10% in the following File: ‘ + char(13) + @FileName +char(13)+
+ ‘Size MB =’+convert(varchar(10),@Size) + ‘ Used Space MB = ‘+convert(varchar(10),@UsedSpace)
+ ‘ %Used = ‘+ convert(varchar(10),@DataUsedPercent)

exec msdb..sp_send_dbmail @profile_name = @MailProfile
, @recipients = @recipients
, @subject = ‘Free Space Alert’
, @body_format = ‘TEXT’
, @body = @Body
,@query_result_header = 0
END

SET @FileName = ”
SET @Size = 0
SET @UsedSpace =0

SELECT
@FileName = s.physical_name ,
@Size = convert (dec (15,2),s.size) * 8192 / 1048576,
@UsedSpace =CAST(tspc.UsedExtents*convert(float,64) /1024 AS float) ,
@IndexUsedPercent =(CAST(tspc.UsedExtents*convert(float,64) /1024 AS float) / (convert (dec (15,2),s.size) * 8192 / 1048576) ) * 100
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id() and (s.drop_lsn IS NULL
)) AND (s.data_space_id=g.data_space_id)
INNER JOIN @tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N’INDEX’)
IF (@IndexUsedPercent > 90)
BEGIN
SET @Body = db_name()+’ Free Space is < 10% in the following File: ‘ + char(13) + @FileName +char(13)+
+ ‘Size MB =’+convert(varchar(10),@Size) + ‘ Used Space MB = ‘+convert(varchar(10),@UsedSpace)
+ ‘ %Used = ‘+ convert(varchar(10),@indexUsedPercent)

exec msdb..sp_send_dbmail @profile_name = @MailProfile
, @recipients = @recipients
, @subject = ‘Free Space Alert’
, @body_format = ‘TEXT’
, @body = @Body
,@query_result_header = 0
END

END

GO

EXECUTE sp_ms_marksystemobject '[sp_CheckSpace]'