MS SQL Server ODBC - SQL 2005 Failing Backups Because it Exits The Query Prematurely

Author

SQL 2005 Failing Backups Because it Exits The Query Prematurely

ESPNSTI

2006-03-05, 8:24 pm

Hi,

PROBLEM:

We've encountered a problem in testing with our application being unable to
perform a backup successfully in SQL 2005.
This same application (which uses ODBC) is able to backup SQL 7.0 and SQL
2000 databases through the same user stored procedure without problems.

What appears to be the problem is that SQL 2005 returns control to the
application that called the stored procedure (usually a fraction of a
second) before the backup actually finishes.
If this happens, when the calling application then immediately closes the
query handle, SQL 2005 does not complete the backup and it'll produce the
following error:

Error: 3041, Severity: 16, State: 1. BACKUP failed to complete the command
BACKUP DATABASE Amazon_New. Check the backup application log for detailed
messages.

If however the application waits for a little bit before closing the query
handle, SQL 2005 completes the backup successfully.
The query is not being called asynchronously by the client app.

ENVIRONMENT:

I've produced this problem the easiest with the calling application running
on the same machine that hosts SQL 2005 (using 127.0.0.1 or (local) to
connect).
I've also reproduced this with the calling application residing on a
different machine than the SQL 2005 server machine (using a LAN connection).
However I believe I've seen it succeed in this (LAN) scenario, but I'm not
100% certain about this (I may have been stepping through the calling app
with a debugger at the time).

The SQL 2005 host machines that I've tried so far (two of them) have SQL
2005 Developer edition installed, and are Windows XP SP2 machines (MDAC 2.8
SP1).
The client machines I've tried are the SQL 2005 host machines and also a
Windows 2000 Pro SP4 machine with MDAC 2.8 SP1 installed.
I've tried connecting using both the SQL Native Client in ODBC and the SQL
Server ODBC driver (which the application normally uses).

STEPS TO REPRODUCE:

http://espnsti.in2hosting.org/BackupTest/BackupTest.zip
http://espnsti.in2hosting.org/Backu...pTestSource.zip
http://espnsti.in2hosting.org/Backu....usp_Backup.sql

I've created and attached a small test app (written in Delphi 5.0) that will
back up the Northwind DB (or any other DB).
To set up this app do the following:

1) If you do not have the Northwind DB, get a copy and restore it or
identify another DB to use.
2) Modify the attached dbo.usp_Backup.sql script to use the correct DB and
backup file path and name if needed.
Run dbo.usp_Backup.sql against Norhtwind (or the DB that will be used.)
3) Set up a new System DSN in the ODBC Data Source Admin control panel.
Name it NorthWind, and be sure to set it to change the default DB to
Northwind.
4) Extract the attached BackupTest.zip to some directory.

To use the app do the following:

A) To see it fail:

1) Enter the appropriate Data Source Name (and User ID and Password if
needed).
2) Leave the "Wait 5 seconds" check box unchecked, and click on Backup.
3) You should see an error in the sql error log (and the event viewer)
stating that the backup failed, and the backup file should not be there.

B) To check that sql returns from the query before the backup is complete:
(this only works accurately if the app and the SQL Server are on the
same machine)

1) Enter the appropriate Data Source Name (and User ID and Password if
needed).
2) Check the "Wait 5 seconds" check box, and click on Backup.
3) You should see the backup file appear and stay in the backup
directory.
4) In the event viewer application log, you should see three new
informational entries:

4.1) One from "BackupTest" that ends with "SQL Northwind Backup Control
Returned To App: " followed by a time.
4.2) Then a standard MSSQLSERVER message with details of the database
backup.
4.3) Finally there is a custom MSSQLSERVER message from the usp_Backup
stored procedure that contains "SQL Northwind Backup Finished:" followed by
a time.

If you compare the time in the message from the 1st and last
messages, you'll see that control was returned to the app before the backup
finished.
(Even though there probably is only a fraction of a second
difference.)

QUESTIONS:

1) Can anyone reproduce this behavior?
2) Has anyone run in to a similar problem (perhaps with something other than
a BACKUP sql statement)?
3) Am I correct in thinking that this is a bug in SQL 2005 or was this a
conscious change?
4) Is there perhaps a configuration setting (in ODBC / SQL / etc...) that
now needs to be used in SQL 2005?

Thanks,
Erik

Peter Yang [MSFT]

2006-03-05, 8:24 pm

Hello,

I tried to run the exe directly on my test machine, and it seems the odbc
query failed and it returned immediately with the time label. The same
thing happens to SQL 2000.

Since I don't have Delphi to test, I was not able to troubleshoot in the
source code level. Since you are not able to reproduce the issue
consistently, it might be a behavior change in SQL 2005. Please rest
assured I have reported this situation to the proper channel.

If you need further assistance on this issue, I recommend that you open a
Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
>From: "ESPNSTI" <ESPNSTI@nospam.nospam>
>Subject: SQL 2005 Failing Backups Because it Exits The Query Prematurely
>Date: Wed, 1 Mar 2006 12:33:21 -0600
>Lines: 122
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
>Message-ID: <O$6ui6VPGHA.3728@tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.odbc
>NNTP-Posting-Host: 12.10.40.130
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.odbc:44754
>X-Tomcat-NG: microsoft.public.sqlserver.odbc
>
>Hi,
>
>
>PROBLEM:
>
>
>We've encountered a problem in testing with our application being unable to
>perform a backup successfully in SQL 2005.
>This same application (which uses ODBC) is able to backup SQL 7.0 and SQL
>2000 databases through the same user stored procedure without problems.
>
>What appears to be the problem is that SQL 2005 returns control to the
>application that called the stored procedure (usually a fraction of a
>second) before the backup actually finishes.
>If this happens, when the calling application then immediately closes the
>query handle, SQL 2005 does not complete the backup and it'll produce the
>following error:
>
>Error: 3041, Severity: 16, State: 1. BACKUP failed to complete the command
>BACKUP DATABASE Amazon_New. Check the backup application log for detailed
>messages.
>
>If however the application waits for a little bit before closing the query
>handle, SQL 2005 completes the backup successfully.
>The query is not being called asynchronously by the client app.
>
>
>ENVIRONMENT:
>
>
>I've produced this problem the easiest with the calling application running
>on the same machine that hosts SQL 2005 (using 127.0.0.1 or (local) to
>connect).
>I've also reproduced this with the calling application residing on a
>different machine than the SQL 2005 server machine (using a LAN
connection).
>However I believe I've seen it succeed in this (LAN) scenario, but I'm not
>100% certain about this (I may have been stepping through the calling app
>with a debugger at the time).
>
>The SQL 2005 host machines that I've tried so far (two of them) have SQL
>2005 Developer edition installed, and are Windows XP SP2 machines (MDAC 2.8
>SP1).
>The client machines I've tried are the SQL 2005 host machines and also a
>Windows 2000 Pro SP4 machine with MDAC 2.8 SP1 installed.
>I've tried connecting using both the SQL Native Client in ODBC and the SQL
>Server ODBC driver (which the application normally uses).
>
>
>STEPS TO REPRODUCE:
>
>http://espnsti.in2hosting.org/BackupTest/BackupTest.zip
>http://espnsti.in2hosting.org/Backu...pTestSource.zip
>http://espnsti.in2hosting.org/Backu....usp_Backup.sql
>
>I've created and attached a small test app (written in Delphi 5.0) that
will
>back up the Northwind DB (or any other DB).
>To set up this app do the following:
>
>1) If you do not have the Northwind DB, get a copy and restore it or
>identify another DB to use.
>2) Modify the attached dbo.usp_Backup.sql script to use the correct DB and
>backup file path and name if needed.
> Run dbo.usp_Backup.sql against Norhtwind (or the DB that will be used.)
>3) Set up a new System DSN in the ODBC Data Source Admin control panel.
> Name it NorthWind, and be sure to set it to change the default DB to
>Northwind.
>4) Extract the attached BackupTest.zip to some directory.
>
>To use the app do the following:
>
>
>A) To see it fail:
>
>1) Enter the appropriate Data Source Name (and User ID and Password if
>needed).
>2) Leave the "Wait 5 seconds" check box unchecked, and click on Backup.
>3) You should see an error in the sql error log (and the event viewer)
>stating that the backup failed, and the backup file should not be there.
>
>
>B) To check that sql returns from the query before the backup is complete:
> (this only works accurately if the app and the SQL Server are on
the
>same machine)
>
>1) Enter the appropriate Data Source Name (and User ID and Password if
>needed).
>2) Check the "Wait 5 seconds" check box, and click on Backup.
>3) You should see the backup file appear and stay in the backup
>directory.
>4) In the event viewer application log, you should see three new
>informational entries:
>
>4.1) One from "BackupTest" that ends with "SQL Northwind Backup Control
>Returned To App: " followed by a time.
>4.2) Then a standard MSSQLSERVER message with details of the database
>backup.
>4.3) Finally there is a custom MSSQLSERVER message from the usp_Backup
>stored procedure that contains "SQL Northwind Backup Finished:" followed by
>a time.
>
>If you compare the time in the message from the 1st and last
>messages, you'll see that control was returned to the app before the backup
>finished.
> (Even though there probably is only a fraction of a second
>difference.)
>
>
>QUESTIONS:
>
>1) Can anyone reproduce this behavior?
>2) Has anyone run in to a similar problem (perhaps with something other
than
>a BACKUP sql statement)?
>3) Am I correct in thinking that this is a bug in SQL 2005 or was this a
>conscious change?
>4) Is there perhaps a configuration setting (in ODBC / SQL / etc...) that
>now needs to be used in SQL 2005?
>
>Thanks,
> Erik
>
>
>
>

ESPNSTI

2006-03-05, 8:24 pm

Hi,

My apologies, I had zipped up an older version of my test app, but that still should have worked.

If it isn't too much trouble, could you please double check that:
- The "Change default database to:" in the Northwind ODBC datasource is checked and set to NorthWind.
- The dbo.usp_Backup.sql is pointing to the correct backup path for your local sql environment.
- That dbo.usp_Backup.sql was ran against Northwind.
- Check that when you execute usp_Backup in Northwind from query analyzer, it functions normally.

If the above is all correct, then the test app ought to work.

http://espnsti.in2hosting.org/BackupTest/BackupTest.zip
http://espnsti.in2hosting.org/Backu...pTestSource.zip
http://espnsti.in2hosting.org/Backu....usp_Backup.sql

I probably will end up contacting MS Support, but for now I'm mostly just interested in seeing if someone can reproduce it. :)

Thanks,
Erik

"Peter Yang [MSFT]" <petery@online.microsoft.com> wrote in message news:bJpN8ZcPGHA.6304@TK2MSFTNGXA03.phx.gbl...
> Hello,
>
> I tried to run the exe directly on my test machine, and it seems the odbc
> query failed and it returned immediately with the time label. The same
> thing happens to SQL 2000.
>
> Since I don't have Delphi to test, I was not able to troubleshoot in the
> source code level. Since you are not able to reproduce the issue
> consistently, it might be a behavior change in SQL 2005. Please rest
> assured I have reported this situation to the proper channel.
>
> If you need further assistance on this issue, I recommend that you open a
> Support incident with Microsoft Product Support Services so that a
> dedicated Support Professional can assist with this case. If you need any
> help in this regard, please let me know.
>
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
>
> Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> ====================
====================
=============
>
>
> This posting is provided "AS IS" with no warranties, and confers no rights.

Peter Yang [MSFT]

2006-03-05, 8:24 pm

Hello Erik,

I tried to test the issue again and I was able to reproduce the issue. It
seems in SQL 2005 returns results to odbc call before backup operation is
finished.

I noticed that you set SQL_ATTR_ASYNC_ENABL
E to SQL_ASYNC_ENABLE_OFF
but it
seems SQL 2005 still executes asynchronously. I have reported this to the
proper channel. If there is any update, I will let you know.

Currently, you may try the Asynchronous Execution method to workaround this
issue. Or you may want to contact PSS directly to check this in a
efficient manner.

http://msdn.microsoft.com/library/d...-us/odbc/htm/od
bcasynchronous_execu
tion.asp

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
>From: "ESPNSTI" <ESPNSTI@nospam.nospam>
>References: <O$6ui6VPGHA.3728@tk2msftngp13.phx.gbl>
<bJpN8ZcPGHA.6304@TK2MSFTNGXA03.phx.gbl>
>Subject: Re: SQL 2005 Failing Backups Because it Exits The Query
Prematurely
>Date: Thu, 2 Mar 2006 09:04:29 -0600
>Lines: 57
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
>Message-ID: <u5BndqgPGHA.1088@tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.odbc
>NNTP-Posting-Host: 12.10.40.130
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.odbc:44756
>X-Tomcat-NG: microsoft.public.sqlserver.odbc
>
>Hi,
>
>My apologies, I had zipped up an older version of my test app, but that
still should have worked.
>
>If it isn't too much trouble, could you please double check that:
>- The "Change default database to:" in the Northwind ODBC datasource is
checked and set to NorthWind.
>- The dbo.usp_Backup.sql is pointing to the correct backup path for your
local sql environment.
>- That dbo.usp_Backup.sql was ran against Northwind.
>- Check that when you execute usp_Backup in Northwind from query analyzer,
it functions normally.
>
>If the above is all correct, then the test app ought to work.
>
>http://espnsti.in2hosting.org/BackupTest/BackupTest.zip
>http://espnsti.in2hosting.org/Backu...pTestSource.zip
>http://espnsti.in2hosting.org/Backu....usp_Backup.sql
>
>I probably will end up contacting MS Support, but for now I'm mostly just
interested in seeing if someone can reproduce it. :)
>
>Thanks,
> Erik
>
>"Peter Yang [MSFT]" <petery@online.microsoft.com> wrote in message
news:bJpN8ZcPGHA.6304@TK2MSFTNGXA03.phx.gbl...
rights.[color=darkred]
>
>
>

Sponsored Links