Solved

SQL 2005 Take Database offline

Posted on 2014-09-08
7
315 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with SQL Server Stoplist 2 20
Can't connect to new installation of SQL Server 2016 6 31
TSQL previous 5 25
TSQL - How to perform an IF… SELECT? 2 0
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 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

21 Experts available now in Live!

Get 1:1 Help Now