Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Timing Issue Expand / Collapse
Author
Message
Posted Friday, December 06, 2013 12:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 5:37 AM
Points: 55, Visits: 119
I am running SQL Server 2012 on an i7 Windows 7-64 PC.
Lately I have noticed that when I execute a stored proc, som parts of it do no trun.
These stored proc have not been modified. The behaviour is not consistent and not in the same code.
Some times a table that needs to be deleted and recreated does not get deleted.
Or an update query would not update.

I am guessing that this could be due to timing issues - a query has not finished executing, when the next one kicks in - given the inconsistent nature of the the problem.

Has anyone noticed this? This has begun occurring in the last two months or so. I never saw it before that.

What else could be the reason? It happens in different stored proc in different places. These have been running without problems for a while.

Thank,
Tina
Post #1520718
Posted Friday, December 06, 2013 3:04 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 2,729, Visits: 2,649
Try to make a log of the executions/SPs in order to locate your issue. It seems the executions order and theirs finishing times are important in your case.
Post #1520759
Posted Friday, December 06, 2013 3:13 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 2,774, Visits: 5,949
Are you using NOLOCK hints? This could be one cause.
It would help a lot to see what the SP does.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520766
Posted Friday, December 06, 2013 3:41 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 2,729, Visits: 2,649
Luis Cazares (12/6/2013)
Are you using NOLOCK hints? This could be one cause.
It would help a lot to see what the SP does.


Try to avoid using hints, unless when you're very sure.
My thought was if you can log the Start and End times of the SPs/Queries executions? and then see whether what you're guessing is true.
Post #1520771
Posted Saturday, December 07, 2013 6:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 5:37 AM
Points: 55, Visits: 119
Thanks SSCrazy, good idea. I will do that.
Post #1520811
Posted Saturday, December 07, 2013 6:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 5:37 AM
Points: 55, Visits: 119
I am not using NOLOCKS.
Post #1520812
Posted Monday, December 09, 2013 12:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:06 AM
Points: 482, Visits: 841
is there any TRY CATCH Clause you are using inside that store procedure ?
Post #1520928
Posted Monday, December 09, 2013 3:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 2,729, Visits: 2,649
Yes there is TRY .. CATCH in T-SQL - http://technet.microsoft.com/en-us/library/ms175976.aspx

Regards,
IgorMi
Post #1520975
Posted Monday, December 09, 2013 3:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:06 AM
Points: 482, Visits: 841
IgorMi (12/9/2013)
Yes there is TRY .. CATCH in T-SQL - http://technet.microsoft.com/en-us/library/ms175976.aspx

Regards,
IgorMi


ahaan nice, thats why your procedure is in the silent mode

try to execute your procedure without TRY CATCH to check on which particular statement, exception is being generated.
and Rollback transaction in your catch clause to avoid the incomplete execution of batch.
Post #1520978
Posted Monday, December 09, 2013 3:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
twin.devil (12/9/2013)
IgorMi (12/9/2013)
Yes there is TRY .. CATCH in T-SQL - http://technet.microsoft.com/en-us/library/ms175976.aspx

Regards,
IgorMi


ahaan nice, thats why your procedure is in the silent mode


Igor just said there is a try-catch construct available in SQL Server. The OP did not say whether or not there is try catch in their procedure.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1520989
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse