Restoring msdb - SQL Agent stopped, but says it's still in use

Question

Restoring msdb - SQL Agent stopped, but says it's still in use

reefshark

Ten Centuries

Points: 1340
More actions
March 28, 2012 at 3:24 pm

#253871

Hello!
I have a new server that I have installed SQL Server 2008 R2 SP1 on. I created a single user database on it and ran a script (provided from a 3rd party vendor) to create a few objects in the new database. Unfortunately, by accident, I ran the script on the msdb, rather than the user database. Now I have tables and views and in the msdb that aren't supposed to be there. I decided to attempt a restore on the msdb from a backup that I created last night. I stopped the SQL Agent via Config Manager and attempted the restore. The restore failed, and I am getting the following message:
[font="Courier New"]Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.[/font]
I have nothing else running or accessing the server, so what else is using the msdb if the SQL Agent is stopped?
Thanks for your help!
March 29, 2012 at 4:02 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 1

reefshark (3/28/2012)
Hello!
I have a new server that I have installed SQL Server 2008 R2 SP1 on. I created a single user database on it and ran a script (provided from a 3rd party vendor) to create a few objects in the new database. Unfortunately, by accident, I ran the script on the msdb, rather than the user database. Now I have tables and views and in the msdb that aren't supposed to be there. I decided to attempt a restore on the msdb from a backup that I created last night. I stopped the SQL Agent via Config Manager and attempted the restore. The restore failed, and I am getting the following message:
[font="Courier New"]Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.[/font]
I have nothing else running or accessing the server, so what else is using the msdb if the SQL Agent is stopped?
Thanks for your help!

I would imagine that SQL Server itself uses it for stuff like keeping track of backups, replication stuff, etc, just from a cursory look.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Kill the connections still using msdb.

But rather than that - I would likely just drop the new objects and move on.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

reefshark Ten Centuries Points: 1340 More actions · Answer 3

I would drop those new objects, but there are hundreds of tables that were created. Is there any EASY way of doing this?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Ask the vendor if they have a rollback script.

Sometimes they do.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events