[ QUOTE ]
Today we experienced an issue where a user submitted a UBE that inserted millions of records in a table. This caused the application using that table to slow down. We had to defragment and reindex the table to restore the performance back to normal.
Does anyone have a monitoring script in SQL that can alert me of such an exponential growth, compare the table size from previous day with the current table size and generate an alert if the table has grown more than 10%
[/ QUOTE ]
This will work for SQL 2000/2005. It has some items that have been deprecated for SQL 2008 that I have not gotten around to changing yet (sysfiles, sysindexes, etc.).
Also, xp_sendmail will work for SQL 2000 but if you are on SQL 2005/2008 you are going to have to use sp_send_dbmail and have Database Mail enabled.
Change database names and size alerting to your needs.
Description
===========
This job determines if hqpspddb1 tempdb is running out of space. There are four tempdb files,
each 10GB. In order to enable the proportional growth algorithm, autogrow is turned off for
these files. (
http://support.microsoft.com/kb/328551)
Details
=======
A SQL Agent job (E1 Alert Tempdb Growth Limit) runs every fifteen minutes. The job contains a
script that checks unallocated space and sends an email to itfss is the space drops below 10GB.
The script is based in part on sp_spaceused
<font class="small">Code:</font><hr /><pre>
use tempdb
DBCC UPDATEUSAGE('tempdb')
go
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @space dec (15,2)
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select @space = ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2))
if @space < 10000
EXEC hqpspddb1.master.dbo.xp_sendmail
@recipients = '
[email protected]',
@subject = 'HQPSPDDB1 tempdb below 10GB unallocated space',
@message = 'HQPSPDDB1 tempdb is below 10GB unallocated space and is set to not autogrow to enable proportional growth algorithm'
</pre><hr />