Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147

    Red face Unanswered: Trigger gone mad.

    Ok,

    I recently had to rebuild a SQL server, this was due to a corrupted Win2k server.

    I backed up all the databases on storage media.

    When I restored the SQL database, one of the tables had a trigger within it.

    It was to email a specific person when a stock reached a low level.

    It has gone mad!!!

    Its producing 5 emails per second, and has been doing so for the last 6 hours...

    It has been removed from the network, but still fires away, I can see them within the Inetpub mailroot queue folder.

    Anyone any ideas on how to kill/purge/stop/assasinate this !

    I have also removed the SQL database, but its still creating them ! Mass spamming from beyond the grave or what ?

    Many thanks for any suggestion.

    Ken

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on what little information you've provided, the best I can say is: "Makes sense". There's nothing in your analysis that I can disagree with, so without the trigger source code all I can do is agree.

    Drop the trigger to make the behavior stop. Post the trigger here if you'd like help fixing it.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Doesn't make sense to me that the trigger would fire just from the restoring the database. Obviously some transaction must have occured against the table after it was restored, and perhaps some process is still making modifications to the table.
    Turn off the trigger, of course, but also check what other processes are running on the database.
    Are other people logged into the database? If so, try bringing it up in single-user mode to debug the issue.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2006
    Posts
    6
    Try restarting the sql mail or whichever mail server you are using..

  5. #5
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147

    Sorted..

    Ok,

    Basically what looks to have happened is that the trigger fired as soon as the database was restored.

    It seemed to have gone through every item and stock level as they were being rebuilt.

    96,435 emails !!! Seems every combination of product, brand and item.

    To stop the emails I removed the network cable. Then left it ro finish its little rant over night.

    First thing next day checked the wwwroot > mail > queue folder and deleted said items.

    I'll make a mental note however, that if I ever backup-restore a database, I'll remove all the triggers first to something like notepad, save them as text. Then when restored, I'll copy them back in.

    I really appreciate all your help and suggestions guys and gals - Happy Easter.

    Best regards

    Ken

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Triggers fire when data is modified, NOT when a database is restored.
    Something else is going on.
    Did you restore an empty copy of the database and then load data into it?
    Did you restart the server? It's possible that one of the stored procedures is set to auto-exec using the sp_procoption setting.
    Post the code of the trigger so we can take a look at it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •