Printable View
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.
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 ?
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!
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.
Can you run a statement like this:
From a query window - change the DATABASE name obviously...Code:BACKUP DATABASE Funds
TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_f23.bak'
Tell me if it runs in seconds instead of minutes.
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.
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.
I have not experienced it but would backing-up the usual way not sort of also lock the tables?
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.
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?
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.
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.
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.
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.