locked
SQL Server 2005 - SQLagent Log message RRS feed

  • Question

  • We are seeing the following in our SQLAgent log every minute.  I cannot find any information anywhere about this error message.

    [298] SQLServer Error: 599, WRITE: The length of the result exceeds the length limit (2GB) of the target large type. [SQLSTATE 42000] (LogToTableWrite)

    Thursday, November 9, 2006 12:02 AM

Answers

  • There are several jobs that run every x minutes on the server.  To eliminate the errors showing up in SQLAgent Error Log, the jobs had to be modified to NOT Log to Table, NOT append to existing log, and NOT include job step output.  So, now there is no job history being maintained on the offending jobs.  This could prove to be an issue if required to audit all runs of a job for SOX compliance.  The only SOX option left is to append to an existing file, which can become quite large in very little time.

    According to the last update of BOL for SQL2005 (SP1), it states that Log to Table, Append to Existing Log, and Include Job Step Output should truncate the length of the entries to fit into the history table.  Based on all testing, it appears that this does not happen for a varchar(max) field in one of the log tables, thus, the erroneous error in the SQLAgent Error Log.  Also note that the Error Log doesn't tell you what is causing the error, nor which sys. table is being affected.  Only way to find the offending job(s) is to run a trace on msdb.

    System Info:
    Microsoft SQL Server 2005 - 9.00.2153.00 (X64)   May  9 2006 13:58:37  
    Copyright (c) 1988-2005 Microsoft Corporation 
    Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

    Monday, November 13, 2006 8:26 PM

All replies

  • DO you have a job that runs every minute ?

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Monday, November 13, 2006 6:56 PM
  • There are several jobs that run every x minutes on the server.  To eliminate the errors showing up in SQLAgent Error Log, the jobs had to be modified to NOT Log to Table, NOT append to existing log, and NOT include job step output.  So, now there is no job history being maintained on the offending jobs.  This could prove to be an issue if required to audit all runs of a job for SOX compliance.  The only SOX option left is to append to an existing file, which can become quite large in very little time.

    According to the last update of BOL for SQL2005 (SP1), it states that Log to Table, Append to Existing Log, and Include Job Step Output should truncate the length of the entries to fit into the history table.  Based on all testing, it appears that this does not happen for a varchar(max) field in one of the log tables, thus, the erroneous error in the SQLAgent Error Log.  Also note that the Error Log doesn't tell you what is causing the error, nor which sys. table is being affected.  Only way to find the offending job(s) is to run a trace on msdb.

    System Info:
    Microsoft SQL Server 2005 - 9.00.2153.00 (X64)   May  9 2006 13:58:37  
    Copyright (c) 1988-2005 Microsoft Corporation 
    Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

    Monday, November 13, 2006 8:26 PM