Maintenance Plans Keith Binford Nebiyu Sorri Maintenance Plans
Maintenance Plans Keith Binford Nebiyu Sorri
Maintenance Plans Most plans have at least four steps: • Database consistency checking • Database backup and backup retention policy • Index maintenance • Statistics maintenance
Consistency Checks Preforming a database consistency check against a Microsoft SQL Server database involves validating the logical and physical integrity of all database objects. • Schema • Data allocations • Page and storage consistency
T-SQL Consistency Check DBCC CHECKDB [ [ ( database_name | database_id | 0 [ , NOINDEX | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) ] [ WITH { [ ALL_ERRORMSGS ] [ , EXTENDED_LOGICAL_CHECKS ] [ , NO_INFOMSGS ] [ , TABLOCK ] [ , ESTIMATEONLY ] [ , { PHYSICAL_ONLY | DATA_PURITY } ] ]
Consistency Checks Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.
Consistency Checks -- Check the current database. DBCC CHECKDB; GO -- Check the Adventure. Works 2012 database without nonclustered indexes. DBCC CHECKDB (Adventure. Works 2012, NOINDEX); GO
Creating Maintenance Plans • T-SQL Statement • Maintenance Plan Wizard
T-SQL Backup Database BACKUP DATABASE [Adventure. Works 2012] TO DISK = N'C: Program FilesMicrosoft SQL ServerMSSQL 11. MSSQLSERVERMSSQLBackupAdventure. Work s 2012_backup_2013_07_23_123130_6012101. bak' WITH NOFORMAT, NOINIT, NAME = N'Adventure. Works 2012_backup_2013_07_23_123130_6012101', SKIP, REWIND, NOUNLOAD, STATS = 10
USE msdb; GO -- Adds a new job, executed by the SQL Server Agent service, called "History. Cleanup. Task_1". EXEC dbo. sp_add_job @job_name = N'History. Cleanup. Task_1', @enabled = 1, @description = N'Clean up old task history' ; GO -- Adds a job step for reorganizing all of the indexes in the Human. Resources. Employee table to the History. Cleanup. Task_1 job. EXEC dbo. sp_add_jobstep @job_name = N'History. Cleanup. Task_1', @step_name = N'Reorganize all indexes on Human. Resources. Employee table', @subsystem = N'TSQL', @command = N'USE Adventure. Works 2012 GO ALTER INDEX AK_Employee_Login. ID ON Human. Resources. Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE Adventure. Works 2012 GO ALTER INDEX AK_Employee_National. IDNumber ON Human. Resources. Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE Adventure. Works 2012 GO ALTER INDEX AK_Employee_rowguid ON Human. Resources. Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE Adventure. Works 2012 GO ALTER INDEX IX_Employee_Organization. Level_Organization. Node ON Human. Resources. Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE Adventure. Works 2012 GO ALTER INDEX IX_Employee_Organization. Node ON Human. Resources. Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE Adventure. Works 2012 GO ALTER INDEX PK_Employee_Business. Entity. ID ON Human. Resources. Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO ', @retry_attempts = 5, @retry_interval = 5 ; GO -- Creates a schedule named Run. Once that executes every day when the time on the server is 23: 00. EXEC dbo. sp_add_schedule @schedule_name = N'Run. Once', @freq_type = 4, @freq_interval = 1, @active_start_time = 233000 ; GO -- Attaches the Run. Once schedule to the job History. Cleanup. Task_1. EXEC sp_attach_schedule @job_name = N'History. Cleanup. Task_1' @schedule_name = N'Run. Once' ; GO
Creating Maintenance Plans Maintenance Plan Wizard
Creating Maintenance Plans Maintenance Plan Wizard
References: Microsoft SQL Server 2012 by Patrick Le. Blanc MSDN Database Consistency Check http: //msdn. microsoft. com/en-us/library/ms 176064. aspx MSDN Creating a Maintenance Plan http: //msdn. microsoft. com/en-us/library/ms 191002. aspx
END
- Slides: 13