Create an Alert for ROW Size increase

  • Hi being a newbie, i have been asked by one of our Apps admin guys to create an alert that when a table rowsize gets to 90 rows to send him an email or log an event in system event viewer (windows server) I have written a small query which tells you how many rows there are but I am stuck as to how to get it to do something meaningful.

    Help!!!

  • you can create a SQL job for this

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Hi, how do I do this ?

    my select statement goes something like this

    Select count(*) AS EMAILQFULL,column name from tablename

    group by column name

    this returns the number of rows in the table, but I only want it to notify me if there are more than 90 rows this is the part I am stuck on.:(

  • stevewes (9/23/2013)


    Hi, how do I do this ?

    my select statement goes something like this

    Select count(*) AS EMAILQFULL,column name from tablename

    group by column name

    this returns the number of rows in the table, but I only want it to notify me if there are more than 90 rows this is the part I am stuck on.:(

    90 rows for each group or 90 rows in the entire table?

    I am guessing you have multiple groups here? Just add a HAVING clause.

    Does this help?

    Select count(*) AS EMAILQFULL, [column name ]

    from tablename

    group by [column name]

    HAVING COUNT(*) >= 90

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • yeah this is a great help many thanks!!!

    next step is to I suppose create a Job with the SQL query in as a step the next step is how do I then if it reports greater than 90 then send an alert do I need an IF statement ??

  • stevewes (9/23/2013)


    yeah this is a great help many thanks!!!

    next step is to I suppose create a Job with the SQL query in as a step the next step is how do I then if it reports greater than 90 then send an alert do I need an IF statement ??

    I am not sure that I would use a sql job for this. How often will this job need to run? I guess the answer to that is how quickly do you need the notification to be sent? If it is once a day then a job would be fine but if you need the notification real time then a job is probably not the best choice. For real time notification a trigger might be a better choice.

    Either way the sql would need to have an EXISTS.

    IF EXISTS(The query that determines if there are rows > 90)

    SendNotificationHere

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi Sean,

    That is great info, yeah the info needs to be real time 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 think a Trigger probably is best, the SendNoticationsHere do I then put in "email address" ?? is there any other thing I need to check? I am using SQL 2005 Enterprise 32bit.

    Many THX

    Steven

  • stevewes (9/23/2013)


    Hi Sean,

    That is great info, yeah the info needs to be real time 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 think a Trigger probably is best, the SendNoticationsHere do I then put in "email address" ?? is there any other thing I need to check? I am using SQL 2005 Enterprise 32bit.

    Many THX

    Steven

    That was very rough sketch pseudocode. You will need to create and send an email there.

    Here is a good place to start on that. http://technet.microsoft.com/en-us/library/ms190307%28v=sql.90%29.aspx

    That will show you how to create the email and send it. You will also have to enable that feature. It explains that in the article as well. Let me know how you get along.

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Many Thanks Sean,

    I will pick this up again tomorrow............

    luckily I have a test server to try it out on will read the article and go from there. I have created the Trigger just need to sort out the code within.

    Thanks again for your help today.

    Will let you know how I get on.

    Steven:-)

  • 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:-)

  • 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
  • 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.

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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'

  • 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

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply