Is there a memory size limit in SQL Agent?

Question

Is there a memory size limit in SQL Agent?

Allan Chen

SSC Veteran

Points: 238
More actions
April 26, 2007 at 2:11 pm

#72809

Hi,
Recently, we noticed that one of the job in our development server has been failing. The job has one step which executes a main stored procedure. Within the main stored procedure, it calls many other stored procedures.
The weird thing about this is that this job run's fine in a production server and it runs fine if we execute it in SQL analyzer.
I have used the Profiler to monitor it. It shows the execution stops in the middle of a called stored procedure. If I commented out some of codes in front of the code where it stoped, the execution go further down. If I commented out other called stored procedures in front of the one it stopped, it execute more stored procedures down. But, it will stop at some point....It looks like there is size limit when this stored procedure is executed by a SQL Agent.
Does anyone have any idea why this is happening?
Any comment would be greatly appreciated!
Thanks,
--Allan

Viewing 6 posts - 1 through 5 (of 5 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

So does the job actually fail or does it just appear hung? If it does fail, what is the failure message? If it just appears hung, have you looked at what is happening on the server at that time, such as disk I/O, memory counters, paging, etc?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Allan Chen SSC Veteran Points: 238 More actions · Answer 2

The job failed with the following message at the end of the log file:

Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE

22001]

Msg 3621, Sev 16: The statement has been terminated. [SQLSTATE 01000]

============

However, Msg 8152 is just a warning message. As I see from the Profiler, it continues to execute statments after the update statment that caused this warning message.

Thanks,

--Allan

Antares686 SSC Guru Points: 125444 More actions · Answer 3

It is only limited by available memory on the server. From your message it looks like you have a character string which when it tries to copy the value in doesn't have enought room to recieve thus it fails. Since you are using profiler, look at the values for the statement just before the error to see if you can spot the item.

Allan Chen SSC Veteran Points: 238 More actions · Answer 4

Found the problem...you are right, it was caused by the update statement that is trying to update a column (varchar8) with data from SYSTEM_USER. The reason, it runs fine in Query Analyzer is because all users have their login 8 chars or less; whereas when run by a job, the SQL agent use a special account which has 10 chars long, which caused the failure.

Thanks,

--Allan

Antares686 SSC Guru Points: 125444 More actions · Answer 5

One thing I would add is that even thou you may not run into it user names can be up to 128 characters, I would either use 128 as the definition or if you use something smaller allow your parameter to be 128 and do a left (or right if you might have a domain name involved) to control the limit so you don't run into any curve balls later. Preference is size to 128 thou.