SQL script to alert table growth

Joel

Well Known Member
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 ]
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 />
 
[ 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 ]

You can also bang around with this to get start and current sizes:


<font class="small">Code:</font><hr /><pre>
--For jde_production:

--SQL 2000

--BEGIN
Select
cast(alt.filename as char(25)) as 'Filename',
cast (alt.name as char(10)) as 'Database Name',
alt.size * 8.0 / 1024.0 AS [Originalsize (MB)],
files.size * 8.0 / 1024.0 AS [Currentsize (MB)]
FROM master.dbo.sysaltfiles alt
INNER JOIN jde_production.dbo.sysfiles files
ON alt.fileid = files.fileid
WHERE alt.dbid = (SELECT sysdatabases.dbid
FROM sysdatabases
where sysdatabases.name = 'jde_production')
--END



--SQL2005

--BEGIN
Select
cast(alt.filename as char(25)) as 'Filename',
cast (alt.name as char(10)) as 'Database Name',
alt.size * 8.0 / 1024.0 AS [Originalsize (MB)],
files.size * 8.0 / 1024.0 AS [Currentsize (MB)]
FROM master.dbo.sysaltfiles alt
INNER JOIN jde_production.dbo.sysfiles files
ON alt.fileid = files.fileid
WHERE alt.dbid = (SELECT sys.databases.database_id
FROM sys.databases
where sys.databases.name = 'jde_production')
--END </pre><hr />
 
[ 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 ]

You could even mess around with these to get alerts on database files that are not set to autogrow and are running out of space:

<font class="small">Code:</font><hr /><pre>
use tempdb
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))


print 'Space available in tempdb files:'
print @space
<font class="small">Code:</font><hr /><pre>



SQL 2005
========
<font class="small">Code:</font><hr /><pre>
use tempdb
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'
FROM sys.database_files;
</pre><hr />
 
Thanks for the reply Jeff. We have similar scripts in place for temp db alerts and database growth as a whole. I was looking for table specific growth. Any abnormal growth in a particular table that can generate an alert... we are on SQL 2005
 
[ QUOTE ]
Thanks for the reply Jeff. We have similar scripts in place for temp db alerts and database growth as a whole. I was looking for table specific growth. Any abnormal growth in a particular table that can generate an alert... we are on SQL 2005

[/ QUOTE ]

Ahhh, table level.

Get a row count, put it in a temporary table, get a second row count, subtract it from the last count, alert on a large enough difference.


Something like this guy does in steps 1 and 2 here: http://myitforum.com/cs2/blogs/jnelson/archive/2007/10/12/106745.aspx but run a script in SQL Agent to alert on a certain increase in records instead of merely tracking it.

Contact me if you want me to write this for you.
 
I found this one script on a forum which gives the growth in percentage as compared to the last backup. It uses the backup information stored in msdb to generate the difference. May work for production databases that are backed up daily. Still not what I was looking for but thought I share it as someone else might find it useful
------
Link http://www.jdebase.com/forum/showthread.php?tid=8&pid=12#pid12
-------
 
[ 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 ]

If it's fragmentation (filesystem and/or index) that you are concerned about, consider the following:

1- Database files should be pre-sized far above their expected terminal growth. Autogrowth of database files (particularly in small increments) is a really great way of massively fragmenting your filesystem.

2- There are scripts out there that will perform an index rebuild depending on the amount of fragmentation. It's a kludgy way of doing what you want but it will work if this is how you wish to do it. You can set up an Agent job to run these scripts every x minutes and it will handle a massive insert or update. However, depending on the timing this rebuild could happen during the insert. I highly recommend against doing it this way. Best to be alerted to table growth and handle it after the fact.
 
Back
Top