Planes de mantenimiento en SQL Server

Planes de mantenimiento en SQL Server

Los Planes de mantenimiento de SQL Server son flujos de trabajo creados por el administrador del SQL Server para asegurar la optimización de la base de datos, estos flujos de trabajo son tareas que pueden crear copias de seguridad, reducir la base de datos, reorganizar los índices, etc.

Características de los planes de mantenimiento

  • Al crear un Plan de mantenimiento se crean de flujos de trabajo con diferentes tareas de mantenimiento las que se describen líneas abajo.
  • En los Planes de mantenimiento se pueden crear scripts Transact-SQL personalizados.
  • Los planes de mantenimiento que trabajen con diferentes bases de datos se pueden organizar en sub planes.
  • Los Sub planes se pueden programar para ejecutarse a horas diferentes, lo que es recomendable para evitar congestionamiento de red.
  • Los planes de mantenimiento se pueden crear usando cualquier tipo de autenticación, ya sea Windows y la autenticación de SQL Server. 
  • Cada tarea se configura de manera individual y luego se asigna un flujo de trabajo.

Tareas del Plan de mantenimiento

Las tareas del Plan de mantenimiento se describen en la siguiente tabla.

Tarea Descripción  de la tarea
Tarea Copia de seguridad de la base de datosAl usar la tarea, permite programar distintos tipos de copias de seguridad de bases de datos de SQL Server.
Tarea Comprobar la integridad de la base de datos Al usar la tarea, permite comprobar la integridad de la base de datos.
Tarea Ejecutar trabajo del Agente SQL ServerEjecuta trabajos del Agente SQL Server.
Tarea Ejecutar instrucción T- SQLEjecuta instrucciones de Transact-SQL, esta tarea es interesante cuando se tienen que ejecutar instrucciones que no están incluidas en otras tareas.
Tarea Limpieza de historialElimina el historial de la base de datos del sistema msdb de SQL Server.
Tarea Limpieza de mantenimientoBorra los archivos de copias de seguridad e informes de los planes de mantenimiento ejecutados con anterioridad
Tarea Notificar al operadorEnvía mensajes de notificación a operadores del Agente SQL Server.  Es similar al NetSend de Windows.
Tarea Volver a generar índiceReindexa los índices de tablas y vistas de bases de datos de SQL Server. 
Tarea Reorganizar índiceReorganiza los índices de tablas y vistas de bases de datos de SQL Server.
Tarea Reducir base de datosReduce la base de datos de SQL Server.
Tarea Actualizar estadísticasActualiza el conjunto de estadísticas en la tablas o vistas de la base de datos. 
Tareas del plan de mantenimiento.

El Agente SQL Server

Para el trabajo con los planes de mantenimiento es necesario iniciar el Agente SQL Server, es conveniente que el inicio del servicio esté Modo Automático, este servicio se puede configurar al instalar la instancia de SQL Server.

Iniciar el Agente SQL Server

Para iniciar el Agente SQL Server, en el Explorador de objetos de Microsoft SQL Server Management Studio pulsar botón derecho en el nodo Agente SQL Server y seleccionar Iniciar.

Si aparece un mensaje de confirmación para ejecutar el programa responder Si.

También es posible iniciar el agente SQL Server desde el panel de control, abriendo Servicios. Puede pulsar la tecla Windows, escribir Servicios y pulsar Enter. En los servicios ubicar el Agente SQL Server de acuerdo a la instancia de SQL Server.

Pulsar botón derecho y seleccionar Iniciar.

Creando Planes de mantenimiento en SQL Server

Para crear Planes de mantenimiento en SQL Server seleccionar el nodo Administración (Management) y luego Planes de Mantenimiento (Maintenance Plan), pulsar botón derecho, luego Nuevo Plan de Mantenimiento

Escribir el nombre del Plan de mantenimiento y pulsar Aceptar

Aparece la ventana de diseño del Plan de mantenimiento. Si no aparece el cuadro de herramientas, pulsar Ctrl + Alt + X para mostrarlo.

Para el diseño se deben arrastar las tareas desde el Cuadro de herramientas a la zona de edición del Plan de mantenimiento. Se debe asignar un nombre al Sub plan y asignarle una programación para el trabajo. Para asignar el nombre, una descripción y la programación pulsar doble clic en el subplán_1.

Nombre y descripción al Sub plan asignados.

Pulsar clic en el botón Programación para asignar la periodicidad del trabajo.

Opciones de periodicidad del trabajo.

Insertar las tareas, para este ejemplo se ha trabajado con la base de datos AdventureWorks. Las tareas y el flujo serán las siguientes. Las tareas incluidas son: Reducir la base de datos, Copia de seguridad completa y Limpieza del mantenimiento.

Las tareas se configuran como sigue:

Tarea: Reducir la base de datos, agregar la tarea y pulsar doble clic para configurarla.

Crear para la primera tarea una conexión.

Seleccionar la base datos

Y los parámetros de la tarea.

Al finalizar pulsar Aceptar.

Tarea: Copia de seguridad de base de datos, seleccionar completa, seleccionar la BD AdventureWorks. Las opciones de configuración de la tarea están en las fichas: General, Destino y Opciones.

Base de datos AdventureWorks, especificar una carpeta adecuada para la copia de seguridad, para el ejercicio se especifica la carpeta C:\Seguridad, especificar la extensión de los archivos de copia de seguridad como bak

Tarea: Limpieza de mantenimiento, especificar la conexión, la carpeta de donde eliminará los archivos, la extensión de los archivos y el tiempo de permanencia de los archivos.

Una vez configuradas las tareas asignar la secuencia de las mismas, arrastrar la flecha verde desde una tarea a otra para configurar la secuencia.

Grabar el Plan de mantenimiento. Al grabar el plan de mantenimiento aparece en el Agente SQL Server un Trabajo (Job) generado.

Para la prueba del funcionamiento del Plan de mantenimiento pulse botón derecho en el trabajo y seleccione Ejecutar el trabajo en paso.

Debería finalizar de manera exitosa.

Comprobamos la existencia de la copia de seguridad.