Alert on available space in datafile
-
I use the sp_spaceused to find the available space in the datafile (due to autogrow problems).
How can I create the alert (there is a perf counter for Percent Log used (under the Database Counter)). Is there anything similar to use for the datafile?
Should this be a trigger that runs the alert? I haven't worked much with this so any hints are appreciated.
Runaldo
-
I have this query running every 15 Minutes with sqlagent.
It writes a message to winnt-eventlog which is captured by HPOVow
Primitive, but it works.
declare @DB_Criterium int
/* ** criterium % must be free ** */
set @DB_Criterium = case @@servername
when 'KW1' then 10
when 'SRSF1' then 5
when 'DB04' then 2
else 5
end
declare @DB_FileNaam varchar(30)
declare @intFileSizeMb int
declare @intPctFree int
declare @intCurrentPctFree int
declare @SumReservedMB as float
declare @FileSizeMB as float
declare @MaxSizeMB as float
declare @CurrentPctFree as float
declare @TotalPctFree as float
declare @PctFreeToExpand as float
declare Csr_FileOvz cursor local Fast_Forward FOR
select Sum_reserved_MB, File_Size_MB, MaxSizeMB, 100 - ( Sum_reserved_MB / File_Size_MB * 100) as Current_Pct_Free, 100 - ( Sum_reserved_MB / MaxSizeMB * 100) as Total_Pct_Free, Pct_Free_To_Expand, Naam
from (
select sum(convert(float,size) * 8 / 1024) as File_Size_MB -- ( size / 1024 * 8) as File_Size_MB
, sum((case maxsize
when -1 then maxsize
else convert(float, maxsize) * 8 / 1024
end)) as MaxSizeMB
, sum((case maxsize
when -1 then Null
else 100 - (( convert(float,size) * 8 )* 100 /(convert(float, maxsize) * 8))
end)) as Pct_Free_To_Expand
, min(name) as naam
from sysfiles
where name like '%data%'
 
FileSizes, (
select sum(reserved_KB) / 1024 as Sum_reserved_MB
from
(select reserved * d.low / 1024 as reserved_KB, a.id as ObjectName_ID
from master.dbo.spt_values d
, (select id, sum(convert(float,reserved)) as Reserved
from sysindexes
where indid in (0, 1, 255)
group by id
) a
where d.number = 1
and d.type = 'E'
 
PagesReserved) SizePagesReserved
Open Csr_FileOVz
FETCH NEXT FROM Csr_FileOVz INTO @SumReservedMB, @FileSizeMB, @MaxSizeMB, @CurrentPctFree,@TotalPctFree, @PctFreeToExpand, @DB_FileNaam /*Get the 1st row*/
WHILE @@fetch_status=0 /*set into loop until no more data can be found*/
BEGIN
IF not @@fetch_status = -2
BEGIN
IF @TotalPctFree is null
BEGIN
print 'Grootte [' + ltrim(str( @FileSizeMb )) + '] MB '
END
ELSE
BEGIN
set @intpctfree = @TotalPctFree
set @intCurrentPctFree = @CurrentPctFree
IF @TotalPctFree < @DB_Criterium
BEGIN
raiserror ('ALZDBA_Check_FreeSpace : Database < ''%d'' pct free : CurrentPctFree ''%d'' , TotalPctFree ''%d'' , Db ''%s''. Verwittig ALZDBA.',
16, 1, @DB_Criterium, @intCurrentPctFree, @intPctFree, @DB_FileNaam ) with log
END
ELSE
BEGIN
Print 'FileSize '+ ltrim(str(@FileSizeMb)) + 'MB / MaxSize ' + ltrim(str(@MaxSizeMb)) + ' / Pct_Free : CurrentPctFree [' + ltrim(str( @intCurrentPctFree )) + '] <> TotalPctFree [' + ltrim(str( @intpctfree )) +']'
END
END
END
FETCH NEXT FROM Csr_FileOVz INTO @SumReservedMB, @FileSizeMB, @MaxSizeMB, @CurrentPctFree,@TotalPctFree, @PctFreeToExpand, @DB_FileNaam /* get the next row*/
END
Close Csr_FileOVz
DEALLOCATE Csr_FileOVz
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I've created a SQL Server Performance Condition Alert that was based on file size rather % used on an old server I have.
It mails me when the file size of the tempdb exceeds 'n' KB, I've also triggered it to fire a dbcc shrinkdb cmd too. In case it reaches it's limit over the weekend or over night.
-
Thanks - I'll use your input and the sp_spaceused (I forgot i could just get the code :hehe
and try get a script working.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply