SQL 2005 Take Database offline

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?
TimSr. System AdminAsked:
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.

Steve WalesSenior Database AdministratorCommented:
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

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
Scott PletcherSenior DBACommented:
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
TimSr. System AdminAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TimSr. System AdminAuthor Commented:
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
Steve WalesSenior Database AdministratorCommented:
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
TimSr. System AdminAuthor Commented:
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
Steve WalesSenior Database AdministratorCommented:
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
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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.