locked
Agent Job Failing after changing Administrator Service Password RRS feed

  • Question

  • All,

    I've recently been given the job to change passwords for all Administrator accounts in one of our clients sites and it just so happens that one of the servers is a SQL server and the Agent service account is set to the domain\Administrator account which I updated the password on.

    I updated the password on the Services in Computer Management and also SQL Configuration Manager.

    I'm recieving this error on a few of the agent backup jobs.

    String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

    Any suggestions on how to resolve this problem?

    Thanks,

    Jim

    Saturday, May 5, 2012 10:23 PM

Answers

All replies

  • I think the password change is just a coincidence and has nothing to do with the job failing. The error means that you are trying to insert a row or update a row where the data type is smaller than the data. For example, trying to insert 26 characters into a varchar(25) column.

    Kathi Kellenberger

    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    • Proposed as answer by Naomi N Sunday, May 6, 2012 5:03 AM
    • Marked as answer by Kalman Toth Monday, May 14, 2012 5:21 PM
    Sunday, May 6, 2012 2:00 AM
  • Kathi suggests that the error message has nothing to do with the change. However, it could be that something is failing and the job tries to log the error somewhere - whereupon this fails, because the target column is too narrow.

    In any case, you need to look what is inside these jobs to determine what is going on.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, May 6, 2012 10:49 AM
  • So would I modify the database table that's having the issue and add more length to the column?

    I went directly to the database and I don't get the option to modify the size of the columns as they are grayed out.

    I looked into the job but I didn't see anything peculiar.

    Can you give me some insight as to where I need to look to resolve this issue or some troubleshooting tools inside SQL that can help me?

    Thanks in advance.

    Jim

    Sunday, May 6, 2012 11:43 AM
  • Apparently you were able to identify the table column that was too short. How did you do that?

    What is the text of the backup job?

    You should not alter the schema of any tables on a whim, as this can have large impact on the application. And you should definitely do this from a GUI in a production environment.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, May 6, 2012 12:29 PM
  • I identified the database that's having the issues by opening the history of the job that runs at night in SQL Management Studio.

    I then drilled down to the database that's having the issues.  I then looked at all the tables and there are only a few.

    I'm kind of lost as to what I need to do from here.

    Here is the text from the backup job:

    date 5/6/2012 3:10:00 AM
    Log Job History (load)

    Step ID 0
    Server servername
    Job Name backupjobname
    Step Name (Job outcome)
    Duration 00:00:00
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed blank
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    The job failed.  The Job was invoked by Schedule 1172 (Nightly load to USERS).  The last step to run was step 1 (STEP1_Load file).

    Date 5/6/2012 3:10:00 AM
    Log Job History (load)

    Step ID 1
    Server servername
    Job Name backupjob
    Step Name STEP1_Load file
    Duration 00:00:00
    Sql Severity 16
    Sql Message ID 3621
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: domain name\Administrator. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.



    • Edited by jimbo0607 Sunday, May 6, 2012 1:34 PM
    Sunday, May 6, 2012 1:33 PM
  • But what is the job doing! Sorry, we can't help you if you don't give us any information.

    Please open the job properties, select the Step page, Edit, and give us the text of the job.

    And it may have nothing to do with the tables in the database. It could be a temp table that has a column which is too narrow. There are also system function that gave produce this error when you need them too short input.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, May 6, 2012 3:24 PM
  • Oddly enough the problem seems to have resolved itself.

    Thanks,

    Jim

    Tuesday, May 8, 2012 4:53 PM
  • Probably there is a problem in the job, but you don't always get this overlong string, so it seems to work sometimes. But it could happen again.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 8, 2012 10:08 PM
  • The data might be changed. But you may get the error again. If the job fails again and you are running a procedure from this job then try to run the procedure from SSMS. You will get an idea what is causing the problem.


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Wednesday, May 9, 2012 1:59 AM