April 28, 2014 at 12:16 pm
I'm working on a performance tuning on a 3TB table and tried to rebuild an non clustered index and I got exceptions(see below) during my rebuild index. Because there is a sproc which is doing MERGE on the table and throwing exception during the index rebuild. I did an workaround that removing the MERGE and replace INSERT and UPDATE instead and the rebuild worked fine after. So is this expected behavior on SQL Server 2008 R2 CU6? Any one seen this issue?
System.Data.SqlClient.SqlException (0x80131904): SP: SprocwithMERGEStmnt, Line: 118. Unexpected error: 681-"ON DBO.InvolvedTbleName MERGE : Attempting to set a non-NULL-able column's value to NULL."
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Thanks
Jay
http://www.sqldbops.com
April 28, 2014 at 12:22 pm
It looks like the error is in the MERGE statement, not an ALTER INDEX...REBUILD.
Maybe you could post the DDL of the tables involved and the entire MERGE statement that throws the error
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply