Queries timeout while DTS is running

[dee-u]

I need to execute a DTS that would transfer one SQL Server database to another and while the DTS is executing certain queries from my application are timing out when executed, is there a workaround to avoid such timeout? I have adjusted the timeout properties of my connection and command objects but still it is bombing and it would not be good if a certain user waits infinitely for the program to continue.

[NeedSomeAnswers]

I take it you can't run you DTS out of hours ??

Assuming this, how are you transferring the data, are you using a Transform Data Task ?

[szlamany]

The most efficient way to get a database moved is with BACKUP/RESTORE - how are you doing it?

If you are doing with some kind of SELECT-logic then there a lock-hints you can use - but you certainly don't want to get dirty reads.

You need to feed us lots more info here!

[dee-u]

I am using the Copy SQL Server Objects Task to transfer the database, it is more automated than back-up and restore so I choosed it. And the DTS just runs for minutes, probably less than 10 minutes.

[szlamany]

Can you run a statement like this:

BACKUP DATABASE Funds 
   TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_f23.bak'

From a query window - change the DATABASE name obviously...

Tell me if it runs in seconds instead of minutes.

[dee-u]

I could do that, I have a back-up facility that does that though its been a while since I last used it, its the users who usually executes it now. I am transferring from one computer to another one so I am using the Copy SQL Server Objects Task, if I could automate the same using back-up and restore then it should be great, that is if it solves the timeout problem.

[szlamany]

That's my whole point.

I don't know what TRANSFER OBJECT's is doing under the hood.

If the backup takes seconds then we can consider how to automate that to move the DB.

Having a process hitting the DB for 10 minutes - possibly locking tables - could be caused by the TRANSFER OBJECT's method.

[dee-u]

I have not experienced it but would backing-up the usual way not sort of also lock the tables?

[szlamany]

I don't know how large your DB's are - but I've seen backups of 5+ gigabyte databases take less then a minute.

But no - locking doesn't occur in a backup as it does in other table operations.

That's because BACKUP is an integral part of the log-ahead concept that SQL uses. The backup intentionally works the LOG file in a fashion that leaves - recognizes - which transactions are open and which are already committed. In that way a restore auto-rollbacks any open transactions done when the backup had started.

We run backups on production db's with 1000+ users all day long - some full backups and some just transaction log backups. We have never in 10 years encountered one single deadlock in any of our applications.

[dee-u]

The last time I checked it is less than 400MB so I guess I could just go that route, back-up the database, transfer it to the other machine then do a restore, now, how would I be able to do that?

[szlamany]

The command I gave you can be put into a SPROC.

Then a DTS job can run that step - then a second step of a .BAT file that copies the .BAK to another server.

Then how it gets restored on that server would be a second DTS job on that server?

I'm guessing all that would work.

[NeedSomeAnswers]

Yes listen to szlamany,

this is exactly what you should do !!!

It should be quicker than the Copy SQL Server Objects Task and its simple to call a stored proc from a DTS you just use an Execute SQL Task.

[dee-u]

Of course, SZ's suggestions is top on my list but if someone else out there who knows more behind the 'Copy SQL Server Objects Task' then I would still welcome any other options on that matter.

[szlamany]

From my reading on COPY SQL SERVER OBJECT with GOOGLE it looks like it's more of a development, installation and/or DBA task. It appears to be doing things at an object-by-object basis.

If that's the case (and I'm only 90% sure it is) then it's not something to ever be used on a production server with users connected.