SQL Maintenance Plan

The Maintenance Plan Wizard creates jobs for the Microsoft SQL Server Agent. This allows you to perform various database management tasks at specific intervals, e.g. Backups, database health checks or database statistic updates. This should be configured for all SQL databases, as this will prevent the transaction logs from becoming excessively large. Excessively large transaction logs can make the server inefficient and unstable.

Error solution regarding Service-Agent

When setting up a maintenance plan, you can receive this error message:

„’Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see… (Microsoft.SqlServer.Management.MaintenancePlanWizard)“

To solve this, you have to execute a new SQL query:

Create Maintenance Plan

• Launch Microsoft SQL Server Manager
• Connect to the server and instance that the databases were installed to
• Expand the folder called Management

• Right click the folder called Maintenance Plans and select Maintenance Plan Wizard
• On the Select Plan Properties screen change the Name of the Job and  do the following:

• Click Change in the scheduler section
  • Change the Frequency Settings to Occurs Daily and Recurs every 1 day
  • Set the Daily frequency to Occurs once at 12:00:00 AM or another time
  • Set the Duration start date to today
  • Set the end date to No end date
  • Confirm settings with OK

• On the Select Maintenance Tasks select following
  • History Cleanup Task
  • Back Up Database Task (Full)
  • Maintenance Cleanup Task 
• On the Select Maintenance Task Order, “select” the order that the tasks should execute
  • History Cleanup Task
    • Use the History Cleanup Task dialog to discard old historical information from tables in the msdb database. This task supports deleting backup and restore history, SQL Server Agent Job history, and maintenance plan history.
  • Back Up Database Task (Full)
    • Use the Back Up Database Task (Full) to add a backup task to the maintenance plan. Hereby you not only get a backup of the database, but also ensures that the transaction logs are deleted.
  • Maintenance Cleanup Task 
    • Use the Maintenance Cleanup Task to remove old files related to maintenance plans, including text reports created by maintenance plans and database backup files.

• On the Define Back Up Database (Full) Task, do the following
  • In the Database field, click the “drop-down arrow” to select the following databases to backup
    • Citrix Database
    • Citrix Logging Database
    • Citrix Monitoring Database
    • possible WEM Database
    • and so on
  • For the Back up to option, select Disk
  • For the Create a backup file for every database section, do the following:
    • Check the box to Create a sub-directory for each database
    • In the Folder field, enter the path of where the backup files will reside
    • For the Backup file extension field, enter .bak
  • Check the Verify backup integrity, to verify the backup file after it has completed the backup

• In the History Cleanup Task the following must be configured
  • Select all historical data to be deleted
  • Remove historical data older than 4 weeks

• On the Define Maintenance Cleanup Task, do the following
  • In the Folder field, enter or browse to the path where backup files are stored
  • In the File age section, check the box Delete files based on the age of the file at task run time and set the appropriate retention time you need to keep the backups

• Now continue to the report, check the settings there again and create the maintenance plan via Done
• The wizard now creates the maintenance plan file, which is visible under Management / Maintenance Plan
• Now check in SQL Management Studio if the job was also created. This can be seen through SQL Server Agent / Jobs.
• Expand the Jobs folder and the .job-file should be visible there