none
Can a big amount of unsent emails cause msdb failure? RRS feed

  • Question

  • Dear All,

     

    A few days ago I have a case.  Our msdb was failed. I started investigating the case and saw following.

    The SQL server uses a job to send emails.

    One email contains big amount of data (one html page).

    It uses to send about 50000 emails and more per session one after another having no delay.

    Msdb data file was about 1.5G large.

    There was a lot of space on the drive.

    I also saw that SMTP server data were wrong so no email was sent.

    It had generated a lot of errors “Can not find the server”. 

    At a moment it had raised error severity 24.

    It had repeated that error a couple of times increasing error state from 1 to 5.

    Finally it had raised “Start recovering msdb”.

    When I saw the database its status was normal.

    So I need to know can it happen just because wrong SMTP server data?

     

    Thanks in advance,

    Rosen Rusev

    Friday, December 28, 2007 8:09 AM

All replies

  • Hi,

     

    As you stated all the unsent mails will be saved in a table sysmail_unsentitems.  You can refer the below link in BOL for further.

     

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/eb290f24-b52f-46bc-84eb-595afee6a5f3.htm

    --Vidhya Sagar

     

    Monday, December 31, 2007 2:13 PM
    Moderator
  • Hi Vidhya,

     

    I'm looking to find out why this had happend?

    Having the answer we shall prevent such a problem in future.

     

    Thanks

     

    Wednesday, January 2, 2008 8:26 AM
  • Vidhya,

    First off, failing emails will not cause MSDB to fail unless the database grows as a result of the unsent items to the point that it runs out of disk space.  Even then, it will only stall, and will not be corrupt.

     

    Secondly, you start by saying that the MSDB failed, but at the end you say that when you looked, the database status was fine.  Can you describe more clearly what symptoms you saw in MSDB?

     

    Thanks, Kevin

     

    Thursday, January 3, 2008 6:32 PM
    Moderator
  • Hi Kevin,

    I said that after repairing database status was normal.
    I'm still looking to find the answer.
    Why our sql server crashed in this way?

    Regards
    Saturday, January 12, 2008 8:09 AM
  • Hi Kevin,

    In my case my MSDB went around 25 GB, while checking the db i can see that Internal tables has occupied a lot of space in MSDB database. i.e The mails which i sent from the server have been queued in service broker, but sql server was unable to receive this from the Q and process it thus causing internal tables (eg table name. queue_19866653) to grow huge. I've manually received those rows from the queue and then shrinked MSDB database to its minimum. Your views are greatly appreciated.

    Thanks.
    Saturday, January 12, 2008 10:22 AM
    Moderator
  • From a maintenance point of view, how would you clean up these files so that they would not get this big.

     

    Saturday, January 26, 2008 3:39 PM
  • Hi,

     

    I've used the below command in MSDB db for manually receiving those messages out of queue.

    Code Snippet
    receive
    top(N) * from loginqueue

     

     

     

    Monday, January 28, 2008 12:11 PM
    Moderator
  • Does this actually remove the old email messages? Or do you have to run a delete command to tidy up all old emails alerts

     

    Monday, January 28, 2008 2:08 PM
  • In my case Internal Tables occupied lot of space and hence used the above command to receive the same. Is this the same situation exists in your case?? If yes execute the above script alone no need to run delete command.

    Tuesday, January 29, 2008 11:34 AM
    Moderator
  • Did not know that thanks very much.

     

    Tuesday, January 29, 2008 3:00 PM