Timeout errors

[pb648174]

After executing osql from the command line via the Windows scheduled
task interface, the following error is returned: "Timeout expired"

The code in the sql is as follows:
BACKUP Database DBName to DISK='D:\Files\Backups\DBName.bak' WITH INIT
go
Is the timeout happening during the backup or in just connecting to the

server? There is no firewall between the two machines they are on the
same hub, and the machine executing osql has SQL 2000 installed while
the database machine has SQL 2005.
The command line is:
print time
osql -b -n -E -S ServerName -i Backup.sql
print time

and the output is:

8:00a
Timeout expired
8:00a

Any ideas what the issue is? It happens intermittently, and works 98%
of the time. Typically when this happens, during the next backup it
will be fixed.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

After executing osql from the command line via the Windows scheduled
task interface, the following error is returned: "Timeout expired"

The code in the sql is as follows:
BACKUP Database DBName to DISK='D:\Files\Backups\DBName.bak' WITH INIT
go
And D is a local disk to the SQL Server machine?
Is the timeout happening during the backup or in just connecting to the
server? There is no firewall between the two machines they are on the
same hub, and the machine executing osql has SQL 2000 installed while
the database machine has SQL 2005.
The command line is:
print time
osql -b -n -E -S ServerName -i Backup.sql
print time

and the output is:

8:00a
Timeout expired
8:00a

Judging from the message, this is a command timeout, but that does not
make sense, as OSQL by default does not have a command timeout. But
maybe there some intermittent error which causes OSQL to set the
default timeout to 0? (The default timeout for ODBC as such is 30
seconds.)

It would have been interesting to see the seconds for the time as well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[John Bell]

Hi

What is print time doing? You may want to change it to echo %time%

John

"pb648174" <go****@webpaul.net> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...

After executing osql from the command line via the Windows scheduled
task interface, the following error is returned: "Timeout expired"

The code in the sql is as follows:
BACKUP Database DBName to DISK='D:\Files\Backups\DBName.bak' WITH INIT
go
Is the timeout happening during the backup or in just connecting to the

server? There is no firewall between the two machines they are on the
same hub, and the machine executing osql has SQL 2000 installed while
the database machine has SQL 2005.
The command line is:
print time
osql -b -n -E -S ServerName -i Backup.sql
print time

and the output is:

8:00a
Timeout expired
8:00a

Any ideas what the issue is? It happens intermittently, and works 98%
of the time. Typically when this happens, during the next backup it
will be fixed.

[pb648174]

Sorry, it isn't print time but time /t

John Bell wrote:

Hi

What is print time doing? You may want to change it to echo %time%

John

"pb648174" <go****@webpaul.net> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...

After executing osql from the command line via the Windows scheduled
task interface, the following error is returned: "Timeout expired"

The code in the sql is as follows:
BACKUP Database DBName to DISK='D:\Files\Backups\DBName.bak' WITH INIT
go
Is the timeout happening during the backup or in just connecting to the

server? There is no firewall between the two machines they are on the
same hub, and the machine executing osql has SQL 2000 installed while
the database machine has SQL 2005.
The command line is:
print time
osql -b -n -E -S ServerName -i Backup.sql
print time

and the output is:

8:00a
Timeout expired
8:00a

Any ideas what the issue is? It happens intermittently, and works 98%
of the time. Typically when this happens, during the next backup it
will be fixed.

[pb648174]

Yes, D is a local drive to the SQL Server. I have also replaced time /t
with echo %time% as suggested above which prints seconds as well. I'll
post that the next time it fails.

[pb648174]

I added seconds and got the same "Timeout expired" error for 3 backups
in a row, with the backups happening every two hours. The issue went
away by the time the 4th backup (8 hours later) came up. The output is
below with time.

10:00:00.43
Timeout expired
10:00:16.35

The interesting thing is that there are two production SQL 2005 servers
and one backup SQL 2005 server. Here is how they were affected during
the three failed backups, two hours between each, with no loss of
connectivity for users using the application. The duration between
start and end for the failure is pretty consistently about 16 seconds
as in the above error message.

production1 production2 backup
10am down up down
12pm down down down
02pm down up up

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

I added seconds and got the same "Timeout expired" error for 3 backups
in a row, with the backups happening every two hours. The issue went
away by the time the 4th backup (8 hours later) came up. The output is
below with time.

10:00:00.43
Timeout expired
10:00:16.35
15 seconds is the default connection timeout. So it does look like a
connection problem after all. What I don't understand is the message.
But maybe there is some variation of connection problems where ODBC
do find the server, but is not let in?
The interesting thing is that there are two production SQL 2005 servers
and one backup SQL 2005 server. Here is how they were affected during
the three failed backups, two hours between each, with no loss of
connectivity for users using the application. The duration between
start and end for the failure is pretty consistently about 16 seconds
as in the above error message.

production1 production2 backup
10am down up down
12pm down down down
02pm down up up

So which of the servers did the script try to connect to?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

All three each time - it backs up prod1, then prod 2 and restores it to
backup. So it is alternatively taking errors on all three machines
while connecting, but this only happens once or twice per month.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

All three each time - it backs up prod1, then prod 2 and restores it to
backup. So it is alternatively taking errors on all three machines
while connecting, but this only happens once or twice per month.

That's impossible! I mean in the original post of this thread, you said

The code in the sql is as follows:

BACKUP Database DBName to DISK='D:\Files\Backups\DBName.bak'
WITH INIT

That's one backup statement, not two, and not a RESTORE statement in sight.

What about telling the full story of what's in the script?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

My first statement is accurate, the backup command is being run once on
one machine and then again on another machine (separate osql calls).
The script that is being run on the third machine (backup) is a restore
not a backup, but it takes the same error so i don't think it is even
running the script, but taking the timeout during the connection. At
any rate, if you want to focus on just the backup part for the two
production servers to simplify it, that is fine. I just included that
info in case it helped to understand it.

[pb648174]

We are starting to get these errors more often now, like 2-3 times per
week, so if anyone has any suggestions for further debugging or can
direct me to the best area to look for issues I would appreicate it.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

We are starting to get these errors more often now, like 2-3 times per
week, so if anyone has any suggestions for further debugging or can
direct me to the best area to look for issues I would appreicate it.

I guess you have to be around when it happens, so you can test
interactively.

But if we assume that is a connection problem, I guess there is a network
problem of some sort.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

My theory all along is that the network connectivity between the
machine making the connection and the database server is somehow
intermittently going up and down. If that is the case, what would be
the best way to confirm? Is the amount of time (15 seconds) consistent
with this?

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

My theory all along is that the network connectivity between the
machine making the connection and the database server is somehow
intermittently going up and down. If that is the case, what would be
the best way to confirm? Is the amount of time (15 seconds) consistent
with this?

15 is the default connection timeout. Normally a connection timeout
results in a different message. But maybe this message could appear if
the connection initially beings to start up, but then fails to complete
for some reason. But that is just pure speculation on my part.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

I used the same script and specified a bogus machine name and got
"Specified SQL Server not found", so it isn't a network issue I
guess... I'm at a loss as to what to do from here.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

I used the same script and specified a bogus machine name and got
"Specified SQL Server not found", so it isn't a network issue I
guess... I'm at a loss as to what to do from here.

Yes, that's the message I would expect if the server is entierly
unavailable. But what if some packets make it, and others not?

I'm about as lost as you are, but what about running ping -n 200 in
conjunction with running the backup job, and then check for packet
loss?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

Ok, I'll try that and report back in a week or two most likely - thanks.

[das]

Just my 2 cents, I faced a similar problem sometime ago:

I would try to backup my SQL Server DB (not from a script, but
manually) and I would get timeout's after sometime. I found out that
this happens when you are running the backup remotely to write to a
drive on the SQL Server (basically a n/w issue)

So I started logging into the actual m/c and then backing up there was
not a problem anymore. Also my DB was huge by then about 10GB.

Hope that helps.

[Erland Sommarskog]

An interesting observation. I have installed some Internet Firewall
Client on my box at work, as per request from my sysadmin.

When I connect from home, I now have troubles of accessing my machine,
and relevant to this thread is that some attempts to connect with
OSQL gave the message "Timeout expired". I am able to connect to the
machine with Remote Desktop and by file system. When I added the
option -l 30, to get a longer connection timeout, the only change
was that it took longer time for "Timeout expired" to appear.

I'm not saying that you have exactly the same problem as I have, but
at least it is an indication that my theory that the connection is
initiated, but then fails somewhere.

I had some more ideas that I should try, but alas now I only get the
message "SQL Server does not exist or access denied".

--
Erland Sommarskog, Stockholm, es****@sommarskog.se

[pb648174]

We have multiple database servers and want to keep it centralized so I
don't think we will do the back job from the SQL Server.. Also, that
doesn't seem to make sense because all osql or enterprise manager does
is issue a command to the SQL Server.. It isn't actually making a
backup from the connecting client and transferring the file. I guess
for now we will just live with the intermittent backups problems. I
have the ping command in there so I'll know once it fails again if the
entire network connection is down.

Erlans, I take it you have unblocked ALL traffic to 1433 for your
firewall? It might consider your home connection as a different type of
connection. Also, I have had problems with Authenticated logins through
firewalls and use only SQL Authentication when just 1433 is open.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

We have multiple database servers and want to keep it centralized so I
don't think we will do the back job from the SQL Server.. Also, that
doesn't seem to make sense because all osql or enterprise manager does
is issue a command to the SQL Server.. It isn't actually making a
backup from the connecting client and transferring the file. I guess
for now we will just live with the intermittent backups problems. I
have the ping command in there so I'll know once it fails again if the
entire network connection is down.
The error "timeout expired" has nothing to do with the backup command.
You could just as well have a "PRINT 'Howdy!'" in that input file. You
would get the same error. OSQL fails to connect to SQL Server for some
reason that I don't know.
Erlans, I take it you have unblocked ALL traffic to 1433 for your
firewall? It might consider your home connection as a different type of
connection. Also, I have had problems with Authenticated logins through
firewalls and use only SQL Authentication when just 1433 is open.

It used to work, but then I installed this Internet Firewall Client. There
is some ISA Server in the mix too. I have not investigated where the
problem might be, and I think I will leave to my sysadmin to find out. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

As per previous posts in this thread, I have added the ping statement
to the server in question to the backup script.. Here we are a few
weeks later with the same intermittent
problem, ping works fine.

Pinging dbserver [192.168.23.154] with 32 bytes of data:

Reply from 192.168.23.154: bytes=32 time<10ms TTL=128

Reply from 192.168.23.154: bytes=32 time<10ms TTL=128

Reply from 192.168.23.154: bytes=32 time<10ms TTL=128

Reply from 192.168.23.154: bytes=32 time<10ms TTL=128

Ping statistics for 192.168.23.154:

Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

Minimum = 0ms, Maximum = 0ms, Average = 0ms

8:00:19.85
Timeout expired
8:00:37.23

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

As per previous posts in this thread, I have added the ping statement
to the server in question to the backup script.. Here we are a few
weeks later with the same intermittent
problem, ping works fine.

Pinging dbserver [192.168.23.154] with 32 bytes of data:

Reply from 192.168.23.154: bytes=32 time<10ms TTL=128

Reply from 192.168.23.154: bytes=32 time<10ms TTL=128

Reply from 192.168.23.154: bytes=32 time<10ms TTL=128

Reply from 192.168.23.154: bytes=32 time<10ms TTL=128

Ping statistics for 192.168.23.154:

Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

Minimum = 0ms, Maximum = 0ms, Average = 0ms

8:00:19.85
Timeout expired
8:00:37.23

When I was able to get "Timeout Expired" to my server at work, I was
still able to ping my machine.

As I've said before, I suspect that the issue is related to Windows
Networking, Active Directory or something else I have very little
knowledge of.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx