LiteSpeed for SQL Server 8.9.8 - User Guide

SmartCleanup

Smart Cleanup Policies provides a convenient way to remove old LiteSpeed backups. It removes full and differential backup files and optionally transaction log backups based on a user-defined period. LiteSpeed will ignore copy-only backups except on secondary replicas in AlwaysOn Availability groups, in which case it will allow deletions.

Smart Cleanup Policies is backup set aware and will never remove a full backup that is needed by a differential backup that is not being deleted.

The backup retention will never delete:

• The backup files, if there are mixed backups in the same backup file. For example, if a user performs a backup of AdventureWorks and Pubs into the same mybackups.bak backup file.
• The full backup, if there are associated differential or t-log backups in the backup set that are not eligible for cleanup.

• File/FileGroup backups
• File/FileGroup differential backups
• Partial backups
• Partial differential backups

• Files that have the filesystem archive bit set (if that option is selected)

NOTE: Fast Compression does not raise errors if it detects a missing backup from a backup set that was removed via the cleanup process.

The diagram below shows how the LiteSpeed components communicate to SQL Server to check if backups are eligible for cleanup and delete them each time Smart Cleanup Policies runs.

To configure Smart Cleanup Policies in Wizards

• Open existing or create a new backup template. For more information, see Create Backup Templates.
• Open or create the Fast Compression Database task in a maintenance plan. For more information, see About Creating Maintenance Plans.
• Run the Backup wizard and select the Fast Compression backup type. For more information, see Back Up Databases.

To run Smart Cleanup Policies manually, do one of the following:

• Use the xp_slsSmartCleanup extended stored procedure. For more information, see xp_slsSmartCleanup.
• Create a Smart Cleanup Backup Files maintenance plan task. For more information, see Smart Cleanup Backup Files.
• Run slsSmartCleanup.exe with the appropriate arguments from the command-line. For more information, see SmartCleanup Command-Line Arguments.

Related Topics

SmartCleanup Command-Line Arguments

xp_slsSmartCleanup

Accepted Variables

LiteSpeed automatically substitutes variables anywhere you need to specify a backup file name, comment, or description in the LiteSpeed UI Console, from the command-line and when using extended stored procedures.

ALL variables are supported for both files and folders.

Click here for information about the command-line and extended stored procedure arguments that accept LiteSpeed variables.

LiteSpeed accepts the following variables:

Examples

1. Specify backup destination:

   \\Storage\Backup\%SERVER%\%DATABASENAME%_%TYPE%_%DATETIME%.bak

2. Back up the Northwind database with the specified backup set name and description.

   EXEC master.dbo.xp_backup_database
   @database='MyDB'
   , @filename='C:\MSSQL\Backup\%D.BAK'
   , @init= 1
   , @backupname = '%D_%w'
   , @desc = '%T Backup of %D'

3. Restore the Northwind database from the backup device c:\temp\Northwind.bak from the command-line interface.

   sqllitespeed.exe -R Database -D Northwind -F "C:\temp\%D.bak" -W REPLACE

Automate Maintenance Tasks

Maintenance plans help you automate routine database maintenance tasks, such as backing up databases, updating statistics, and rebuilding indexes to run on a specific day and time. This ensures that your databases are stable and perform at optimal levels.

Legacy and SSIS Maintenance Plans

4.x maintenance plans are considered legacy plans.

In SQL Server 2005 or later, maintenance plans create SSIS packages that run as agent jobs.

To take advantage of Integration Services (SSIS) in maintenance plans, Integration Services must be installed on any server instance where you want to create maintenance plans. Integration Services may be a part of the Client Tools or a part of SQL Server.

NOTES:

• Express edition of SQL Server does not support maintenance plans.
• LiteSpeed also creates a legacy maintenance plan when:
  • SSIS components are not installed on server.
  • SQL Server 2005 SSIS components version is lower than 9.00.3042 (Service Pack 2).

Native SQL Server and LiteSpeed Maintenance Plans

You can manage both native SQL Server and LiteSpeed maintenance plans in the LiteSpeed UI Console. LiteSpeed maintenance plans have the extended task set and a larger number of advanced options, including the high-performance compression technology LiteSpeed uses to create backups.

You need to have LiteSpeed installed on every server instance where you want to create LiteSpeed maintenance plans. Otherwise, only native SQL Server functionality is available.

Convert to LiteSpeed

You can convert native SQL Server maintenance plans to LiteSpeed maintenance plans. Using this option, you can also convert previous LiteSpeed maintenance plans to the latest version. Upgrade LiteSpeed Maintenance Plans

To convert a maintenance plan

1. Select Maintenance Plans in the Navigation pane (CTRL+4).
2. Right-click a maintenance plan and select Convert to LiteSpeed.

Related Topics

About Creating Maintenance Plans

About Creating Maintenance Plans

Use the Design pane to create a workflow of database maintenance tasks. Tasks can execute independent of another task's status, or can be dependent on another task's completion before they can begin execution.

You can add additional subplans in a maintenance plan to group related tasks or to schedule tasks to execute at different times.

To create a maintenance plan

1. Select Maintenance Plans in the Navigation pane (CTRL+4).
2. Right-click a server instance and select Create New Maintenance Plan in the Maintenance Plan pane.

3. Drag tasks to the Design pane to add them to the subplan. Double-click a task to specify its properties.

   Tip: If you need to create several similar plans, you can simply copy existing plans or subplans and then make the necessary edits. Copy Maintenance Plans

   Tip: For legacy maintenance plans only:

   • You cannot have more than one task of the same type in a subplan. To add a duplicate task, create a new subplan and add the task.  
   • You cannot have both a Fast Compression Backup task and Back Up Database task added to the same subplan.
   • Any tasks in the subplan must use the same list of databases. If you select a different database in a task, LiteSpeed prompts you to apply the database change to the entire subplan.

   Task	Add this task to...	Available for legacy plans
   Fast Compression Backup	Perform full or differential backups using the Fast Compression technology. NOTE: Fast Compression is only available with LiteSpeed 5.1 or later; Enterprise license.  Back Up Databases Using Maintenance Plans	With LiteSpeed only
   Back Up Database	Perform full, differential, or transaction log backups; with or without encryption. You can back up databases on multiple servers by adding a separate backup task to the maintenance plan for each server. Back Up Databases Using Maintenance Plans	Yes
   Smart Clean Up Backup Files	Remove backup files with SmartCleanup Smart Cleanup Backup Files	With LiteSpeed only
   Check Database Integrity	Validate the following: Disk space allocation Page and structural integrity for tables and indexed views Catalog consistency Contents of indexed views Service Broker data	Yes
   Reorganize Index	Defragment and compact existing indexes to improve performance.	With LiteSpeed only
   Execute Job	Execute an existing SQL Server Agent job.	No
   Clean Up Maintenance Plans	Remove obsolete backup files and reports created by a maintenance plan.   Clean Up Maintenance Plans	With LiteSpeed only
   Clean Up History	Remove historical data from the msdb database and LiteSpeed Local and Central repositories for the following: Back up and restore history SQL Server Agent jobs Maintenance plans LiteSpeed activity NOTE: LiteSpeed activity is removed from the Central repository, only if it is located on the same server, where the Local repository resides. Any information for deleted databases Log shipping history Status history (Job, DTS, Maint Plans)	With LiteSpeed only
   Notify Operator	Send an email notification to one or more existing operators. You can use the notification profiles only if they are already configured within a SQL Server instance. TIP: Variables defined in the maintenance plan can be used to format the Subject Line. There is also help information inside the task that lists the available Subject Line variables. NOTE: The SQL Server Agent must be configured to send email using Database Mail. Review the following for additional information: http://msdn.microsoft.com/en-us/library/ms189635.aspx http://technet.microsoft.com/en-us/sqlserver/dd939167.aspx	With LiteSpeed only
   Rebuild Index	Drop and recreate an index to improve performance.	Yes
   Shrink Database	Reduce the size of data and log files in a database that grow beyond a specified size.	Yes
   Execute T-SQL	Execute statements or batches on one or more databases. NOTE: SSIS maintenance plans support only the Transact-SQL command type.	Yes
   Update Statistics	Update column and index statistics.	Yes

4. (SSIS Plans only) Right-click a task to execute first and select Add Constraint to draw a line to the dependent task.

   Tips:

   • You can add multiple constraints for a single task, and can execute those tasks concurrently or based on the outcome of the previous task.  
   • To use edit the constraint or use an expression to evaluate precedence, right-click the constraint line and select Edit.

   NOTE: Constraints are not available for legacy plans. Legacy plan tasks are executed in the following sequence:
   1. Clean Up History
   2. Check Database Integrity
   3. Rebuild Index
   4. Shrink Database
   5. Update Statistics
   6. Reorganize Index
   7. Fast Compression Backup or Back Up Database
   8. Clean Up Maintenance Plans
   9. Notify Operator
   10. Execute T-SQL

5. Click to enter or select a schedule for executing the current subplan.
6. Click and repeat steps 2, 3 and 4 for additional subplans you want to add.

7. (Optional) Set reporting options.

   Reporting and Logging	Click to set the reporting and logging options. Reporting and Logging in Maintenance Plans
   Notifications	Click to notify an operator of job (subplan) status when a job fails, succeeds, or completes.

Tips:

• To manually execute, edit, or delete a maintenance plan, right-click the plan in the Maintenance Plan pane and select an option.
• To change the plan owner, open the plan in the Designpane and select the owner from the drop-down list in the upper-right corner of the pane. Note that the plan owner is only responsible for creating and editing plans. The account that executes packages is the SQL Agent service account (or a proxy account). Privilege and Grant Requirements For more information, please refer to the "Privilege and Grant Requirements" section of the LiteSpeed Installation Guide.
• To remove a subplan, select it and click on the toolbar in the maintenance plan designer.
• To disable a subplan, double-click the subplan in the Design pane.
• To define any connections you want to use for tasks in a maintenance plan, click . Once you add a connection, you can select it from the Connection drop-down list in any task. You can also define a connection at the task level that applies to other tasks and subplans. For legacy plans, you can add only one connection for remote logging and you must use Windows Authentication.

Related Topics

Copy Maintenance Plans

Troubleshoot Maintenance Plans