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?