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
faith victoryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.