Solved

SQL 2005 Take Database offline

Posted on 2014-09-08
7
310 Views
Last Modified: 2014-09-12
I have a database that is no longer used by any application and would like to take it offline then remove from the server to free up some disk space for a new database. When I try to use the management studio and select Tasks\Take Offline the task just spins and the database is never taken offline. When I try to use the following script:

 "ALTER DATABASE [myDB] SET OFFLINE WITH ROLLBACK IMMEDIATE"

to take the database offline I get the
this error:

Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'DatabaseName'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

What do I need to do to get this database offline and removed from the server?
0
Comment
Question by:tparus
  • 3
  • 3
7 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 40310321
Try to set it to single user mode first ?

Alter database [mydb] SET single_user WITH ROLLBACK IMMEDIATE

Then try to set offline.

I've found that for these kinds of operations, forcing single user mode first often helps
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40310354
1) NEVER use the gui for things like this.

2) See if a backup or some other serialized process is active on that db.  You won't be able to offline a db while it's being backed up, of course :-).
0
 

Author Comment

by:tparus
ID: 40310773
How can I see what process/application is using that database? I thought I had killed all the processes in our network that would be using this database but obviously I missed something. I will try the single user mode tonight when there is no one on the database to make sure there is nothing going on with the database(s) that might be connecting to it.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:tparus
ID: 40312119
Thanks Steve that worked and I was able to drop the database I wanted to get rid of. Now how do I recover the disk space that database used?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40312166
If you are 100% certain that you no longer need anything in the database, you should just be able to delete the data file(s) and logfile that make up this database.

However why not just drop the database if you want it gone ?  That way all traces of it are removed from the instance and the underlying disk files are also gone.

Taking it offline, then removing the files is going to leave you with an entry in your list of databases with no files behind it.
0
 

Author Comment

by:tparus
ID: 40312301
I did drop the database but I did not recover any disk space. The mdf and ldf files are still in the data folder so I should just remove those files and that will be it, correct? I used 'Drop Database [mydb]' in the query analyzer and it removed the databases from my server but it did not delete the files. Was it supposed to or was I supposed to add another line to the drop script that would delete those files?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40312316
When working in Management Studio in 2005 and up, dropping a database removes the data files / log files.

You say "Query Analyzer" so I assume you're working with SQL Server 2000 (or at least the toolset).

According to the documentation: http://technet.microsoft.com/en-us/library/aa258843%28v=sql.80%29.aspx it should also drop database files in SQL Server 2000.

So, not really sure WHY they didn't get removed.  If you're 100% certain that the data files aren't being used, I guess you could delete them, but proceed with care.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now