Skip to content

xp-sqlmaint-transact-sql.md

Latest commit

 

History

History
76 lines (52 loc) · 2.44 KB

xp-sqlmaint-transact-sql.md

File metadata and controls

76 lines (52 loc) · 2.44 KB
title xp_sqlmaint (Transact-SQL)
description Calls the sqlmaint utility with a string that contains sqlmaint options.
author MashaMSFT
ms.author mathoma
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
xp_sqlmaint
xp_sqlmaint_TSQL
helpviewer_keywords
xp_sqlmaint
dev_langs
TSQL

xp_sqlmaint (Transact-SQL)

[!INCLUDE 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

[!INCLUDE ssNoteDepFutureAvoid]

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

xp_sqlmaint 'switch_string'

Arguments

[!INCLUDE extended-stored-procedures]

'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.

EXECUTE xp_sqlmaint '-D AdventureWorks2022 -PlanID 02A52657-D546-11D1-9D8A-00A0C9054212
   -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\DBMaintPlan2.txt" -WriteHistory -CkDB -CkAl';

[!INCLUDE ssResult]

The command(s) executed successfully.

Related content