April 2006 - Posts

In BOL for SQL 2K we read, that SORTED_DATA and SORTED_DATA_REORG clauses that existed in SQL Server 6.x are no longer supported in SQL 2K. Don't believe that, they are not just supported but even used by Maintenance Plan. Add Optimization step to your plan and query sysprocesses table when it runs - you'll see something like:

dbcc dbreindex(N'[dbo].[MyTable]', N'', 90, sorted_data_reorg)

But don't get used to it - in SQL 2K5 SORTED_DATA and SORTED_DATA_REORG clauses don't exist.
In SQL 2000 there is nothing easier then that - just choose required options in the Complete Backup and/or Transaction Log Backup tabs. In SQL 2005 those who try to create Maintenance Plan using a wizard, usually ask aftewards, where did they miss the "delete old backups" option. The answer is: nowhere. There is no such option in a wizard. In order to delete old backups one should open existing plan (created with a wizard, for example) in SSIS window (open Management Studio, Management --> Maintenance Plans, right click on your plan --> Modify) and add "Maintenance Plan Cleanup" task.