A failed job

“What does your scheduled task look like?” asked Sam. “And what is the failure message?”

“Well, the TSQL command I’m running is…

load database testDb from disk='f:mssqlbackupweeklyDump.dat'

…and the failure message I’m getting is ‘Database in use. System Administrator must have exclusive use of database to run load. (Message 3101)’ But there can’t be anyone in the database; I’m the only user on the system.”

“Hmmm…” mulled Sam. “Have you tried running sp_who or sp_who2 to see if there are users in your database? You might be surprised…it might be you, if you have any query windows open on that database.”

“Sql Sam, you’re the greatest!” exclaimed Jimmy. “I’m sure that’s it!” Jimmy scurried off to make sure he wasn’t connected to the database.

A little while later, Jimmy came back, his shoulders slumped. “Well, that seemed like the problem… I had two query connections open. But I closed those, and I still get the same failure. I even ran sp_who before and after I ran my task, and there wasn’t anyone in the test database. All my connections are connected to master. There must be some sort of phantom connection that I can’t see.”

“Let’s look at your scheduled task, Jimmy,” said Sam. They opened the task up in the SQL Enterprise Manager:

Task Properties
“Ah-ha!” said Sam. “Not all of your connections are to master!”