• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 62
  • Last Modified:

SP Time Out Every Monday At 12:20AM

What could be causing this SP to time out every Monday at 12:20AM when called from the application? It used to be sporadic, but recently becames regular, on Mondays at 12:20AM.  I've checked windows events log to see if it capture unusual activity at that time, nothing was reported. Also, I checked SQL Server errorlog, same nothing unusual was reported.

Please what can you recommend?  The error is posted below too

========================================================================
                                                                       Stored Procedure


@TransactionID varchar(20),
@Confirm varchar(50),
@PaymentMethod varchar(10)
 
AS

BEGIN TRY

      DECLARE @CntRec INT

      BEGIN      
             SET @CntRec =(select ISNULL(count(DSTransactionID),0) as RowC from DSServices
                                    where (ISNumeric(ConfirmationNbr) <> 1) AND
                                            (SegmentTypeCD = 'S') AND
                                            (ServicePerformYN = 'Y') AND
                                            (DSTransactionID = @TransactionID))

            IF      (@CntRec <> 0)
                  BEGIN
                        UPDATE DSServices
                              SET ConfirmationNbr = @Confirm ,
                              PaymentMethod = @PaymentMethod ,
                              ConfirmationDT = getdate()
                        WHERE
                              (ISNumeric(ConfirmationNbr) <> 1) AND
                              (SegmentTypeCD = 'S') AND
                              (ServicePerformYN = 'Y') AND
                              (DSTransactionID = @TransactionID)
                        --Return 'updated' Flag indicating successfull update
                        Select 'updated'AS ReturnValue
                  END
            ELSE
                  BEGIN
                        --Return 'norecord' Flag indicating successfull update
                        Select 'norecord'AS ReturnValue
                  END
      END      
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ReturnValue;
END CATCH;


GO


=======================================================================
Error Message

Processor.aspx - GoToCheckOut
Transaction ID : 32971084799
Please investigate due to possible issues with transaction processing
System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at OnlineServicesWebApp.clsDatabaseConn.ExecScalar(String spName, SqlParameter[] arParams) in C:\WWW\VisualStudio 2010\OnlineServices-05272015-For-Hire-Dev\OnlineServicesWebApp\Common Classes\clsDatabaseConn.vb:line 200 at OnlineServicesWebApp.Payment_Processor.GoTocheckOut(String transid, String cardtype) in C:\WWW\VisualStudio 2010\OnlineServices-05272015-For-Hire-Dev\OnlineServicesWebApp\Payment\Processor.aspx.vb:line 572 ClientConnectionId:150ef64d-22f1-4358-81ed-bd3a25003129 Error Number:-2,State:0,Class:11
0
faith victory
Asked:
faith victory
6 Solutions
 
Mark WillsTopic AdvisorCommented:
Well, it indicates to me that it possibly isnt the server side, unless background jobs are running, so it is more likely your timeout setting : https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

If systems are getting a heavier workload over time, then you will see a corresponding increase in potential time outs.

Things like indexes (et al) can mean that the SP is no longer using an optimal plan. Might want to 'recompile' your SP

Do you run this at other times without issue ?

Also, wrap in a discrete transaction.

Is there any concurrency in the updates ?

Is there any background procedures that might stall access ?

Have you set any timeouts in the connection string ?

Always best to create your own variables inside the SP and do any validity checking as you pass the params to your internal variables - cuts out parameter sniffing and any overhead of checking within the query / update.

Cant think of anything else off the top of my head....

Change SP to use internal variables for the params being passed.
Wrap in a begin transaction block
use 'WITH RECOMPILE' when changing your proc
check backend jobs
Change timeout settings
Double check connection settings....
0
 
faith victoryAuthor Commented:
Yes, it runs daily, same time without any time out issue but on Monday midnight is when we get the time out
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
What's so special about Monday night? Do you have maintenance or backups that are running at that time?
I fully agree with Mark that you would expect to see this in high-workload situations or when  the database is locked out for some reason (e.g.  a backup is starting or some volume maintenance tasks are running causing the read/writes on the database to be frozen momentarily). Would recommend working with your IT/system admin to figure out the root cause.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
arnoldCommented:
Echo Nakul,
What runs over the weekend, and Monday that potentially impacts it...

Is it a publisher/subscriber setup?
0
 
Mark WillsTopic AdvisorCommented:
Well, as a glimpse into sysjobs, (scheduled procs) you can run :
use msdb
go
select j.name, j.enabled, s.next_run_Date, s.next_run_time
from sysjobs j, sysjobschedules s
where j.job_id= s.job_id

-- or 

exec dbo.sp_help_job

exec dbo.sp_help_jobhistory

Open in new window

Then check scheduled jobs or the jobhistory and see what happens on a monday night.

Read up on sp_help_job : https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-job-transact-sql
Or more involved : https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/
0
 
Scott PletcherSenior DBACommented:
You can simplify the existing code; you don't need a SELECT first.  That might reduce activity to prevent the delay/wait that is occurring on Mondays.

UPDATE DSServices
             SET ConfirmationNbr = @Confirm ,
                 PaymentMethod = @PaymentMethod,
                 ConfirmationDT = getdate()
             WHERE
                 (ISNumeric(ConfirmationNbr) <> 1) AND
                 (SegmentTypeCD = 'S') AND
                 (ServicePerformYN = 'Y') AND
                 (DSTransactionID = @TransactionID)

SET @CntRec = @@ROWCOUNT

SELECT CASE WHEN @CntRec > 0 THEN 'update' ELSE 'norecord' END AS ReturnValue
0
 
faith victoryAuthor Commented:
All solutions provided addressed the issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now