1 / 13

Maintenance Plans

Maintenance Plans. Keith Binford Nebiyu Sorri . Maintenance Plans. Most plans have at least four s teps: Database consistency checking Database backup and backup retention policy Index maintenance Statistics maintenance. Consistency Checks.

tanek
Download Presentation

Maintenance Plans

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Maintenance Plans Keith Binford Nebiyu Sorri

  2. Maintenance Plans Most plans have at least four steps: • Database consistency checking • Database backup and backup retention policy • Index maintenance • Statistics maintenance

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

  4. 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 } ]          } ] ]

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

  6. Consistency Checks -- Check the current database. DBCC CHECKDB; GO -- Check the AdventureWorks2012 database without nonclustered indexes. DBCC CHECKDB (AdventureWorks2012, NOINDEX); GO

  7. Creating Maintenance Plans • T-SQL Statement • Maintenance Plan Wizard

  8. T-SQL Backup Database BACKUP DATABASE [AdventureWorks2012] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012_backup_2013_07_23_123130_6012101.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012_backup_2013_07_23_123130_6012101', SKIP, REWIND, NOUNLOAD, STATS = 10

  9. USE msdb; GO -- Adds a new job, executed by the SQL Server Agent service, called "HistoryCleanupTask_1". EXEC dbo.sp_add_job @job_name = N'HistoryCleanupTask_1', @enabled = 1, @description = N'Clean up old task history' ; GO -- Adds a job step for reorganizing all of the indexes in the HumanResources.Employee table to the HistoryCleanupTask_1 job. EXEC dbo.sp_add_jobstep @job_name = N'HistoryCleanupTask_1', @step_name = N'Reorganize all indexes on HumanResources.Employee table', @subsystem = N'TSQL', @command = N'USE AdventureWorks2012 GO ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE AdventureWorks2012 GO ALTER INDEX AK_Employee_NationalIDNumber ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE AdventureWorks2012 GO ALTER INDEX AK_Employee_rowguid ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE AdventureWorks2012 GO ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE AdventureWorks2012 GO ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO USE AdventureWorks2012 GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON ) GO ', @retry_attempts = 5, @retry_interval = 5 ; GO -- Creates a schedule named RunOnce that executes every day when the time on the server is 23:00. EXEC dbo.sp_add_schedule @schedule_name = N'RunOnce', @freq_type = 4, @freq_interval = 1, @active_start_time = 233000 ; GO -- Attaches the RunOnce schedule to the job HistoryCleanupTask_1. EXEC sp_attach_schedule @job_name = N'HistoryCleanupTask_1' @schedule_name = N'RunOnce' ; GO

  10. Creating Maintenance PlansMaintenance Plan Wizard

  11. Creating Maintenance PlansMaintenance Plan Wizard

  12. References: Microsoft SQL Server 2012 by Patrick LeBlanc MSDN Database Consistency Check http://msdn.microsoft.com/en-us/library/ms176064.aspx MSDN Creating a Maintenance Plan http://msdn.microsoft.com/en-us/library/ms191002.aspx

  13. END

More Related