xp_sqlmaint (Transact-SQL)
Applies to:
SQL Server
Calls the sqlmaint utility with a string that contains sqlmaint options (also known as switches). The sqlmaint utility performs a set of maintenance operations on one or more databases.
Note
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.
Transact-SQL syntax conventions
Syntax
xp_sqlmaint 'switch_string'
Arguments
'switch_string'
A string containing the sqlmaint utility switches. The switches and their values must be separated by a space.
The -?
switch isn't valid for xp_sqlmaint
.
Return code values
None. Returns an error if the sqlmaint utility fails.
Remarks
If this procedure is called by a user logged on with SQL Server Authentication, the -U "<login_id>"
and -P "<password>"
switches are prepended to switch_string before execution. If the user is logged on with Windows Authentication, switch_string is passed without change to sqlmaint.
Permissions
Requires membership in the sysadmin fixed server role, or execute permission directly on this stored procedure.
Examples
In the following example, xp_sqlmaint
calls sqlmaint to perform integrity checks, create a report file, and update msdb.dbo.sysdbmaintplan_history
.
EXEC xp_sqlmaint '-D AdventureWorks2022 -PlanID 02A52657-D546-11D1-9D8A-00A0C9054212
-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\DBMaintPlan2.txt" -WriteHistory -CkDB -CkAl';
Here is the result set.
The command(s) executed successfully.