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]

Re: Timeout errors

pb648174 (google@webpaul .net) writes:[color=blue]
> 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[/color]

And D is a local disk to the SQL Server machine?
[color=blue]
> 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[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[John Bell]

Re: Timeout errors

Hi

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

John

"pb648174" <google@webpaul .net> wrote in message
news:1145380471 .168459.108470@ v46g2000cwv.goo glegroups.com.. .[color=blue]
> 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.
>[/color]

[pb648174]

Re: Timeout errors

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

John Bell wrote:[color=blue]
> Hi
>
> What is print time doing? You may want to change it to echo %time%
>
> John
>
> "pb648174" <google@webpaul .net> wrote in message
> news:1145380471 .168459.108470@ v46g2000cwv.goo glegroups.com.. .[color=green]
> > 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.
> >[/color][/color]

[pb648174]

Re: Timeout errors

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]

Re: Timeout errors

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]

Re: Timeout errors

pb648174 (google@webpaul .net) writes:[color=blue]
> 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[/color]

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?
[color=blue]
> 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[/color]

So which of the servers did the script try to connect to?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[pb648174]

Re: Timeout errors

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]

Re: Timeout errors

pb648174 (google@webpaul .net) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[pb648174]

Re: Timeout errors

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]

Re: Timeout errors

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]

Re: Timeout errors

pb648174 (google@webpaul .net) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[pb648174]

Re: Timeout errors

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]

Re: Timeout errors

pb648174 (google@webpaul .net) writes:[color=blue]
> 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?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[pb648174]

Re: Timeout errors

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.