Can't Delete MS SQL 2005 Database

Hello,

I am using Windows Server 2003 r2, MSSQL Server 2005, sp 3.

I have a copy of my production database that I use for testing, called play. I want to delete the play database and make a new copy of my production database, but when I try to delete the play copy, it says I can't because it is currently being used for replication.

Replication was at one point (sort of) set up on the production database (never functional), but never the play database.

I need to get an updated copy of the play database available, with the same name as what the current one is, soon for some important testing.

I have a feeling I'm missing something simple...  Any suggestions would be most appreciated. Thanks.
CCUITAdminAsked:
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.

DrutchCommented:
Have you tried deleting the subscription?
0
CCUITAdminAuthor Commented:
I'm still pretty new to SQL, how would I do that?
0
DrutchCommented:
In MSSM studio connect to your "play" server and expand replication then expand local subscriptions find the subscription that is subscribing to your production server and right click, select delete.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DrutchCommented:
You may have to go into your publications on your production server and delete the subscription from there first, depending on how the replication was configured.
0
CCUITAdminAuthor Commented:
Both Play and Production are on the same server. There is another server that was set up to use replication but it never was really working properly (call it reports server).

There are no publications or subscriptions on the server that houses play and production.
The reports server has two publications on it, but they are both pointing to production, not play.

I was wondering that since I am currently getting my play copy by creating a blank db and restoring a backup of production into play, if the replication was set up on production and then a copy was made into play, would it have gotten the same replication settings that production had?
0
DrutchCommented:
Hmm, not sure really. I usually use the copy database wizard. I have in the past shut down the service in Control Panel - Administrative Tolls - Services ...SQL Server (MSSQLSERVER) ..Stop. Then move the mdf. and restart the service. Though it's not the best solution but a last ditch effort. I'm sure that method leaves loose ends hanging around and seeing as one of your DB's is named production I doubt you can stop the service.
0
CCUITAdminAuthor Commented:
Okay....

So any thoughts on how I can just get the durn thing to delete? I can't stop the services because it's running our production database for our campus management system. Essentially the service has to be up 24/7, except under extreme circumstances.
0
CCUITAdminAuthor Commented:
The play database is also stuck in single user mode. I can't get into properties (as sa, mind you) because it says there is already somebody logged in, but when I go to, say, detach the play database, it tells me that there are no open connections, it just says that it's being used for replication. But nothing I can find points to it being used for replication.

Please help.
0
DrutchCommented:
Try

ALTER DATABASE play SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE play SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
 See if that gets you out of single user mode.
0
CCUITAdminAuthor Commented:
Here's what I get when I try that:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'play' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'play' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
0
DrutchCommented:
Ok if you go to Management Node and expand and open the Activity monitor, does it show any processes runnning against the database? If so right click on the process and kill it.
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
DrutchCommented:
With the excpetion of your managment studio session, that is.
0
CCUITAdminAuthor Commented:
Yes, thank you!!! I was able to delete it finally.
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 2005

From novice to tech pro — start learning today.