SQL Server Performance

Getting error in sql job but not when running script in query analyzer

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by sandeepsingh_05, Apr 19, 2013.

Thread Status:
Not open for further replies.
  1. sandeepsingh_05 New Member

    Dear friends,

    I need help with my index rebuild job and update_stats job. they are behavings trangely on a particular server.
    The update_stats job runs the following script -

    Code:
    SET ANSI_DEFAULTS ON
    GO
    SET ANSI_WARNINGS  OFF
    GO
    declare @db sysname
    declare @tsql varchar(MAX)
    
    declare cr cursor static for select name from master.
    sys.databases where name not in ('tempdb','master','model')
    open cr
    fetch cr into @db
    while (@@fetch_status = 0)
    begin
           set @tsql = ('SET ANSI_WARNINGS  OFF;'
        +  ' use ' + @db + ' exec sp_updatestats;'+' SET ANSI_WARNINGS  ON;')
        exec (@tsql)
        select @tsql
        print @tsql
        fetch cr into @db
    end
    deallocate cr
    GO
    SET ANSI_WARNINGS  ON
    GO
    SET ANSI_DEFAULTS OFF
    GO
    the script works perfectly when i run it manually in management studio - query analyzer but fail almost everytime when run in a sql job and i get the following error -

    Msg 8152, Sev 16, State 2, Line 21 : String or binary data would be truncated. [SQLSTATE 22001]
    Msg 3621, Sev 16, State 1, Line 1 : The statement has been terminated. [SQLSTATE 01000]

    This is happening only on 2 instances which are on a cluster in the whole environment with more than 200 sql instances. every where else it works fine.

    I also have a index rebuild job that fails on these 2 instances with above error and the same job works fine when i execute it in managenent studio. the same reindexing job works fine in all other servers. will send more details on reindexing in another thread.

    Thanks in advance.
  2. FrankKalis Moderator

    Please don't multi-post!
Thread Status:
Not open for further replies.

Share This Page