Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kowani1
Starting Member
42 Posts |
 Posted - 2006-12-19 : 18:51:38
|
| Hi All,I have a DTS package that takes a backup of prod, and then restores it to a specified test DB - thus refreshing test data with Prod data.Now the issue I am having is "Exclusive access could not be obtained because the database is in use", but I know that there are no processes, as there is also a step that goes thru and terminates any processes.I wonder what could still have the db open.Please help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-19 : 18:57:45
|
| Well it didn't terminate all access, that's why you are getting the error. Perhaps a spid didn't rollback its transaction fast enough, so when the restore occurred, it was still connected. Maybe you could add a delay in your process using WAITFOR. BTW, why are you using DTS for this? Why not just put this into a job directly? Why add the overhead of a DTS package?Are you using the ALTER DATABASE command to get rid of the connections?Tara Kizer |
 |
|
|
kowani1
Starting Member
42 Posts |
Posted - 2006-12-19 : 20:38:34
|
| I know there is an overhead with with DTS package, but I found it more easier to follow throught the steps using the DTS package.I am open to any suggestions that will ultimately achieve the goal of refreshing my test DB with a copy of the production data evey night..BTW: I tried using WAITFOR and still having the same issues.. Maybe I will increase the wait interval from a few minutes to say 20 or 30 mins..thanks.Jungla DBA |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-12-19 : 21:32:03
|
quote:
Originally posted by kowani1
I know there is an overhead with with DTS package, but I found it more easier to follow throught the steps using the DTS package.I am open to any suggestions that will ultimately achieve the goal of refreshing my test DB with a copy of the production data evey night..BTW: I tried using WAITFOR and still having the same issues.. Maybe I will increase the wait interval from a few minutes to say 20 or 30 mins..thanks.Jungla DBA
Maybe the problem is that your DTS package is connecting to the database you are trying to restore.Make sure you are connecting to the master database.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-19 : 21:32:45
|
| What command are you using to disconnect the users? And the answer to your e-mail is no. Post your questions on the forum rather than sending them directly to a forum member.Tara Kizer |
|
|
|
|
|
|
Issues Restoring within a DTS package