Scripts for Space Monitoring Report

The SQL to enter in the "Execute SQL Task"

if not exists(select * from master..sysdatabases where name='dbamaint')
create database dbamaint
go
alter database dbamaint
set RECOVERY SIMPLE
go

use dbamaint
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_filespacestats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_filespacestats]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--if not exists (select * from dbamaint.dbo.sysobjects where name = 'sp_filespacestats' and type='P')
create procedure sp_filespacestats
as
Begin
declare @dbname sysname
if not exists (select * from dbamaint.dbo.sysobjects where name = 'filespacestats' and type='U')
begin
CREATE TABLE dbamaint.[dbo].[filespacestats] (
[server_name] [sysname] NOT NULL ,
[dbname] [sysname] NOT NULL ,
[flag] [bit] NULL ,
[Fileid] [tinyint] NULL ,
[FileGroup] [tinyint] NULL ,
[total_space] [decimal](20, 1) NULL ,
[usedspace] [decimal](20, 1) NULL ,
[freespace] [decimal](20, 1) NULL ,
[freepct] [decimal](20, 1) NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FileName] [sysname] NULL ,
[report_date] [datetime] default getdate()
) ON [PRIMARY]
end
delete dbamaint.[dbo].[filespacestats]
declare cur_dbname cursor for
select name from master..sysdatabases


open cur_dbname

fetch next from cur_dbname into @dbname
while @@fetch_status=0
begin

if exists (select * from tempdb..sysobjects where name like '#datafilestats%')
drop table #datafilestats
create table #datafilestats
( dbname varchar(25),
flag bit default 0,
Fileid tinyint,
[FileGroup] tinyint,
TotalExtents dec (20, 1),
UsedExtents dec (20, 1),
[Name] varchar(50),
[FileName] sysname )

declare @string sysname

set @string = 'use [' + @dbname + '] DBCC SHOWFILESTATS with no_infomsgs'

insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents
, [Name] , [FileName])
exec (@string)

insert dbamaint.dbo.filespacestats (server_name,dbname, [flag],Fileid,FileGroup,total_space,usedspace,freespace,freepct,Name,FileName)
select @@servername,@dbname,flag,Fileid,FileGroup,(TotalExtents*64/1024),(UsedExtents*64/1024),
((TotalExtents*64/1024)-(UsedExtents*64/1024)),((TotalExtents*64/1024)-(UsedExtents*64/1024))*100/(TotalExtents*64/1024),Name,FileName from #datafilestats

fetch next from cur_dbname into @dbname
end
CLOSE cur_dbname
DEALLOCATE cur_dbname


if exists (select * from tempdb..sysobjects where name like '#logspacestats%')
drop table #logspacestats
create table #logspacestats
( dbname varchar(25),
flag bit default 1,
Totallogspace dec (20, 1),
usedlogspace dec (20, 1),
status char(1)
)

insert #logspacestats (dbname,Totallogspace,usedlogspace,status)
exec ('dbcc sqlperf(logspace) with no_infomsgs')
--dbcc sqlperf(logspace) with no_infomsgs
insert dbamaint.dbo.filespacestats (server_name,dbname,flag,total_space,freepct,Name)
select @@servername,dbname,flag,Totallogspace,(100-usedlogspace),'logspace_for_db' from #logspacestats
select * from dbamaint.dbo.filespacestats order by flag ,report_date desc

End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec dbamaint.dbo.sp_filespacestats

The text of Db_space_monitor.Bat

@echo OFF
@if not "%ECHO%"=="" echo %ECHO%

SET LANG=en_US.iso88591

rem *******************************************
rem Script : space_monitor.bat
rem Author : Neeraj Nagpal
rem Date : 09/26/2005
rem The script is used to monitor the space on all the DB servers.
rem It will send an email for database with less than 10% free space
rem *******************************************

rem Set Local scope and call main proc.

setlocal & pushd & Set RET=
set SCRIPTNAME=%~n0
set SCRIPTPATH=%~f0
set DBASQL=E:\dba\SQL
set DBAOUT=E:\DBA\out
set DEBUG=1

if "%DEBUG%"=="1" (set TRACE=echo) else (set TRACE=rem)
if /i {%1}=={/Help} (call :HELP %2) & (goto :HELPEXIT)
if /i {%1}=={/?} (call :HELP %2) & (goto :HELPEXIT)

call :MAIN %*
:HELPEXIT
popd & endlocal & set RET=%RET%
goto :EOF

rem ********************************************
rem Help Information Follows

:HELP
if defined TRACE %TRACE% [proc %0 %*]
echo The script is used to monitor the space on all the DB servers.
echo Usage : Double click

goto :EOF

rem *******************************************
rem Main Procedure will follow

:MAIN
if defined TRACE %TRACE% [proc %0 %*]


REM ************** Step 0 *** Delete the data in the table for current date ***************************

isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60 -w 3000 -i%DBASQL%\bef_space_mon.sql > %DBAOUT%\bef_space_mon.out


REM **************Step 1 *** Populate the exception report table ***************************

echo "*******Date=%date%::%time% || Start of the DTS step ************" >%DBAOUT%\db_space_monitor.log

dtsrun /S SQLSRVCNTREP /N space_mon /E >>%DBAOUT%\db_space_monitor.log


set err=%ERRORLEVEL%
echo Return code is %err%

echo "Return Code = %err%">>%DBAOUT%\db_space_monitor.log

echo "End of the DTS step" >>%DBAOUT%\db_space_monitor.log



REM **************Step 2 *** Run the sql to get the Space Monitoring report ***************************


echo "*******Date=%date%::%time% || Start of the Query execution step ************" >>%DBAOUT%\db_space_monitor.log


isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60 -w 3000 -i%DBASQL%\db_space_monitor.sql > %DBAOUT%\db_space_monitor.out

echo "*******Date=%date%::%time% || Sending exception report in email ************" >>%DBAOUT%\db_space_monitor.log

blat %DBAOUT%\db_space_monitor.out -t infy_dba@paramount.com,roberto_james@paramount.com,tammy_rogers@paramount.com -s "Space Monitoring Report" -server timeline -f neeraj_nagpal@paramount.com

The text of db_space_monitor.sql

set nocount on
use dbamaint
go
select getdate() as 'Report Generation Date'
go
print 'The following SQL Server databases have less than 10% free data space'
print '----------------------------------------------------------------------------'
print ''
print ''
select substring(server_name,1,15) as 'Server Name' ,substring(dbname,1,15) as 'Database',convert(varchar(12),sum(total_space)) 'Total space(MB)',convert(varchar(12),sum(freespace)) 'Free space(MB)' ,
convert(varchar(8),(sum(freespace)*100)/sum(total_space)) '% Free space' from db_all_spacestats
where flag=0 and datediff(dd,report_date,getdate())=0
and dbname not in ('model','master','msdb','tempdb')
group by report_date,server_name,dbname
having (sum(freespace)*100)/sum(total_space)<10
order by server_name, "% Free space" ,report_date desc
go
print ''

The text of bef_space_mon.sql

use dbamaint
go
print 'Deleting all the space monitoring data from same date'
go
delete db_all_spacestats where datediff(dd,report_date,getdate())=0
and datediff(mm,report_date,getdate())=0
and datediff(yy,report_date,getdate())=0
go

The query to get the growth rate statistics

select * from vw_sql_growth_mnth_qtr order by Used_growth_mb desc

Create view script of view vw_sql_growth_mnth_qtr

create view vw_sql_growth_mnth_qtr
as
select Server_Name,"Database",
convert(decimal(10,2),used_space_MB) -(select convert(decimal(10,2),used_space_MB) from vw_prm_dbspace_stats
where datepart(dd,report_date)=1
and datepart(mm,report_date)=case (datepart(mm,getdate()))
when 1 then 12
else (datepart(mm,getdate())-1)
end
and Server_Name=v.Server_Name
and "Database"=v."Database"
) as "Used_growth_mb" ,

(convert(decimal(10,2),used_space_MB) -(select convert(decimal(10,2),used_space_MB) from vw_prm_dbspace_stats
where datepart(dd,report_date)=1
and datepart(mm,report_date)=case (datepart(mm,getdate()))
when 1 then 10
when 2 then 11
when 3 then 12
else (datepart(mm,getdate())-3)
end
and Server_Name=v.Server_Name
and "Database"=v."Database"
))/3 as "Avg_per_month_grth_for_last_qtr" ,

convert(decimal(10,2),total_space_MB) -(select convert(decimal(10,2),total_space_MB) from vw_prm_dbspace_stats
where datepart(dd,report_date)=1
and datepart(mm,report_date)= case (datepart(mm,getdate()))
when 1 then 12
else (datepart(mm,getdate())-1)
end
and Server_Name=v.Server_Name
and "Database"=v."Database") as "Alloc_in_curr_month",
Total_space_MB as "Total_alloc_spc", Used_space_MB, Free_space_MB,report_date
from vw_prm_dbspace_stats v
where datepart(dd,report_date)=1
and datepart(mm,report_date)=datepart(mm,getdate())

Create view script of view vw_prm_dbspace_stats

create view vw_prm_dbspace_stats
as
select substring(server_name,1,15) as 'Server_Name' ,substring(dbname,1,15) as 'Database',
convert(varchar(12),sum(total_space)) as 'Total_space_MB',
convert(varchar(12),sum(usedspace)) as 'Used_space_MB',
convert(varchar(12),sum(freespace)) as 'Free_space_MB' ,report_date
from prm_all_spacestats
where flag=0
and dbname not in ('model','master','msdb','tempdb')
group by report_date,server_name,dbname