System Stored Procedures
SQL Server system stored procedures for administrative tasks, configuration, security management, database maintenance, and replication. — Page 6 of 7.
Sets or clears a stored procedure for automatic execution. A stored procedure that is set to automatic execution runs every time an instance of SQL Server is started.
Initiates an article validation request for each article in the specified publication. This stored procedure is executed at the Publisher on the publication database. Specifies whether to return only the rowcount for the table.
Displays or changes publisher properties for non-SQL Server Publishers. This stored procedure is executed at the Distributor.
Removes data from the management data warehouse based on a retention policy. This procedure is executed daily by the SQL Server Agent job against the management data warehouse associated with the specified instance. You can use this stored procedure to perform an on-demand removal of data from the management data warehouse. The number of days to retain data in the m
Removes the history records for a job in the SQL Server Agent service.
2022 (16.x) and later versions SQL database in Microsoft Fabric argument defaults to the local replica (primary or secondary), but you can optionally specify a value matching a value in the to clear a hint for a different replica group. Arguments for extended stored procedures must be entered in the specific order as section. If the parameters are entered
Clears all queued (non-persisted) Query Store messages pending for the replica against which Query Store for secondary replicas is supported starting in SQL Server 2025 (17.x) and later versions, and in Azure SQL Database. For complete platform support, see Requires the ALTER permission on the database. The following example clears all queued (non-persisted) Query S
2016 (13.x) and later versions SQL database in Microsoft Fabric Flushes the in-memory portion of the Query Store data to disk. Query Store for readable secondaries executed on a secondary replica, that secondary replica's cache is forced to flush to the cache on the primary replica. This can accelerate the Query Store cache data being synced to the primar
2016 (13.x) and later versions SQL database in Microsoft Fabric Enables forcing a particular plan for a particular query in the Query Store. When a plan is forced for a particular query, every time SQL Server encounters the query, it tries to force the plan in the Query Optimizer.
2016 (13.x) and later versions SQL database in Microsoft Fabric Removes a single plan from the Query Store. The ID of the query plan to be removed. Requires the ALTER permission on the database. The following example returns information about the queries in the Query Store.
2016 (13.x) and later versions SQL database in Microsoft Fabric Removes the query and all associated plans and runtime stats from the Query Store. The ID of the query to be removed from the Query Store. Requires the ALTER permission on the database. Arguments for extended stored procedures must be entered in the specific order as section. If the parameter
2016 (13.x) and later versions Azure SQL Database SQL Managed Instance SQL database in Microsoft Fabric Clears the runtime stats for a specific query plan from the Query Store. The ID of the query plan to be cleared. , with no default. Requires the ALTER permission on the database. Arguments for extended stored procedures must be entered in the specific o
2022 (16.x) and later versions SQL database in Microsoft Fabric A character string of query options beginning with argument, the single quotes around individual hint names must be repeated. For example, Arguments for extended stored procedures must be entered in the specific order as section.
2016 (13.x) and later versions SQL database in Microsoft Fabric Enables unforcing a previously forced plan for a particular query in the Query Store. The ID of the query plan that will no longer be enforced.
Allows you to read the contents of the SQL Server or SQL Server Agent error log file and filter The integer value of the log you want to view. ), the one before previous is 2 ( The integer value for the product whose log you want to view. SQL Server Agent.
Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they're run. It does this by dropping the existing plan from the procedure cache, forcing a new plan to be created the next time that the procedure or trigger is run.
Specifies a redirected publisher for an existing publisher/database pair. If the publisher database belongs to an Always On availability group (AG), the redirected publisher is the AG listener name associated with the AG.
This stored procedure refreshes the remote monitor tables with the latest information from a given primary or secondary server for the specified log shipping agent. The procedure is invoked on the primary or secondary server. The primary ID for backup or the secondary ID for copy or restore.
2016 (13.x) and later Updates the Always Encrypted metadata for the parameters of the specified non-schema- bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database.
(dedicated SQL pool only) SQL database in Microsoft Fabric Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database.
Add subscriptions to new articles for all the existing Subscribers to an immediate-updating publication. This stored procedure is executed at the Publisher on the publication database. Specifies the publication for which to refresh subscriptions. Identified for informational purposes only. Not supported.
Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view identifier, but can only refer to views in the current database. (success) or a nonzero number (failure). should be run when changes are made to the objects
Replication allows user-defined custom stored procedures to replace one or more of the default procedures used in transactional replication. When a schema change is made to a replicated table, these stored procedures are re-created. registers a stored procedure or Transact-SQL script file that is executed when a schema change occurs to script out the definition for a new user-defined custom stored
Registers a business logic handler or a COM-based custom resolver that can be invoked during the merge replication synchronization process. This stored procedure is executed at the Specifies the friendly name for the custom business logic being registered. Specifies the CLSID value of the COM object that being registered. This parameter must be set to a valid CLSI
Marks a merge pull subscription for reinitialization the next time the Merge Agent runs. This stored procedure is executed at the Subscriber in the subscription database.
Marks a merge subscription for reinitialization the next time the Merge Agent runs. This stored procedure is executed at the Publisher in the publication database.
Marks a transactional pull or anonymous subscription for reinitialization the next time the Distribution Agent runs. This stored procedure is executed at the Subscriber on the pull The name of the Publisher database. subscriptions for reinitialization.
Marks the subscription for reinitialization. This stored procedure is executed at the Publisher .
Releases a lock on an application resource. A lock resource name specified by the client application. is binary-compared, thus is case-sensitive regardless of the collation settings of the current database. The application must ensure that the resource is unique. The specified name is hashed internally into a value that can be stored
Displays or changes options for a remote login defined on the local server running SQL Server. This stored procedure returns the following error message: Linked servers (Database Engine) This feature will be removed in a future version of SQL Server.
warehouse database. The procedure must be executed in the context of the management data view shows the registered collector types that can upload data to the management data warehouse. The GUID for the collector type. (with EXECUTE permission) fixed database role.
Removes the specified job from the given target servers or target server groups. The job identification number of the job from which to remove the specified target servers or must be specified, but both can't be specified.
This stored procedure removes all replication objects on the publication database on the Publisher instance of SQL Server, or on the subscription database on the Subscriber instance of in the appropriate database, or, if the execution is in the context of another database on the same instance, specify the database where the replication objects should be removed.
Removes publishing metadata belonging to a specific publication at the Distributor. This stored procedure is executed at the Distributor on the distribution database.
SQL analytics endpoint in Microsoft Fabric Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type. in SQL Server and Azure SQL Database: (preview) in Azure Synapse Analytics: Some system objects and Transact-SQL syntax a
Changes the name of a database. The current name of the database. follow the rules for identifiers. (success) or a nonzero number (failure). 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. instead.
Adds a column to an existing published table article. Allows the new column to be added to all publishers that publish this table, or just add the column to a specific publication that publishes the table. This stored procedure is executed at the Publisher on the publication The name of the table article that contains the new column to add.
Returns the commands for transactions marked for replication. This stored procedure is executed at the Publisher on the publication database. The number of transactions to return information about. which specifies the next transaction waiting for distribution. procedure should be run only to troubleshoot problems with replication.
Returns replication statistics about latency, throughput, and transaction count for each published database. This stored procedure is executed at the Publisher on any database.
Updates the record that identifies the last distributed transaction of the server. This stored procedure is executed at the Publisher on the publication database. The log sequence number (LSN) of the first record for the last distributed transaction of the manually, you can invalidate the order and consistency of delivered transactions.
Drops a column from an existing table article that was published. This stored procedure is executed at the Publisher on the publication database.
Flushes the article cache. This stored procedure is executed at the Publisher on the publication is used in transactional replication. Article definitions are stored in the cache for efficiency. is used by other replication stored procedures whenever an article definition is modified or dropped. You shouldn't have to execute this procedure
Checks each distribution database for replication agents that are running but haven't logged history within the specified heartbeat interval. This stored procedure is executed at the The maximum number of minutes that an agent can go without logging a progress message. raises error 14151 for each agent it detects as suspect.
Sets a replication database option for the specified database. This stored procedure is executed at the Publisher or Subscriber on any database. The database for which the replication database option is being set. The replication database option to enable or disable. Database can be used for merge publications. Database can be used for other types of publications.
Changes the monitoring threshold metric for a publication. This stored procedure, which is used to monitor replication, is executed at the Distributor on the distribution database.
Returns information on past sessions for a given replication Merge Agent, with one row returned for each session that matches the filtering criterion. This stored procedure, which is used to monitor merge replication, is executed at the Distributor on the distribution database or at the Subscriber on the subscription database.
Returns detailed, article-level information about a specific replication Merge Agent session, which is used to monitor merge replication. The result set includes a detail row for each article that was synchronized during the session. It also includes a row that represents the session initialization and rows that summarize both the upload and download phases of the session.
Returns current status information for one or more publications at a Publisher. This stored procedure, which is used to monitor replication, is executed at the Distributor on the The name of the Publisher the status of which is being monitored. , information is returned for all Publishers that use the Distributor.
Returns the threshold metrics set for a monitored publication. This stored procedure, which is used to monitor replication, is executed at the Distributor on the distribution database.
Returns current status information for one or more Publishers associated with a Distributor. This stored procedure, which is used to monitor replication, is executed at the Distributor on The name of the Publisher the status of which is being monitored. , information is returned for all Publishers that use the Distributor. Identified for informational purposes only.
Returns current status information for subscriptions belonging to one or more publications at the Publisher and returns one row for each returned subscription. This stored procedure, which is used to monitor replication, is executed at the Distributor on the distribution database.
Returns information on the number of pending commands for a subscription to a transactional publication and a rough estimate of how much time it takes to process them. This stored procedure returns one row for each returned subscription. This stored procedure, which is used to monitor replication, is executed at the Distributor on the distribution database.
Lists the queue messages from a SQL Server queue or Microsoft Message Queuing for queued updating subscriptions to a specified publication. If SQL Server queues are used, this stored procedure is executed at the Subscriber on the subscription database. If Message Queuing is used, this stored procedure is executed at the Distributor on the distribution database. is u
Used by transactional replication during backup and restore so that the replicated data at the Distributor is synchronized with data at the Publisher. This stored procedure is executed at the Publisher on the publication database. is used when the highest log sequence number (LSN) value at the Distributor doesn't match the highest LSN value at the Publisher. fixed d
Used to invoke loopback detection and handling in bidirectional transactional replication. This stored procedure is executed at the Publisher on the publication database.
Returns the commands for transactions marked for replication in readable format.
Returns a result set of all the transactions in the publication database transaction log that are marked for replication but aren't marked as distributed. This stored procedure is executed at the Publisher on a publication database. returns information about the publication database from which it's executed, allowing you to view transactions currently not distribute
When executed from a node in a peer-to-peer topology, this procedure requests a response from every other node in the topology. By executing this procedure and reviewing the corresponding responses, you can guarantee that all previous commands are delivered to the responding nodes. This stored procedure is executed at the requesting node on any database.
MSpeer_topologyresponse system table with information about a peer-to-peer transactional replication topology. Execute sp_gettopologyinfo to obtain information from the table in XML format.
Clears the Microsoft Distributed Transaction Coordinator (MSDTC) log. or have CONTROL SERVER permissions.
Resets the snapshot delivery process for a pull subscription so that snapshot delivery can be restarted. Executed at the Subscriber on the subscription database. Specifies the amount of information returned. means that an error is returned if the necessary locks can't be obtained on the means that no error is returned. Whether to drop or truncate the table containin
Resets the status of a suspect database.
Removes replication settings if restoring a database to the non-originating server, database, or system that is otherwise not capable of running replication processes. When restoring a replicated database to a server or database other than the one where the backup was taken, replication settings can't be preserved.
This stored procedure is used to update identity range assignments. It ensures that automatic identity range management functions properly after a Publisher is restored from a backup. This stored procedure is executed at the Publisher on the publication database.
Resynchronizes all multiserver jobs in the specified target server.
Resynchronizes a merge subscription to a known validation state that you specify. You can force convergence or synchronize the subscription database to a specific point in time, such as the last time there was a successful validation, or to a specified date. The snapshot isn't reapplied when resynchronizing a subscription using this method. This stored procedure isn't used for snapshot replication
Removes access to a proxy for a security principal.
Revokes access to a subsystem from a proxy. The proxy identification number of the proxy to revoke access from. must be specified, but both can't be specified.
Removes the login from a publications access list. This stored procedure is executed at the Publisher on the publication database.
Removes a database user from the current database.
Removes the login entries from SQL Server for a Windows user or group created by using The name of the Windows user or group. , with no default.
2016 (13.x) and later - Windows only Generates a predicted value for a given input consisting of a machine learning model stored in a binary format in a SQL Server database. Provides scoring on R and Python machine learning models in near real time. stored procedure written in C++, and is optimized specifically for scoring operations. The model must be created using R or Python. However
Modifies and displays information on the schema that is excluded when listing Oracle tables The name of the non-SQL Server Publisher. The action to be taken on this schema. Adds the specified schema to the list of schemas that aren't eligible for publication.
Generates a script that contains the calls to be applied at Subscribers for updatable subscriptions. There's one call for each article in the publication. The generated script also contains the table that is needed to process queued publications. This stored procedure is executed at the Publisher on the publication database.
statement that creates a dynamic update stored procedure. statement within the custom stored procedure is built dynamically based on the syntax that indicates which columns to change. Use this stored procedure if the number of indexes on the subscribing table is growing and the number of columns being changed is small. This stored procedure is run at the Publisher on the publication database. Tran
procedures for all table articles in a publication in which the autogenerate custom procedure schema option is enabled.
Generates script for creating a conflict table on the Subscriber for a given queued subscription article. The script that is generated is executed at the Subscriber on the subscription database. This stored procedure is executed at the Publisher on the publication database.
Sends an e-mail message to the specified recipients. The message might include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue, of the message.
Returns a range of sequence values from a sequence object. The sequence object generates and issues the number of values requested and provides the application with metadata related For a more information about sequence numbers, see The name of the sequence object. The schema is optional. The number of values to fetch from the sequen
Captures diagnostic data and health information about SQL Server to detect potential failures. The procedure runs in repeat mode and sends results periodically. It can be invoked from either a regular connection, or a Indicates the time interval at which the stored procedure runs repeatedly to send health . The valid parameter values are . The stored procedure has t
Returns a list of attribute names and matching values for SQL Server, the database gateway, or the underlying data source. Used in ODBC only. The integer ID of the attribute. Current setting of the attribute.
Sets server options for remote servers and linked servers.
2016 (13.x) and later versions Sets the value of the specified Smart Admin system parameter. The available parameters are related to SQL Server managed backup to Microsoft Azure. These parameters are used to set the email notifications, enable specific extended events, and enable user set policy based management policies. You must specify the parameter name and the Transact-SQL syntax c
2016 (13.x) and later versions SQL analytics endpoint in Microsoft SQL database in Microsoft Fabric Sets a key-value pair in the session context. with no default. The maximum key size is 128 bytes. The value for the specified key. frees the memory. The maximum size is 8,000 bytes.
Activates the permissions associated with an application role in the current database.
Marks an existing data type mapping between SQL Server and a non-SQL Server database management system (DBMS) as the default. This stored procedure is executed at the Identifies an existing data type mapping. , then the remaining parameters aren't required.
to their actual network computer names for remote instances of SQL Server. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing SQL Server identifiers that The name of the remote server as referenced in user-coded remote stored procedure call , with no default. Exactly one row in The network
Allows you to set the failover operation mode for subscriptions enabled for immediate updating, with queued updating as failover. This stored procedure is executed at the Subscriber on the subscription database. For more information about failover modes, see Updatable Subscriptions - For Transactional Replication , with no default. The publication must The name of t
Used during troubleshooting to specify the last delivered transaction using the log sequence number (LSN), allowing the Distribution Agent to begin delivering at the next transaction. After it restarts, the Distribution Agent returns transactions greater than this watermark (LSN) from the Distribution database cache (msrepl_commands).
triggers that are fired first or last. The triggers that are fired between the first and last triggers are executed in undefined order.
Returns a result set showing the changes that are waiting to be replicated. This stored procedure is executed at the Publisher on the publication database and at the Subscriber on The name of the server where the replicated changes are applied. This procedure provides an approximation of the number of changes and the rows that are involved in those changes.
Displays information about a row in a table that is being used as an article in merge replication. This stored procedure is executed at the Publisher on the publication database. is useful to differentiate tables if a database contains multiple tables with the same name, but each table has a different owner.
system stored procedure displays either: the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database the disk space reserved and used by the whole database For Azure Synapse Analytics and Analytics Platform System (PDW), upon the ordinal position of parameters.
Returns the optimal set of columns that uniquely identify a row in the table. Also returns columns automatically updated when any value in the row is updated by a transaction.
Returns column information for a single stored procedure or user-defined function in the The name of the procedure used to return catalog information. , which means all tables in the current database. Wildcard pattern matching is supported.
Displays the permissions of a fixed server role.
Instructs SQL Server Agent to execute a job immediately. must be specified, but both can't be specified. The identification number of the job to start. must be specified, but both can't be specified. Identified for informational purposes only. Not supported.
Used to start the Snapshot Agent job that generates the initial snapshot for a publication. This stored procedure is executed at the Publisher on the publication database.
Returns a list of all indexes and statistics on a specified table or indexed view. Specifies the table used to return catalog information. default. Wildcard pattern matching isn't supported.
Instructs SQL Server Agent to stop the execution of a job. The identification number of the job to stop.