Date: 03/18/11 (SQL Server)    Keywords: no keywords

In our last two topics, we discussed gathering the space statistics.

Now we need to devise a procedure called sp_check_usage that will check our statistics and calculate when we expect to run out of space.  We will also use a 2 new tables called drive_check_log and server_space_report to record our data for reporting purposes.  For the first day, we will not have any data to report because we will need a second day's worth of statistics to compare the data against.

Now the new procedure will make a couple of passes at the stored data, use some temp tables to do a comparison, then will write a report you can send or review.

CREATE PROCEDURE [dbo].[sp_check_usage]
as

set nocount on

declare @since_date datetime,
@rc1 INT,
@files INT,
@cmd VARCHAR(256),
@datfile1 VARCHAR(128),
@subject nvarchar(500)

set @since_date = dateadd(d,-7,getdate())

SELECT @datfile1 = '\RPT' + CONVERT(CHAR(8),GETDATE(),112) + '.CSV'

-- group by days
select
t1.drive,
datestamp=convert(varchar,datestamp,111),
last_sample_id=max(t2.id),
MBFreeSpace= sum(MBFreeSpace) / count(*),
sample_count = count(*)
into #x1
from free_space_log t1 inner join drive_check_log t2
on t1.id = t2.id
where t2.datestamp >= @since_date
group by t1.drive, convert(varchar,datestamp,111)

-- uncomment to debug
--select * from #x1

-- get a days growth
select
t1.drive,
avg_per_day =
sum(t2.MBFreeSpace - t1.MBFreeSpace ) / count(*)
into #x3
from #x1 t1 inner join #x1 t2 on
t1.drive = t2.drive and
t1.datestamp = convert(varchar,dateadd(d,1,t2.datestamp),111)
group by
t1.drive

select * from #x3

-- get the latest sample date
select
t1.drive,
last_sample_id=max(t2.id),
last_sample_date=max(t2.datestamp),
CurrentMBFree=convert(float,0)
into #x2
from free_space_log t1 (nolock) inner join drive_check_log t2 (nolock)
on t1.id = t2.id
group by
t1.drive

-- set the current free space

update #x2 set
CurrentMBFree = MBFreeSpace
from free_space_log t1 (nolock)
where
#x2.drive = t1.drive and
#x2.last_sample_id = t1.id

select * from #x2

--This is where the procedure produces the output

truncate table server_space_report

insert server_space_report ( drive,
last_sample_date,
avg_per_day,
CurrentMBFree,
days_left)
select
#x2.drive,
#x2.last_sample_date,
#x3.avg_per_day,
#x2.CurrentMBFree,
days_left =
case when #x3.avg_per_day = 0 then 'low or no usage'
when #x3.avg_per_day < 0 then 'negative usage'
else 'Out of space in ' +
replicate(' ', 6 - datalength(convert(varchar, round((#x2.CurrentMBFree / #x3.avg_per_day),0)))) +
convert(varchar, round((#x2.CurrentMBFree / #x3.avg_per_day),0)) + ' days'
end
from #x2
inner join #x3 on
#x2.drive = #x3.drive
order by #x2.drive

-- uncomment to debug
-- select * from server_space_report

set nocount off
GO


** Please be responsible with free code. Test and check before implementing in a production environment

Source: https://sqlserver.livejournal.com/74374.html