SQL 2005 64 Bit job failure

Posted on 2010-01-08
Medium Priority
Last Modified: 2012-05-08
Hello everyone and thank you for your help:
Background:  Have a SQL 2005 64bit with one instance with three databases (A,B,C).  This is connected to a MS cluster using Windows 2003R2 64 bit SP3.  The cluster was at SP and we were getting timeout errors running jobs from database A to B.  It was recommended to upgrade to SP3 which we did.  Now we get timeout errors from A to B and B to C when running jobs.  
Error meassage:
Date  1/8/2010 10:13:04 AM
Log  Job History (BPW_DAILY_00 - stream 00 hard delete extracts [00:01])

Step ID  3
Server  servername\PWp
Job Name  BPW_DAILY_00 - stream 00 hard delete extracts [00:01]
Step Name  Delete Records in Staging Files
Duration  00:12:05
Sql Severity  16
Sql Message ID  16389
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Executed as user: server01\APP-PWp. TCP Provider: The semaphore timeout period has expired. [SQLSTATE 08S01] (Error 121)  Communication link failure [SQLSTATE 08S01] (Error 121).  The step failed.

Question by:Lou Pereira
  • 3
  • 3
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26272972
>> Now we get timeout errors from A to B and B to C when running jobs.  
>> Step Name  Delete Records in Staging Files

Ok.. Kindly let me know what exactly( logic) is done in the job and how..
Also it fails while Deleting records ( Guessing as per your job step description).
If you have huge no. of records to delete, then it might create Transactional logs more and spanning for a long time requires more server resources thus causing these kind of issues..

Timeouts can be reduced by tuning the existing logic or approach and by placing appropriate indexing if not present in the existing one..

Some more inputs on the aspects mentioned should help me to guide you in the correct direction..

Author Comment

by:Lou Pereira
ID: 26285393
The jobs are copying records from one DB to another in the same instance.  The DB is very large I believe 8TB or >.    I also saw posts regarding implementing registry keys as the following:
"MaxFreeConnections"=dword: 00000064
"MinFreeConnections"=dword: 00000020
"MaxRawWorkItems"=dword: 00000200
"MaxWorkItems"=dword: 00002000

1. Set TcpMaxDataRetransmissions to 30 (decimal);
2. Set KeepAliveInterval to 25000 (decimal).

This does not seem to work!! Thank you for your help and try to be detailed as I'm not a DBA.
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26290601
Haven't tried out the above Registry key approached on my own and hence not able to comment on it..

Kindly post out the queries which causes timeouts so that we can try optimizing it or tuning it out along with Execution plans of them..

Hope you are aware of how to check execution plans in SSMS...
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

by:Lou Pereira
ID: 26299951
I'm confused with your statements.  The error I posted is all over the SQL community with various fixes.   Have you ever seem this error message before, try googling it, this will take you to the SQL msg boards.  I think I fixed with one of the fixes I got from the SQL msg board.  
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26303389
Ok.. If that is the case, then request you to post the link / solution which worked for your case and close this question accordingly..

Accepted Solution

Lou Pereira earned 0 total points
ID: 26485246
The issue was fixed with my earlier post fix.  Thank you

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question