Database Maintenance Jobs Fail

Question

Database Maintenance Jobs Fail

dm_tapas

Hall of Fame

Points: 3515
More actions
September 6, 2011 at 12:56 am

#247578

I Have the job, DBA: Longrunningjobs, for monitoring long running jobs and it runs every 15 min.
Last few days it was not working fine . and I am getting the following error:
Message
Executed as user: ABC. File attachment or query results size exceeds allowable value of 10000000 bytes. [SQLSTATE 42000] (Error 22050). The step failed.

Viewing 8 posts - 1 through 7 (of 7 total)

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

dm_tapas Hall of Fame Points: 3515 More actions · Answer 1

I have changed default parameters By Clicking on Database Mail -> Select Configure DB Mail -> Next -> Select the radio button "View or Change System Parameters".

Still it is having the same error.

somebody give me a better solution for this error.

Thanks in advance.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

dm_tapas (9/6/2011)
I have changed default parameters By Clicking on Database Mail -> Select Configure DB Mail -> Next -> Select the radio button "View or Change System Parameters".
Still it is having the same error.
somebody give me a better solution for this error.
Thanks in advance.

How much have you increased?

Check it once again and bear in mind the values are byte.

Have you done this via t-sql? What is your attachment size?

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

dm_tapas Hall of Fame Points: 3515 More actions · Answer 3

I have added one extra zero to that. not through T-SQL

and I don't know what is the attachment size.

That was a error message. that has not mentioned about the attachment size.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

dm_tapas (9/6/2011)
I have added one extra zero to that. not through T-SQL
and I don't know what is the attachment size.
That was a error message. that has not mentioned about the attachment size.

Then via maintenance plan.

Adding one zero will give 95 MB. Add another one zero and try it.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

dm_tapas Hall of Fame Points: 3515 More actions · Answer 5

Again I added an extra zero that is "1000000000"

Still I am getting the error:

But it is different error.

Message

Executed as user: LHOCR\sqlssadmin. Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. [SQLSTATE 42000] (Error 14641). The step failed.

Again,

I execute sysmail_start_sp in MSDB database

Now the Job (DBA:LongRunningJobs) is not running after 15 mins. interval .

suggest me the possible things.

Thanks in advanced

dm_tapas Hall of Fame Points: 3515 More actions · Answer 6

I got this message in Database mail log

Message

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2011-09-07T12:30:14). Exception Message: Cannot send mails to mail server. (Failure sending mail.).)

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

dm_tapas (9/7/2011)
I got this message in Database mail log
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2011-09-07T12:30:14). Exception Message: Cannot send mails to mail server. (Failure sending mail.).)

1.Check is there anyone changed the Dbmail setup recently

2.Have you changed SQL server log on user account

3.SQL server service account has access to that path where the attachment stored.

Check the bottom of the post "Basic checks while setup the DBmail"

Basic checks while setup the DBmail[/url]

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/