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?
Who is Participating?
ZberteocConnect With a Mentor Commented:
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:


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.
chenegarAuthor Commented:
Thanks, Z. That seems like a good solution and I'll give it a try.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.