SQL Server Performance

Update Stats script fails in sql job but works fine when run manually in query analyzer

Discussion in 'ALL SQL SERVER QUESTIONS' started by sandeepsingh_05, Apr 19, 2013.

  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 -

    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. Also i want to add there whener i run the code in query analyzer manually it leaves an open transaction and
    i have to commit it.

    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.
    Sandeep Singh
  2. FrankKalis Moderator

    Looks like the same error as in your "reindex" thread. See, if the same solution helps here as well.

Share This Page