Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Create an Alert for ROW Size increase Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 4:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1498252
Posted Wednesday, September 25, 2013 7:33 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 4,293, Visits: 3,732
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
Post #1498353
Posted Wednesday, September 25, 2013 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 13,093, Visits: 12,574
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 Moden's 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)
Post #1498373
Posted Thursday, September 26, 2013 3:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 4:41 AM
Points: 11, Visits: 37
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'
Post #1498742
Posted Thursday, September 26, 2013 3:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1498744
Posted Thursday, September 26, 2013 3:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 4:41 AM
Points: 11, Visits: 37
Hi where would the IF condition be best placed as I really don't want a full mailbox..

Thanks

Steve.
Post #1498747
Posted Thursday, September 26, 2013 4:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1498756
Posted Thursday, September 26, 2013 4:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 4:41 AM
Points: 11, Visits: 37
cheers will take a look
Post #1498758
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse