SQL Server 2008 DTS Package Leaves Database in Single-User Mode

I have a DTS package that puts a database into single-user mode, copies it to another server, then puts it back into multi-user mode. I created it using the Copy Database Wizard. Both SQL servers are SQL Server 2008 R2. The job runs in the middle of the night and most nights it works just fine.

The job is run on the server the database is being copied to.

Now and then, however, the job starts, puts the database into single-user mode, deletes the database on the server being copied to, then stops. The database doesn't get copied nor does it get put back into multi-user mode. All I see in the logs is that the database was put into single-user mode, nothing else. When it works correctly, the log also shows the database starting up about 3 minutes later, which is when the job has finished.

Without buying monitoring software, is there some way to figure out what's going on?
chenegarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
I recommend you to use the snapshot replication instead of an old DTS package. It is straight forward and available and also easier to monitor.  Here is how you do it:

http://technet.microsoft.com/en-ca/sqlserver/ee848811.aspx

The snapshot replication does pretty much the same thing you are doing with the DTS, creates a snapshot(backup) of the database on the source server, called the publisher, and then applies it on a different server, called subscriber.

If you simply want to keep the 2 databases in sync you can also use transnational replication, which after initial setup uses much less bandwidth to keep them in sync.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chenegarAuthor Commented:
Thanks, Z. That seems like a good solution and I'll give it a try.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.