Dropping a device frees up storage space on the server. When you use SQL Enterprise Manager to drop a device, all databases on it are also dropped. Only the SA has permission to drop devices.
Dropping a database device frees the file handle and the device number. This means that you can delete the physical file from the hard disk without shutting down SQL Server. You can also immediately create a new device with that same device number and logical name. (You can use the same physical name as well, as long as you've deleted the physical file.)
There are two ways to drop a device using SQL Enterprise Manager: from the Manage Database Devices window and from the Server Manager window.
The Manage Database Devices window appears.
A confirmation box displays a list of any databases on that device.
The SQL Server database device is dropped, but the physical file still remains.
You must delete the device's filename from the directory where it is located. Use File Manager or the operating-system del command. It is not necessary to shut down and restart SQL Server.
A confirmation box displays a list of any databases on that device.
You can also drop a device by using the sp_dropdevice stored procedure. However, when you use sp_dropdevice to drop a device, you must drop all databases on that device before you can drop the device. For information about sp_dropdevice, see the Microsoft SQL Server Transact-SQL Reference.
Dropping a device removes the device from the sysdevices table.