sp-dropdistributiondb-transact-sql.md

title	sp_dropdistributiondb (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/03/2017
ms.prod	sql
ms.prod_service	database-engine
ms.reviewer
ms.technology	replication
ms.topic	language-reference
f1_keywords	sp_dropdistributiondb_TSQL sp_dropdistributiondb
helpviewer_keywords	sp_dropdistributiondb
ms.assetid	b6dd1846-2259-4d29-93af-a70a5d25a0c5
author	stevestein
ms.author	sstein

sp_dropdistributiondb (Transact-SQL)

[!INCLUDEappliesto-ss-asdbmi-xxxx-xxx-md]

Drops a distribution database. Drops the physical files used by the database if they are not used by another database. This stored procedure is executed at the Distributor on any database.

Transact-SQL Syntax Conventions

Syntax

  
sp_dropdistributiondb [ @database= ] 'database'  

Arguments

[ @database = ] 'database' Is the database to drop. database is sysname, with no default.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_dropdistributiondb is used in all types of replication.

This stored procedure must be executed before dropping the Distributor by executing sp_dropdistributor.

sp_dropdistributiondb also removes a Queue Reader Agent job for the distribution database, if one exists.

To disable distribution, the distribution database must be online. If a database snapshot exists for the distribution database, it must be dropped before disabling distribution. A database snapshot is a read-only offline copy of a database, and is not related to a replication snapshot. For more information, see Database Snapshots (SQL Server).

Example

[!code-sqlHowTo#sp_DropDistPub]

Permissions

Only members of the sysadmin fixed server role can execute sp_dropdistributiondb.

See Also

Disable Publishing and Distribution
sp_adddistributiondb (Transact-SQL)
sp_changedistributiondb (Transact-SQL)
sp_helpdistributiondb (Transact-SQL)
Replication Stored Procedures (Transact-SQL)