SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create an Alert for ROW Size increase


Create an Alert for ROW Size increase

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219035 Visits: 46279
stevewes (9/23/2013)
our Help desk ticketing application gets lots of email request into a particular table and seem to break when it gets to 100 !! so it's so we can notify the Help desk Admin guy before it does break.


I know I'm a little weird, but personally I'd be trying to figure out why it breaks and fix that...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47377 Visits: 10844
GilaMonster (9/25/2013)
stevewes (9/23/2013)
our Help desk ticketing application gets lots of email request into a particular table and seem to break when it gets to 100 !! so it's so we can notify the Help desk Admin guy before it does break.


I know I'm a little weird, but personally I'd be trying to figure out why it breaks and fix that...

I have to agree with Gail here - fix the problem instead of creating a band-aid to deal with it.

A short story to illustrate a point...
Some years ago, we had an email process to send email generated from an Oracle database. Since the database itself couldn't do email, it was handled by using a Unix ksh script. It also broke when it hit 100. It turned out that the Unix csplit allocated only 2 positions (not 2 bytes, but 2 positions) for an number internally, so we had to limit it to processing 99 at a time. It was re-done later so it didn't suffer from this limitation.

The moral of the story is to look everywhere for your limiting factor and fix the actual problem.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61131 Visits: 17954
stevewes (9/25/2013)
Hi Sean,

I have managed to get the query working and also have setup DB Mail and the Query now mails me if the rows are greater than 90 so all is well. The only thing I need to do now is put this query into a Trigger any tips on what the code should contain??

Thanks,

Steve:-)


Basically the logic I posted above in an insert trigger for the table in question. I will be happy to review your trigger if you want when you are done.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
stevewes
stevewes
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 41
Hi Sean, please can you check...

CREATE TRIGGER tgr_SM7EmailQ
ON TBL_EVENTOUTm1
AFTER INSERT
AS
BEGIN
SELECT COUNT(*) AS EMAILQFULL, [evtype]
FROM EVENTOUTM1
GROUP BY [evtype]
HAVING COUNT(*)>= 90
EXEC msdb.dbo.sp_send_dbmail @profile_name='profile name',@subject='Email Q Rows Filling up', @recipients ='email address',@body = 'The Row is over 90 please check'
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219035 Visits: 46279
Since there's no conditional checks, mail will be sent out every single time that trigger fires, no matter whether there are any groups over 90 rows or not.

Perhaps an IF EXISTS...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


stevewes
stevewes
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 41
Hi where would the IF condition be best placed as I really don't want a full mailbox..

Thanks

Steve.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219035 Visits: 46279
Maybe look in Books Online what the EXISTS does (and how the IF EXISTS works) and then give it a try?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


stevewes
stevewes
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 41
cheers will take a look
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search