Try using Query Window.
USE MASTER;
GO

ALTER DATABASE <DBName>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

DROP DATABASE <DBNAME>;

Will this save a lot of time?  From what I read on-line the drop and delete do the same thing, so would it make any difference?  How do I terminate the existing delete function?

You mean, dropping the database correct? To terminate the existing drop in management studio, simply close the drop database window.

Yes, that's what I meant.  But will the SQL statement be any faster?  I have been running the Delete all morning and would hate to "start over".

Restart the SQL server and try again to drop the database.

Do you have any replication or mirroring or logshipping setup in that database? otherwise you are safe to run the above query.

Restart is not preferred (especially in production), restarting the server will clear the plan cache procedure cache and some DMV information, making SQL Server with some performance problems.

Do you mean restart the SQL Server Agent?  Why do I need to do that?  This db has been offline for some time.  I also have a production database on this server and would rather not have to do this unless it's absolutely necessary.  Is there any way to determine if there is activity on the "delete"?  How long does it usually take to delete a database?  Is it unusual that it would take a few hours?

BTW, hope it doesn't sound like I am challenging anyone's expertise - definitely not the case.  Just trying to understand the process so I can make an informed, wise decision on how to proceed.  I appreciate everyone's help immensely!

I don't prefer to restart the server or any service, restarting the service or server will bring downtime for other databases.

This is very simple issue, bring the database online first.

USE MASTER
GO
ALTER DATBASE <dbname> SET ONLINE

then follow my previous post query to drop the database safely.

No, no replication, mirroring or logshipping.  I initiated the Delete about 2 1/2 hours ago.  It says it is "executing" and the execution symbol next to it is rotating, which seems to indicate it is active.  I have read that the delete can take some time, and that doing it via management studio or sql will take the same amount of time.  Is this true?  Basically, I am trying to understand about how long it will take so I can determine if I should be concerned that it hasn't completed.  And, to learn if there is any way to determine whether it is looping, hung, or running a normal process.

I am preparing to follow arunjb's instructions.  Before doing so, can someone confirm these instructions will run faster than the Delete via Mgmt Studio?  Will it get delete all the files (as the Mgmt Studio Delete does?  The only reason I am doing this is to reclaim disk space on the server.

Yes what you read is true, any operation through queries are faster when compare to SSMS.
You can cancel it, and give a try using query.

I meant restart the SQL server, not the agent. I didn't mention agent. Right click on the server name in the Management Studio and click Restart, or Stop and then Start.

There is something wrong with that database which could be repaired by restarting the server. Dropping an offline database should not be a problem and it should be immediate unless maybe is a huge one.

I am trying to close the drop database window in management studio, but it's not terminating.  When I click on the "X" in the right corner, it does nothing.  If I right click and select Close, it does nothing.  Looks like I am going to have to restart sql server anyway.  Will be trying it via SQL in a few and will keep you posted.

can we just have some clarification ...

what do you mean by deleting the database....

actually deleting the physical/database files ... removing it from the sql server instance

or

deleting the rows from the table(s) in the database but leaving the physical database  still existing...

?

what database recovery model are you using ?
where is the database located ... local disk/SAN ?

how big is it   rows/GB of data...?

Kill it from the Task Manager.  This will kill the Management Studio app as well but that's fine. You open it again and then do the restart.

FYI, when you restart SQL Server, it restarts SQL Server AND the Agent.  No problem, as I already informed users the production database would be taken offline, just in case (glad I did that).  Just wanted to let you know.

Well, of course it does that but I don't see how this is relevant to your problem. :o)

FYI again, looks like if I had tried to close Management Studio, rather than just closing the Delete window, it would have terminated the Delete function.  I have a feeling that's what you were trying to tell me to do in the first place - I misunderstood.  Oh well, at least it is finally terminated so I can run the SQL statements now.

It doesn't matter which one you choose because both will be killed anyway.

And you can try the delete from SM again, it doesn't have to be the SQL statements.

Zberteoc,  It's not.  I only mentioned it to clarify our stream earlier.  I was trying to find a way to fix this without having to restart services that would effect the production db on this server.  I had misunderstood your comments and thought you were saying there was a service that could be restarted that wouldn't restart the agent, and wouldn't impact all databases on the server.

Kill the session that is executing Drop statement as @Zberteoc suggested.

Run sp_who2, this will give the list of sessions currently executing, find out the session with Drop database , take the session Id and run KILL <SessionID> this may takes more time if it needs to rollback the work done by this session earlier.

Can you run DBCC OPENTRAN to check for any open transaction?

I did not know this was a production environment, however sometimes the only solution to problems is server restart. For some reason the drop of that db was hanging and there is not much you can do in that case. You could have waited until after hours, during night time, to minimize the impact in prod. Dropping an offline db is not a priority.

another option is to detach the database and then delete the MDF & LDF Files.

I just noticed lowfatspread's questions so will wait for further instruction before starting the queries.  The Delete has been terminated, SQL Server restarted.  Database is still offline.  

My objective: reclaim disk space on this server.  I want to delete all physical files (or as many as possible) associated with this particular database (and corresponding metadata from system tables/databases).

I ran a Delete from SQL Server Mgmt Studio. After 21/2 hours I thought I best ask someone if it's normal for it to take so long.  That led to my terminating the process.  Now that I terminated the process, I'd like to know the quickest method to meet my objective (reclaiming disk space) and have some idea how long will it take.

The database is on a dedicated db server, which I am logged on to (as admin).  The physical .mdf file is about 450mb.  

Do I proceed as advised and run the SQL statements posted in this stream or start the Delete from Management Studio again (or do something else)?  How long should it take and is there any way to ensure it is working properly if it's taking a long time?

FYI, dropping an offline database is a priority because my Backups are failing because I don't have anough tapes to handle all the data, so I have to reduce the amount of data being backed up.  And restarting isn't that big of a deal on this database, as there arent many users - just prefer not doing so unless I have to.  I will read through the comments and perform whatever is suggested (and respond with any additional info requested).  Thank you so much - it's great to have this site!

Ran the DBCC  OPENTRAN - no open transactions.  SP_who2 doesn't show the DELETE process.

Try to delete it now.

So glad I took your advice.  I restarted sql server, ran the Delete from MSS Mgmt Studio, and it's done!  Thank you!

Glad I could help.

Just curious - What was the databse size that you deleted?

If you database is offline and you are deleting it. It will not delete files from directory.

After deleting offline database you have to manually delete the files.