Solved

SQL 2005 Take Database offline

Posted on 2014-09-08
7
320 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:Scott Pletcher
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

803 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