System Stored Procedures
SQL Server system stored procedures for administrative tasks, configuration, security management, database maintenance, and replication.
Adds a new parameter and its value to an agent profile. This stored procedure is executed at the Distributor on any database. The ID of the profile from the To find out what agent type this field value. The values are as follows:
Creates a new profile for a replication agent. This stored procedure is executed at the The ID associated with the newly inserted profile. parameter. If specified, the value is set to the new profile ID. The type of replication agent.
The name of the alert. The name appears in the e-mail or pager message sent in response to the alert. It must be unique and can contain the percent ( The message error number that defines the alert.
Adds the specified category of jobs, alerts, or operators to the server. For alternative method, The class of the category to be added.
warehouse database. The procedure must be executed in the context of the management data The GUID for the collector type. (with EXECUTE permission) fixed database role. The following example adds the Generic T-SQL Query collector type to the view. By default, the Generic T-SQL Query collector type already exists. Therefore, if you run this code on a default installa
Adds a data file to a filegroup when recovery can't complete on a database due to insufficient space on the file group (error 1105). After the file is added, this stored procedure turns off the suspect setting and completes the recovery of the database. The parameters are the same as The filegroup you're adding the file to. which indicates the primary file.
Creates a new job executed by the SQL Server Agent service.
Creates a schedule for a SQL Server Agent job.
Targets the specified job at the specified server. The identification number of the job. must be specified, but both can't be specified. must be specified, but both can't be specified.
Adds a step (operation) to a SQL Server Agent job.
Adds a log file to a database when recovery can't complete on a database due to insufficient log space (error 9002). After the file is added, the suspect setting and completes the recovery of the database. The parameters are the same The name used in the SQL Server to reference the file. The name must be unique in the server.
This stored procedure checks to see if an alert job has been created on this server. If an alert job doesn't exist, creates the alert job, and adds its job ID to table. The alert job is enabled by default, and runs on a schedule of once every two minutes. The SQL Server Agent job ID of the log shipping alert job. must be run from the database on the monitor server.
Sets up the primary database for a log shipping configuration, including the backup job, local monitor record, and remote monitor record.
This stored procedure adds an entry for a secondary database on the primary server.
Sets up a secondary database for log shipping.
Sets up the primary information, adds local and remote monitor links, and creates copy and restore jobs on the secondary server for the specified primary database.
Adds a maintenance plan and returns the plan ID. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies the name of the maintenance plan to be added. Specifies the ID of the maintenance plan. This stored procedure is used with databas
Associates a database with a maintenance plan. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies the plan ID of the maintenance plan. This stored procedure is used with database maintenance plans. This feature has been replaced w
Associates a maintenance plan with an existing job. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies the ID of the maintenance plan. Specifies the ID of the job to be associated with the maintenance plan. , and must be a valid I
Sets up a notification for an alert. The alert for this notification. The operator to be notified when the alert occurs. The method by which the operator is notified.
Adds an object (that is, a table) to the stream group for the feature introduced in SQL Server 2025 (17.x) and Azure SQL Database. Specifies the name of the event stream group you want to add the table to. , with no default, and can't be Change event streaming is currently in Azure SQL Database (preview feature database scoped configuration not required).
Creates an operator (notification recipient) for use with alerts and jobs.
Adds the specified SQL Server Agent proxy.
Creates a schedule that can be used by any number of jobs. Indicates the current status of the schedule. , the schedule isn't enabled. When the schedule isn't enabled, no jobs run on this schedule.
Adds the specified server group.
Adds the specified target server to the specified target server group.
2017 (14.x) and later Adds an assembly to the list of trusted assemblies for the server. This procedure adds an assembly to The SHA2_512 hash value of the assembly to add to the list of trusted assemblies for the server.
Adds an application role to the current database.
Creates an article and adds it to a publication. This stored procedure is executed at the Publisher on the publication database.
Configures a Publisher to use a specified distribution database. This stored procedure is executed at the Distributor on any database. The stored procedures must have been run prior to using this stored procedure. Server name can be specified as for a named instance. Specify the port number for your connection when SQL Server is deployed on Linux or Windows with a c
Creates a new distribution database and installs the Distributor schema. The distribution database stores procedures, schema, and metadata used in replication.
table (if there isn't one), marks the server entry as a Distributor, and stores property information. This stored procedure is executed at the database to register and mark the server as a distributor. In the case of a remote distributor, it's also executed at the Publisher from the , with no default. This parameter is only used if setting up a remote Distributor. I
Creates an agent job that generates a filtered data snapshot for a publication with parameterized row filters. This stored procedure is executed at the Publisher on the publication database. This stored procedure is used by an administrator to manually create filtered data snapshot jobs for Subscribers. Create a Snapshot for a Merge Publication with Parameterized In
Registers the name of a new extended stored procedure to SQL Server.
Adds a new extended property to a database object.
be specified in the parameter. Optionally, the connection string can also supply a failover partner name. from a local login, or a login that isn't part of the role, you might receive the following error: To resolve this issue, add the parameter to your connection string.
Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.
Creates a new SQL Server login that allows a user to connect to an instance of SQL Server by using SQL Server authentication. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. When possible, use Windows authentication.
Adds a Log Reader agent for a given database. This stored procedure is executed at the Publisher on the publication database. The login for the Windows account under which the agent runs. This Windows account is always used for agent connections to the Distributor. On Azure SQL Managed Instance, use a SQL Server account. When configuring a Publisher with a remote
Adds the ability for a Subscriber to use an alternate synchronization partner. The publication properties must specify that Subscribers can synchronize with other Publishers. This stored procedure is executed at the Subscriber on the subscription database.
Adds an article to an existing merge publication. This stored procedure is executed at the Publisher on the publication database.
Adds a new merge filter to create a partition based on a join with another table. This stored procedure is executed at the Publisher on the publication database.
Creates a dynamically filtered partition for a subscription, filtered by the values of at the Subscriber. This stored procedure is executed at the Publisher on the database that is being published, and is used to manually generate partitions. The merge publication on which the partition is being created. The value used when creating the partition for a subscription,
Creates a new merge publication. This stored procedure is executed at the Publisher on the database that is being published.
Adds a pull subscription to a merge publication. This stored procedure is executed at the Subscriber on the subscription database. , with a default of the local server name. The Publisher must be a valid server.
Adds a new agent job used to schedule synchronization of a pull subscription to a merge publication. This stored procedure is executed at the Subscriber on the subscription database.
Adds a new agent job used to schedule synchronization of a push subscription to a merge publication. This stored procedure is executed at the Publisher on the publication database. When configuring a Publisher with a remote Distributor, the values supplied for all , are sent to the Distributor as plain text.
Creates a push or pull merge subscription. This stored procedure is executed at the Publisher
Stores a new user-defined error message in an instance of the SQL Server Database Engine. error messages can be an integer between 50,001 and 2,147,483,647. The combination of must be unique; an error is returned if the ID already exists for the The severity level of the error. For more information about severities, see Database Engine error severities
Creates a snapshot or transactional publication. This stored procedure is executed at the Publisher on the publication database.
Creates the Snapshot Agent for the specified publication. This stored procedure is executed at the Publisher on the publication database. When configuring a Publisher with a remote Distributor, the values supplied for all , are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing t
Adds a pull subscription to a snapshot or transactional publication. This stored procedure is executed at the Subscriber on the database where the pull subscription is to be created. Server name can be specified as for a named instance.
Adds a new scheduled agent job used to synchronize a pull subscription to a transactional publication. This stored procedure is executed at the Subscriber on the subscription database.
Adds a new scheduled agent job used to synchronize a push subscription to a transactional publication. This stored procedure is executed at the Publisher on the publication database. When configuring a Publisher with a remote Distributor, the values supplied for all , are sent to the Distributor as plain text. You should encrypt the connection between the Publisher
Adds a Queue Reader agent for a given Distributor. This stored procedure is executed at the Distributor on the distribution database or at the Publisher on the publication database. The login for the Windows account under which the agent runs.
table at the Subscriber that is used to track article subscription information (queued, updating, and immediate updating with queued updating as failover). This stored procedure is executed at the Subscriber on the subscription database.
Adds a new remote login ID on the local server. This enables remote servers to connect and execute remote procedure calls.
Creates a new database role in the current database.
Adds a database user, database role, Windows login, or Windows group to a database role in The name of the database role in the current database. The security account being added to the role.
file) to all Subscribers of a publication. This stored procedure is executed at the Publisher on the publication database. The full path to the SQL script file. Indicates whether the Distribution Agent or Merge Agent should stop when an error is encountered during script processing. = the agent continues the script and ignores the error.
Defines the name of the local instance of SQL Server. When the computer hosting SQL Server is to inform the instance of the SQL Server Database Engine of the new computer name. This procedure must be executed on all instances of the Database Engine The instance name of the Database Engine can't be changed.
Adds a login, or security principal, as a member of a fixed server role.
Adds a new Subscriber to a Publisher, enabling it to receive publications. This stored procedure is executed at the Publisher on the publication database for snapshot and transactional publications; and for merge publications using a remote Distributor, this stored procedure is This stored procedure has been deprecated. You're no longer required to explicitly regist
Adds a schedule for the Distribution Agent and Merge Agent. This stored procedure is executed at the Publisher on any database. unique in the database, must not already exist, and can't be , and can be one of these values.
Adds a subscription to a publication and sets the Subscriber status. This stored procedure is executed at the Publisher on the publication database.
Creates triggers at the Subscriber used with all types of updatable subscriptions (immediate, queued, and immediate updating with queued updating as failover). This stored procedure is executed at the Subscriber on the subscription database.
inserts references into the merge tracking tables, for any rows in a source table that aren't currently included in the tracking tables. Use this option if you bulk- loaded a large amount of data using , which won't fire merge tracking triggers. This stored procedure is executed at the Publisher on the publication database.
The name of the alias data type. Alias data type names must follow the rules for must be unique in each database. The physical, or SQL Server supplied, data type on which the alias data type is based. , with no default, and can be one of these values: This feature will be removed in a future version of SQL Server.
Adds a backup device to an instance of SQL Server. , with no default, and can be one of the Hard disk file as a backup device. Any tape devices supported by Microsoft Windows. : Support for tape backup devices will be removed in a future version of SQL Server.
Adds a new user to the current database.
Adjusts the identity range on a publication and reallocates new ranges based on the threshold value on the publication. This stored procedure is executed at the Publisher on the publication The name of the publication in which new identity ranges are reallocated.
Alters the state of user-defined or system messages in an instance of the SQL Server Database Engine. User-defined messages can be viewed using the The error number of the message to alter from to indicate that the message is to be written to the Windows , the message is written to the Windows application message isn't always written to the Windows application log,
Applies a job to one or more target servers or to the target servers belonging to one or more The job identification number of the job to apply to the specified target servers or target server must be specified, but both can't be specified.
Changes the password of an application role in the current database.
Initiates a data validation request for the specified article. This stored procedure is executed at the Publisher on the publication database and at the Subscriber on the subscription database.
Used to specify columns included in an article to vertically filter data in a published table. This stored procedure is executed at the Publisher on the publication database.
Filters data that is published based on a table article. This stored procedure is executed at the Publisher on the publication database.
Creates the view that defines the published article when a table is filtered vertically or horizontally. This view is used as the filtered source of the schema and data for the destination tables. Only unsubscribed articles can be modified by this stored procedure. This stored procedure is executed at the Publisher on the publication database.
Attaches a database to a server. Don't attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure.
The job identification number of the job to which the schedule is added. must be specified, but both can't be specified.
Attaches a database that's only one data file to the current server. can't be used with multiple data files. Don't attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted
Attaches an existing subscription database to any Subscriber. This stored procedure is executed This feature is deprecated and will be removed in a future release. This feature shouldn't be used in new development work. For merge publications that are partitioned using parameterized filters, we recommend using the new features of partitioned snapshots, which simplif
Adds a user-defined audit event to A parameter defined by the user and recorded in the A parameter passed by user to indicate whether the event was successful or not. This value Arguments for extended stored procedures must be entered in the specific order as section.
Displays or changes the automatic statistics update option, index, a statistics object, a table, or an indexed view.
managed_backup.sp_backup_config_advanced (Transact-SQL) managed_backup.sp_backup_config_schedule (Transact-SQL)
2016 (13.x) and later versions Configures the SQL Server managed backup to Microsoft Azure basic settings for a specific database or for an instance of SQL Server. Enable or disable SQL Server managed backup to Microsoft Azure for the specified database. Required parameter when configuring SQL Server managed backup to Microsoft Azure for the first instanc
managed_backup.sp_backup_config_basic (Transact-SQL) managed_backup.sp_backup_config_schedule (Transact-SQL)
2016 (13.x) and later versions Requests SQL Server managed backup to Microsoft Azure to perform a backup of the specified Use this stored procedure to perform ad hoc backups for a database configured with SQL Server managed backup to Microsoft Azure. This prevents any break in the backup chain and SQL Server managed backup to Microsoft Azure processes are aware and the backup is stored
Binds a default to a column or to an alias data type.
condition_expression Is the condition or conditions that define the rule. A rule can be any expression valid in a WHERE clause and can include elements such as arithmetic operators, relational operators, and predicates (for example, IN, LIKE, BETWEEN). A rule cannot reference columns or other database objects. Built-in functions that do not reference database objects can be included. User-defined
Binds or unbinds a session to other sessions in the same instance of the SQL Server Database Engine. Binding sessions allows two or more sessions to participate in the same transaction and The token that identifies the transaction originally obtained by using This feature will be removed in a future version of SQL Server. Avoid using this feature in new development
Returns the complete path for the latest snapshot generated for a merge publication. This stored procedure is executed at the Publisher on the publication database.
Returns a result set in a readable version of the replicated commands stored in the distribution database, and is used as a diagnostic tool. This stored procedure is executed at the Distributor Specifies the lowest valued exact sequence number to return. Specifies the highest exact sequence number to return.
Returns the complete path for the latest snapshot generated for a publication. This stored procedure is executed at the Publisher on the publication database.
Verifies whether a transaction log backup can be applied to a SQL Server database. requires that the database is in the Indicates whether the transaction log can be applied to the database.
Returns the list of catalogs in the specified linked server. This is equivalent to databases in SQL
Creates a change data capture cleanup or capture job in the current database. Flag indicating whether the job should be started immediately after it's added. Maximum number of transactions to process in each scan cycle. If specified, the value must be a positive integer. is valid only for capture jobs.
Modifies the configuration of a change data capture cleanup or capture job in the current database. To view the current configuration of a job, query the Maximum number of transactions to process in each scan cycle. , which indicates no change for this parameter. If specified, the value must be a is valid only for capture jobs.
performs the following operations: @low_water_mark @capture_instance is left unchanged. However, if the current low watermark is greater than the low watermark value specified using the @low_water_mark parameter for the is thrown. The error message for Error 22957 is 2. Change table entries with values less than the low watermark are then deleted. The delete threshold is used to limit the number o
Disables change data capture (CDC) for the current database. Change data capture isn't available in every edition of SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and supported features of SQL Server 2022 disables change data capture for all tables in the database currently enabled.