SQL Server Performance Forum – Threads Archive
Alert Notification …URGENT Help Required
I want to create an alert for the following condition –if the number of rows in a table Exceeds 2M it should send me an email…
How can i do this…Please advice… Thanks,
B
Create a trigger on the table for checking the number of rows. As soon as it crosses 2M, generate a mail using XP_SENDMAIL. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
You could set a job that runs a count(*) on the table and if arrive to a certain range, send the alert. Schedule it to run as often as needed.
If you absoluely need the number to be 2M, then I guess trigger is your only option. Though I would not reccomand it as it will fire with each insert. A job even if run often would be a better choice. Make sure you use NOLOCK hint in the SELECT COUNT(*) not to cause lock problems.
You can create the job from EM (management/sql server agent/jobs). Bambola.
Well it will impact the performance of insert queries. So try this query for getting the number of rows…
SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID(Table_Name)
AND INDID < 2
I am not sure if I put the correct column names…. Also note this may not give you the current no. of rows. So if you want to be dead sure about that use sp_spaceused with updateusage set to true. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Gaurav
I tried this
SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID(emp_tag)
AND INDID < 2 Server: Msg 207, Level 16, State 3, Line 1
Invalid column name ’emp_tag’ I tried using Bambola’s suggestion but count(*) takes 5-10 mins to come back with the result… Please advice
-B
Sysindexe.rows give you information about number of rows in table and indexes. Luis
emp_tag is the table name …did I do something wrong….. SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID(emp_tag)
AND INDID < 2
I got this error Server: Msg 207, Level 16, State 3, Line 1
Invalid column name ’emp_tag’
You are missing the single quotes. SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID(’emp_tag’)
AND INDID < 2 Note that this will give an approximate number as it depends on sp_updatestats. If you can live with it it’s a faster choice. I’d still would go with a job though and not with a trigger. Bambola.
Well I tried it with the quotes SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID(’emp_tag’)
AND INDID < 2 and it did not return any rows …. So you are suggesting that I put the following in a job Select count(*) from emp_tag WITH (NOLOCK)
if count(*) > 2M then
use xp_sendmail to email
run this as a job on a schedule and get the job done?
try to update statistics and see if it changes. Bambola.
In order to perfomance I suggest the following: select si.rows, so.name as TableName, si.id from sysobjects as so
join sysindexes as si
on so.id = si.id
where so.name = ’emp_tag’ and si.indid = 0 Luis Martin
it gives me an error UPDATE STATISTICS emp_tag Server: Msg 2706, Level 11, State 6, Line 1
Table ’emp_tag’ does not exist.
Should I create statistics….on the table IF YES , Is this the syntax…..
CREATE STATISTICS emp_tag
ON DB..emp_tag
WITH FULLSCAN, NORECOMPUTE
About error: be sure you are working with rigth database. Luis Martin
I was thinking about the same thing, Luis. bzeebee – If you are using QA run use database_name to make sure you are using the correct database, or select db_name() to find out where you are <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Bambola.
Its definitely the right db
dbserver – ARGONE
dbname – DBONE use DBONE
UPDATE STATISTICS emp_tag Server: Msg 2706, Level 11, State 6, Line 2
Table ’emp_tag’ does not exist.
So table does not exist on the database…
can you see it in sysobjects? maybe you did not spell the name correctly… Bambola.
select * from sysobjects where name = ’emp_tag’ I get one row for this
Any chance we are talking about a view?
what does this return select xtype from sysobjects where name = ’emp_tag’ Bambola.
xtype = ‘U’
its definitely a table
Try to run a maintenance job to test the table and database.
I can’t understand what is goin on. Luis Martin
works fine…. job ran successfully … queries run fine…. update stats does not work though…… i just need to get an email when the count of rows in the table exceeds 2million
Ok. That was the first problem, I write, few post ago, scrip to get the numbers of rows in emp_tag table. After that we go to the statistics problem. So, run a job with script and XP_sendmail and see what is goin on. Luis Martin
What about the owner of the table? maybe it’s not dbo…. Bambola.
update stats worked when I user user.table_name
thanks bambola ..I guess i overlooked that one.. luis I used this query you gave:- select si.rows, so.name as TableName, si.id from sysobjects as so
join sysindexes as si
on so.id = si.id
where so.name = ‘user.emp_tab’ and si.indid = 0
It does not return any rows inspite of me having done the update stats
Try this select si.rows, so.name as TableName, si.id
from sysobjects as so
join sysindexes as si
on so.id = si.id
where so.name = ‘syslogd’
and si.indid < 2
and so.type = ‘u’
Bambola.
Why do we need this join? Why can’t we get the information from sysindexes only? Use this and post what is the result?
SELECT rows FROM SYSINDEXES
WHERE ID = OBJECT_ID(‘user.emp_tag’)
AND INDID < 2
I am assuming user is the owner of the object. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
gaurav,
the query returned the number of rows.. so now I need to use xp_sendmail to email me right? thanks
Yes you do…. See if http://www.exchangeadmin.com/Articles/Index.cfm?ArticleID=14309
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6hbg.asp
http://www.sqlservercentral.com/columnists/kdas/xp_sendmail.asp
is of any help… Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Thanks Gaurav, Bambola and Luis for your valuable time to help me solve this issue….. -B
]]>