sp_dropdevice System Stored Procedure

Removes a SQL Server database device or dump device.

Syntax

sp_dropdevice logical_name [, DELFILE]

where

logical_name

Is the logical name of the database device or the dump device as listed in master.dbo.sysdevices.name.

DELFILE

Specifies that the physical file should be deleted.

Remarks

The sp_dropdevice system stored procedure drops a database device or dump device from SQL Server, deleting the entry from master.dbo.sysdevices. It frees the file handle and the device number when a database device is dropped. This means that the physical file can be deleted without shutting down SQL Server. To delete the physical file, use the DELFILE option or delete the file from the command line.

Note You can also immediately use the DISK INIT statement to create a new device with that same device number, logical name, and physical name (if you've deleted the file).

Permission

Execute permission defaults to the system administrator and cannot be transferred.

Tables Used

master.dbo.sysdatabases, master.dbo.sysdevices, master.dbo.sysusages

Example

This example drops the TAPEDUMP1 tape dump device from SQL Server.

sp_dropdevice TAPEDUMP1

See Also