?
Solved

SQL 2005 Take Database offline

Posted on 2014-09-08
7
Medium Priority
?
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 2000 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

800 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