Dropping a Database Device

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.

    To drop a database device using the Manage Database Devices window

1. From the Server Manager window, select a server, and then from the toolbar, choose the Manage Devices button.

   

   The Manage Database Devices window appears.

2. Select a device, and then choose the Delete Device button.

   

   A confirmation box displays a list of any databases on that device.

   

3. To confirm deletion, choose Drop.

   The SQL Server database device is dropped, but the physical file still remains.

4. Delete the device's operating system file.

   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.

    To drop a database device using the Server Manager window

1. In the Server Manager window, select a server, and then open its Database Devices folder.
2. Click the right mouse button on a device, and then from the drop-down menu that appears, choose Delete.

   A confirmation box displays a list of any databases on that device.

3. To confirm deletion, choose Drop.
4. Delete the device's operating system file.

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.