Maintenance Plans

Defnition

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies. The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility

Types of Maintenance tasks

• Check Database Integrity
• Shrink database
• Reorganize index
• Rebuild index
• Update statistics
• Clean up History
• Execute Sql Server Agent Job
• Back up Database(Full)
• Back up Database(Differential)
• Back up Database(Transaction Log)
• Maintenance clean up task

Check Database Integrity

• Database Integrity task checks the allocation and structural integrity of all the objects in the specified database. The task can check a single database or multiple databases
• The reasoning behind this technique is that for anything bad to happen on a system something on that system must change, so then detecting when something bad has happened simply requires detecting changes that shouldn't have happened..
  DBCC CHECKDB(AdventureWorks)
  GO

Reorganize & Rebuild index

• The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data
• Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file
• Heavily fragmented indexes can degrade query performance and cause your application to respond slowly
• Index reorganize and Index rebuild help addressing Index fragmentation issues

Update statistics

• It ensures the query optimizer has up-to date information about the distribution of data values in the table. This allows the query optimizer to make better judgement about data access strategies
• While executing a Sql query, It will run thousands of strategies and execution plans like how many rows that table is having
• Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. Its not recommend updating statistics too frequently because it will mis- lead the execution plan
USE AdventureWorks2012;
GO
EXEC sp_updatestats;

Database Mail

• Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users or alerts about SQL Jobs to Admins. The messages can contain query results, and can also include files from any resource on your network
  Configuration:
  1. Create a Profile
  2. Create An Account
  3. Send a Test Email
  4. Enable Database Mail on the SQL Server Agent
  Gmail Configuration:
  1. SMTP Server: smtp.gmail.com
  2. port number: 587

Database transfer

Database files are transferred from instance to another using:

• Database backup
• Detach-and-attach
• Copy database wizard
• Generate script task

Read More ...