none
SQLSTATE 01000

    Question

  • In what circumstances do we get the below error. Shouldn't it be a little more descriptive?

    The statement has been terminated. [SQLSTATE 01000]

    This is for an SQL SERVER 2005 SP2. I am running a rebuild index using a TSQL job. The rebuild runs fine for a while giving this error later.
    Wednesday, December 23, 2009 6:57 AM

Answers

All replies

  • This is a generic message as you have already figured out and should be preceeded by a more detailed error which should be displayed in the SQL Agent job history log. If you have a profiler trace from that time, an additional error should also be present.

    This error message can also pop up when you have some inconsistencies in the database. Could you run a dbcc checktable on the table whose index you were rebuilding and ensure that there are no inconsistencies reported.

    HTH
    This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
    Wednesday, December 23, 2009 10:43 AM
  • The job history log is not complete either. The errorlog also doesn't state anything

    ". ...contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execu...  The step failed."


    And the good part is, when I ran the proc manually it ran fine. i.e. from sql management studio

    Very perplexing behaviour
    Thursday, December 24, 2009 1:51 AM
  • When you create a maintenance plan you have the option to logging the details.
    Can you check your maintenance plan and take the logging path, go to that path and open the last executuion log file to get the error. Else go the maintenance plan and right click on the plan to view the history.
    Vidhya Sagar. Mark as Answer if it helps!
    Thursday, December 24, 2009 2:42 AM
    Moderator
  • Hi VidhyaSagar,
    I am not using maintenance plan. I am using a TSQL proc.

    Thanks
    Thursday, December 24, 2009 2:50 AM
  • Hi,

    To troubleshoot the issue, could you provide us the error messages logged in the SQL Server error log files. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

    If there are any more questions, please let me know.
    Thanks and Merry Christmas.

    ***Xiao Min Tan***Microsoft Online Community***
    Friday, December 25, 2009 7:25 AM
    Moderator
  • You mention "job history log" so I infer that this is a scheduled job from SQL Agent. 

    If so, you can edit the Advanced Properties of the Job Step.   There you will see options for retaining more log information.  For instance, you can select "Log to table" which will log information from running the job into a table in msdb.

    This may provide details missing from the logs that you are examining.

    After the job runs, you can come back to the Advanced Properties tab and click the View button to see what details were recorded.  Or you can select it out as:

    SELECT j.Name, s.Step_Name, s.Step_id, l.log_size, RIGHT(l.log, 50000) AS recent_log
    FROM msdb.dbo.sysjobs j JOIN msdb.dbo.sysjobsteps s
      ON j.job_id = s.job_id
      JOIN msdb.dbo.sysjobstepslogs l
        ON s.step_uid = l.step_uid

    The RIGHT(l.log, 50000) is just to limit the output, since if you use "Append output to existing entry..." this row can grow to two gigabytes.


    RLF
    • Edited by SQLWorkMVP Friday, December 25, 2009 2:59 PM Corrected last line
    Friday, December 25, 2009 2:41 PM
  • Hi Russel,
    I have done that part i.e select Advanced option and log to table and log to a file also. But the error is the same
    like tail of the file is:
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
    The statement has been terminated. [SQLSTATE 01000]

    Hi Xiao-Min,
    There is nothing of importance in errorlog.

    ----------
    But the thing is, I can run the rebuild index proc as a query but it is only giving this error from sql agent.

    Thanks all for contributing your valuable time.
    Sunday, December 27, 2009 10:06 AM
  • Hi Kalman,

    I am not using print. I am writing below the code that is used as the TSQL PROC.

    CREATE procedure [dbo].[Usp_osdeDBReIndex_2k5]
    as
    DECLARE @TableName varchar(255)

    DECLARE TableCursor CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_type = 'base table'

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DBCC DBREINDEX(@TableName,' ',90)
    FETCH NEXT FROM TableCursor INTO @TableName
    END

    CLOSE TableCursor

    DEALLOCATE TableCursor
    Monday, December 28, 2009 2:03 AM
  • I was able to recreate it in SQL Server 2008 using your modified sproc.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator. 
    [SQLSTATE 01000] (Message 2528) 
    As some of the links above stated, it can just be ignored.

    You can also open a bug report at Connect: https://connect.microsoft.com/SQLServer?wa=wsignin1.0


    Sproc:
    CREATE procedure uspDBreindex
    as 
    DECLARE @TableSchema varchar(127), @TableName varchar(255)
    
    DECLARE TableCursor CURSOR FOR
    SELECT table_schema,table_name FROM information_schema.tables
    WHERE table_type = 'base table'
    
    OPEN TableCursor
    
    FETCH NEXT FROM TableCursor INTO @TableSchema, @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	SET @TableName=@TableSchema+'.'+@TableName
    	DBCC DBREINDEX(@TableName,' ',90)
    	FETCH NEXT FROM TableCursor INTO  @TableSchema,@TableName
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor
    GO
    

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Friday, September 28, 2012 7:29 PM
    Monday, December 28, 2009 2:46 AM
  • Hi Kalman,
    I can ignore it but the job fails and sends a notification. Isn't there a way to avoid the failure of job?

    Thanks
    Monday, December 28, 2009 4:17 AM
  • 70 and then I get "The statement has been terminated". Previously I was able to run the proc fine as a query at-least but now I am getting after 70 or so [SQLSTATE 01000] msg  This error message "Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded."

    I found one KB article http://support.microsoft.com/kb/938102. It kind of qualifies. I am indeed on SP2.
    But the errorlog doesn't have anything

    2009-12-27 21:25:45.90 Server      The time stamp counter of CPU on scheduler id 12 is not synchronized with other CPUs.
    2009-12-27 21:33:46.00 Server      The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
    2009-12-27 21:57:46.03 Server      The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
    2009-12-27 22:05:46.04 Server      The time stamp counter of CPU on scheduler id 14 is not synchronized with other CPUs.
    2009-12-27 22:33:46.05 Server      The time stamp counter of CPU on scheduler id 11 is not synchronized with other CPUs.
    2009-12-27 22:41:46.05 Server      The time stamp counter of CPU on scheduler id 12 is not synchronized with other CPUs.
    2009-12-27 22:57:46.06 Server      The time stamp counter of CPU on scheduler id 4 is not synchronized with other CPUs.
    2009-12-27 23:09:46.06 Server      The time stamp counter of CPU on scheduler id 14 is not synchronized with other CPUs.
    2009-12-27 23:13:46.06 Server      The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
    2009-12-27 23:29:46.07 Server      The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
    2009-12-28 00:00:08.94 spid20s     This instance of SQL Server has been using a process ID of 2824 since 12/10/2009 9:29:36 PM (local) 12/11/2009 2:29:36 AM (UTC). This is an informational message only; no user action is required.
    2009-12-28 00:05:46.08 Server      The time stamp counter of CPU on scheduler id 4 is not synchronized with other CPUs.
    2009-12-28 00:09:46.08 Server      The time stamp counter of CPU on scheduler id 12 is not synchronized with other CPUs.
    2009-12-28 00:21:46.09 Server      The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
    2009-12-28 00:49:46.10 Server      The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
    2009-12-28 01:13:46.11 Server      The time stamp counter of CPU on scheduler id 4 is not synchronized with other CPUs.
    2009-12-28 01:17:46.11 Server      The time stamp counter of CPU on scheduler id 14 is not synchronized with other CPUs.
    2009-12-28 01:29:46.11 Server      The time stamp counter of CPU on scheduler id 12 is not synchronized with other CPUs.
    2009-12-28 01:41:46.12 Server      The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
    2009-12-28 01:53:46.12 Server      The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
    2009-12-28 02:41:46.14 Server      The time stamp counter of CPU on scheduler id 4 is not synchronized with other CPUs.

    So I am not sure if this is the bug I am hitting for this rebuild index job. How to find out?


    Monday, December 28, 2009 7:43 AM
  • I get the below error.
    -------------------------------------
    The statement has been terminated.
    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.
    Monday, December 28, 2009 9:17 AM
    • Marked as answer by HumayunM Monday, December 28, 2009 9:49 AM
    • Edited by Kalman Toth Friday, September 28, 2012 7:28 PM
    Monday, December 28, 2009 9:24 AM
  • Thanks Kalman. I will work on that.
    Monday, December 28, 2009 9:49 AM