It looks like a silly question but I’d like to mention some cool stuff about
it. When you drop the database it should delete the database file and the log
file accordingly. At least this is what we expect. But what if the database is
off-line? Haha, give it a try. Your drop script will delete the data but the
data and log files stay there. Now if you want to create another database with
the same name you see this:
But how this is related to SharePoint?
Remember that when you create MOSS or WSS Site collections it gives you the
chance to pick a database name? Now remember when you uninstall SharePoint it
doesn’t delete the databases?
Ok, here is what happens. You uninstall MOSS or delete Site Collection. Then you
may think that, I may use these data in the future or for any reason database is
in use and you cannot delete it so you take it off-line and then you delete and
forget it while the files are still there. When you want to create another site
collection or a new installation the existing files will not let you create the
new database.
But why do you want to take the database off-line?
Taking the database off-line means that we don’t want anybody connect to the
database anymore but we are polite enough to let the current users to the
database finish their work. It doesn’t kill the existing connections and it
takes some time for all the live users to finish their work and disconnect and
in the meanwhile no new connection is made to the database. Usually when junior
database administrators want to delete a database that there is live connection
to that and they come up to related errors they try to take the database
off-line, which is a good move, but do you really want to keep the current users
when you want to delete what ever the data that they enter? So to drop a
database simply go to SQL Server 2005 management studio and Management ->
Activity Monitor. If you just double click on the activity monitor you can
see a list of all the databases and existing connections to them. Simply right
click on the database and kill the connection. Then you will be good to delete
the database.
Cheers
Alireza