Query timeout expired

Data Abstract
1

Hello,
I’m working at a mechanism for database back-up and restore (sql server) for my application, the back-up process works fine, but the restore process it raises am exception : "Project… raised exception class EROUnregisteredServerException with message ‘An exception was raised on the server: Query timeout expired’."
I do this process as fallows:
From client application I upload the .bak file on the server, after that it calls a method from Server application that it initiates a SQLExecuteCommand who has in it the “restore database… from disk …” ( t-sql statement), this command it executes ok on the sql server but after 50 second it terminate the session on sql server an it raises the the exception mentioned above, and the database remains in restoring state forever.
I use TROIndyTCPChannel/Server for my application, can you provide me some help with this problem?

2

Hello,

Maybe your SQL server has timeout value less than 50 seconds, that is why query execution is interrupted. Try to change it. Just in case increase TROIndyTCPChannel Timeout value too.
If this doesn’t help give us more information about your project: what DA and Delphi version you use, what is your SQL server and driver.

Best regards

3

The SQL Server Timeout value is 1800 seconds, so is not from this.
I have changed the TROIndyTCPChannel Timeout and it it gives read timeout error
I use the latest version of DA and Delphi XE2 latest update, SQL Server 10.50 Driver ADO AuxDriver: SQLNCLI10.1 , and still don’t work.

4

Check your ADOCommand.Timeout

5

Where is this because I’am using SQLExecuteCommand who gets a string as a parameter and I din’t find ADOCommand.Timeout, can you be more specific.

6

Hello,

Set custom parameter for ADO connection string in Schema Modeler CommandTimeout=n;

It has 30 sec value by default.

Best regards

7

I’ve tried this before, and now, and still don’t work same error.

8

Hello,

I’ve tried this before, and now, and still don’t work same error.

Are you sure that you set enough CommandTimeout value for restore operation?

I have tested the following scenario with Northwind database:

  1. Database is backed-up
  2. database is deleted
  3. database is restored

If connection uses default CommandTimeout - I get ‘Query timeout expired’ exception. After setting CommandTimeout to 200 everything works as expected.
What is your scenario? What is your DB size?
Give me exact t-sql scripts that you use for DB backup and restoring, maybe some specific options influence the situation.

Best regards

9

Hello, I’ve been testing various values for CommandTimeout and nothing, my db has 300K records in three tables an is about 87 Mb (compressed), the scenario is the one from first post.
T_SQL Script for back-up is:

SQLExecuteCommand(‘BACKUP DATABASE +’ TO DISK = ’ + <.bak filepath destionation > + ’ WITH COMPRESSION’);

T-QSL Scrit for restore is :

SQLExecuteCommand(‘RESTORE DATABASE ‘+ +’ FROM DISK = ‘+ <.bak filename> +’ WITH FILE = 1, NOUNLOAD, REPLACE, MOVE N’+ <DbLogical_name> TO N’+ + ‘, MOVE N’+ <LogLogical_name> +’ TO N’+ );
( I want to restore the DB to a new location with a new name.)

For small db’s that is restored in less than 50 seconds it works for bigger ones it doesn’t!

10

Hello,

All the same, RESTORE operation lasts more then 200 sec for my DB and executes successfully.
Could you attach simple testcase reproducing situation? Make it with your DB and I will retarget it to my one.

Best regards

11

Hello,

That means that it may be some problems with our sql sever instance, I will try it on a new instance. This problem it apears for every db that it takes more than 50 seconds to be restored, so il try it on a new instance and come back with the result.

Best regards!

12

Hello,
We finally managed to figure it out, it seems that was a problem with our sql server instance, some faulty configurations, now it’s working as it should, thank you for your support!

Best regards!