September 18, 2015 at 6:45 am
Hi All,
From C# code we are calling the below storedprocedure and it fails for Command timeout and its sets 30 secs in C# config. and this issue is intermittently happens in Production. Can anybody suggest the solution please.
CREATE PROCEDURE [dbo].[EDML_LogMilestone2]
(
@DeliveryTrackingId uniqueidentifier
,@FromNode varchar(15)
,@ToNode varchar(15)
,@DeliveryState varchar(10)
,@MilestoneName varchar(100)
,@MilestoneUTCTime datetime
,@DeliveryStartUTCTime datetime = NULL
,@Consumer nvarchar(50) = NULL
,@UserIdentity nvarchar(50) = NULL
,@AppServerName varchar(50) = NULL
,@SubsidiaryName nvarchar(50) = NULL
,@EntityName varchar(50) = NULL
,@Action varchar(50) = NULL
,@HubRowId int = NULL
,@ClientDBRowId varchar(50) = NULL
,@ClassName varchar(50) = NULL
,@MethodName varchar(50) = NULL
,@SplitCount int = NULL
,@ParentDeliveryTrackingId uniqueidentifier = NULL
,@MessageBody nvarchar(max) = NULL
,@ByteArrayData varbinary(max) = NULL
)
AS
DECLARE @sp-2 varchar(100)
,@dbname nvarchar(128)
,@servername nvarchar(100)
,@ByteArrayDataString varchar(max)
,@ErrorSeverity int
,@ErrorState int
,@ErrorMessage nvarchar(MAX)
,@Error nvarchar(1000)
,@ErrorLine int
,@ERRORNUMBER int
SELECT @sp-2 = 'EDML_LogMilestone2'
,@dbname = DB_NAME()
,@servername = @@servername
IF @DeliveryTrackingId IS NULL RETURN;
IF @ByteArrayData IS NOT NULL
BEGIN
SET @ByteArrayDataString = CONVERT(varchar(max), @ByteArrayData, 2)
END
IF @ParentDeliveryTrackingId IS NOT NULL AND @ParentDeliveryTrackingId = @DeliveryTrackingId
BEGIN
SET @ParentDeliveryTrackingId = NULL
END
BEGIN TRY
IF (XACT_STATE()) != -1
BEGIN
INSERT INTO [EDMLoggingService].[dbo].[EDMMilestones]
([DeliveryTrackingId]
,[FromNode]
,[ToNode]
,[DeliveryState]
,[MilestoneName]
,[MilestoneUTCTime]
,[DeliveryStartUTCTime]
,[DBServerName]
,[DatabaseName]
,[IniQueueUTCTime]
,[Consumer]
,[UserIdentity]
,[AppServerName]
,[SubsidiaryName]
,[EntityName]
,[Action]
,[HubRowId]
,[ClientDBRowId]
,[ClassName]
,[MethodName]
,[SplitCount]
,[ParentDeliveryTrackingId]
,[Data]
,[ByteArrayData])
VALUES
(CAST(@DeliveryTrackingId as varchar(255))
,ISNULL(@FromNode,'NA')
,ISNULL(@ToNode,'NA')
,@DeliveryState
,@MilestoneName
,@MilestoneUTCTime
,@DeliveryStartUTCTime
,@servername
,@dbname
,GETUTCDATE()
,@Consumer
,@UserIdentity
,@AppServerName
,@SubsidiaryName
,@EntityName
,@Action
,@HubRowId
,@ClientDBRowId
,@ClassName
,@MethodName
,@SplitCount
,CAST(@ParentDeliveryTrackingId as varchar(255))
,@MessageBody
,ISNULL(@ByteArrayDataString,''))
END
ELSE
BEGIN
SET @ErrorMessage = 'The transaction in which the EDML_LogMilestone2 was called is in an uncommitable state. @DeliveryTrackingId='+CAST(@DeliveryTrackingId as varchar(255))+ --Fixed a part of arthimetic overflow issue
', @FromNode='+ISNULL(@FromNode,'NA')+', @ToNode='+ISNULL(@ToNode,'NA')+', @DeliveryState='+ @DeliveryState + ', @MilestoneName='+ @MilestoneName + ', @MilestoneUTCTime='+
CONVERT(nvarchar,@MilestoneUTCTime,21)+', @DeliveryStartUTCTime='+CONVERT(nvarchar,@DeliveryStartUTCTime,21)+', @Consumer='+ISNULL(@Consumer,'') +
', @UserIdentity='+ ISNULL(@UserIdentity,'') + ', @AppServerName=' + ISNULL(@AppServerName,'')+', @EntityName=' +ISNULL(@EntityName,'')+', @Action='+ISNULL(@Action,'')+
', @HubRowId='+CAST(ISNULL(@HubRowId,-1) AS nvarchar)+ ', @ClientDBRowId='+ ISNULL(@ClientDBRowId,'')
EXECUTE xp_logevent 61000, @ErrorMessage, ERROR
END
END TRY
BEGIN CATCH
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
SET @Error = ERROR_MESSAGE()
SET @ErrorLine = ERROR_LINE()
SET @ERRORNUMBER = ERROR_NUMBER()
SET @ErrorMessage = 'EDML_LogMilestone2 encountered an error. @DeliveryTrackingId='+CAST(@DeliveryTrackingId as varchar(255))+
', @FromNode='+ISNULL(@FromNode,'NA')+', @ToNode='+ISNULL(@ToNode,'NA')+', @DeliveryState='+ @DeliveryState + ', @MilestoneName='+ @MilestoneName +
', @MilestoneUTCTime='+CONVERT(nvarchar,@MilestoneUTCTime,21)+', @DeliveryStartUTCTime='+CONVERT(nvarchar,@DeliveryStartUTCTime,21)+', @Consumer='+ISNULL(@Consumer,'') +
', @UserIdentity='+ ISNULL(@UserIdentity,'') + ', @AppServerName=' + ISNULL(@AppServerName,'')+', @EntityName=' +ISNULL(@EntityName,'')+', @Action='+ISNULL(@Action,'')+
', @HubRowId='+CAST(ISNULL(@HubRowId,-1) AS nvarchar)+ ', @ClientDBRowId='+ ISNULL(@ClientDBRowId,'')+', @ERRORNUMBER='+CAST(ISNULL(@ERRORNUMBER,0) as nvarchar)+
', @ErrorSeverity='+CAST(ISNULL(@ErrorSeverity,0) as nvarchar)+', @ErrorState='+CAST(ISNULL(@ErrorState,0) as nvarchar)+', @ErrorLine='+CAST(ISNULL(@ErrorLine,0) as nvarchar)+
', @Error='+ISNULL(@Error,'')
EXECUTE xp_logevent 61000, @ErrorMessage, ERROR
END CATCH
GO
September 18, 2015 at 9:11 am
Increase your timeout? It's a property of the connection so it needs to be done in C#.
Although I can't see why that code should take > 30 seconds to run.
September 18, 2015 at 12:35 pm
Based on your code it looks like this procedure is part of a series of actions that are all part of the same transaction. This could be part of your issue as locks need to be held for the length of the transaction so if you have this process being called by multiple connections or the tables being accessed by other processes you are likely just seeing blocking issues. So that's where I'd start.
You also have some MAX (BLOB) columns that can affect behavior so I'd also investigate if my timeouts are occurring when I have a BLOB that has to be stored off-row.
So you really want to be looking at your wait stats, even down to the specific process to see what it is waiting for.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply