Server Connection Timeout

Question

Server Connection Timeout

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

Hi,

So you mean to say that after 10 mins of job execution you are unable to retrieve the data as the connection is getting terminated. Did you check out in the job what the job is exactly doing. You will get an idea how it is getting connected to the server. Sometimes we can create a linked server and transfer the data and once the data transfer gets completed we can remove the linked server. I think this sort of thing might be happening. Or there might be connection time out set to default of 600. Inorder to checkout wht is causing the issue I need further information.

1. SQL Server versions on both servers and sp being used (to find sp installed execute below query

select serverproperty('ProductLevel')

2. Check out the job and you can find out how it is getting connected to the server (using ipaddress or name or using a linked server etc.. )

3. Check out the error logs and post the exact error.

If you dont have problem you can post the job code and the error log here. replace the server names using A and B for ur security.

[font="Verdana"]Thanks
Chandra Mohan[/font]

HBhagat Hall of Fame Points: 3341 More actions · Answer 2

Hi,

Both have Sp4

When the job is running its using a login what is made specially as an adminstrator rights for the jobs. I know what the job is doing. It is starting two other jobs in different servers and its updating a table in master database of both the servers.

I think when the job is executed the connection gets refreshed and the connection starts, but if i am not using the connection for 10 mins or more then the connection dies.

There were no error logs for the same.

Chand00 SSCertifiable Points: 6466 More actions · Answer 3

As I see from your replies that there is connection problem. You have two timeout values in sql server one of them is remote query timeout which is by default of 600 (i.e. ten minutes). Increase the timeout value to 1200 sec and check it out. You can set this with sp_configure and this does not require a server restart. Check and let me know

Thanks

Chandra mohan N

[font="Verdana"]Thanks
Chandra Mohan[/font]

HBhagat Hall of Fame Points: 3341 More actions · Answer 4

HBhagat

Hall of Fame

Points: 3341

May 26, 2009 at 6:14 am

#999842

did not helped.

The problem is still there...

Chand00 SSCertifiable Points: 6466 More actions · Answer 5

Hi,

Using the query analyzer are you able to connect from server a to server b without any problems after 10mins of the job completion.

Thanks

Chandra Mohan N

[font="Verdana"]Thanks
Chandra Mohan[/font]

HBhagat Hall of Fame Points: 3341 More actions · Answer 6

Hi,

I discovered one more thing...

When I am executing the query from A server through QA I am logged in as windows authentication but I have the rights as adminitrator. After the 10 mins if i try to execute the query with the windows authentication mode I am not able to access the B server.

But if I login with the sa login or any sql authentication login then i can access the B server and then using the windows authentication i can also access it later.

Any suggestion whats happening in SQL server logins ???

HBhagat Hall of Fame Points: 3341 More actions · Answer 7

Hi ,

you are understanding the incorrect part......

After 10 mins I am not able to access the B server via query.

If I access the within 10 mins i am able to access the server ..and if I access it after 10 mins then I am not

HBhagat Hall of Fame Points: 3341 More actions · Answer 8

Hi,

I know this is really old one, but at that time this was not solved. I had to create a job which used to run every 5 mins just to ensure the link is made.

I have now stopped the job. and have updated the query timeout in all the 3 servers. Now its fine.

Thanks,

H.