martes, 6 de noviembre de 2012

Control proactivo del espacio disponible en BDs SQL Server 2008

Seguramente si llevas tiempo trabajando con SQL Server te habrás encontrado alguna vez con el siguiente error:
No pudo asignar espacio para el objeto 'TableName' de base de datos 'DatabaseName' porque el grupo de archivos 'FilegroupName' está lleno. Elimine archivos innecesarios, quite objetos del grupo de archivos, agregue archivos adicionales al grupo de archivos o establezca la opción de crecimiento automático para los archivos existentes en el grupo de archivos con el fin de crear espacio...
Bien la solución puede ser tan "sencilla" como añadir un nuevo archivo en el grupo, aumentar el tamaño de un archivo del grupo.... hay distintas opciones, y pongo "sencilla" entre comillas porque no siempre tenemos disponible ese espacio en disco necesario para crear un nuevo archivo o aumentar el tamaño de uno existente. Mientras estemos con este error posiblemente tengamos ciertas aplicaciones fuera de servicio y eso puede suponer un grave problema para servicios en producción...

El objetivo de este artículo no es indicar cómo solucionar el problema (es relativamente sencillo para cualquier usuario con cierta experiencia en SQL Server) sino indicar cómo hacer un control proactivo del espacio disponible en una base de datos SQL Server de manera que sepamos con cierta antelación que se está agotando el espacio disponible y podamos actuar antes de producirse el error "No pudo asignar espacio para el objeto 'TableName' de base de datos 'DatabaseName' porque el grupo de archivos 'FilegroupName' está lleno....

Hasta SQL Server 2005 la cosa tenía cierta complejidad pero una de las novedades que incorporó SQL Server 2008 fue la posibilidad de administrar servidores basándonos en directivas, condiciones y facetas (policies, conditions y facets en inglés). Esta nueva funcionalidad nos ayudará a controlar proactivamente el espacio disponible en nuestra base de datos, a continuación os muestro un sencillo ejemplo. Antes de empezar quizás sería conveniente que consultarais el siguiente enlace de la Technet para haceros una mejor idea de lo que es la administración basada en directivas.

Vayamos a por el ejemplo concreto que supongo es lo que os interesa. Lo que haremos es (en la base de datos AdventureWorks) que cuando el fichero de datos del grupo de archivos 'PRIMARY' tenga un espacio disponible inferior a 100 Mb se envíe una alerta al operador correspondiente; de esta manera podemos actuar antes de quedarnos sin espacio, los pasos a realizar serían los siguientes:
  1. Abrimos el SSMS, vamos a " Administración\Administración de directivas", pulsamos botón derecho sobre "Condiciones" y seleccionamos la opción "Nueva condición..."


  2. Generamos una condición (conSpaceAvailableData100) que nos compruebe que el espacio disponible es superior a 100 MB, para ello informamos en la siguiente pantalla:
    Nombre de la condición: conSpaceAvailableData100
    Faceta: Data File (fichero de datos)
    Campo: @AvailableSpace (espacio disponible en el fichero de datos)
    Operador: >
    Valor: 1024000 (100 Mb en kilobytes)
    

  3. Generamos otra condición (conDatabaseName) que nos servirá para aplicar la directiva sobre una base de datos concreta, para ello informamos en la siguiente pantalla:
    Nombre de la condición: conDatabaseName
    Faceta: Database (base de datos)
    Campo: @Name (nombre de la base de datos)
    Operador: =
    Valor: 'AdventureWorks'
    

  4. A continuación creamos la directiva (dirDatabaseSpaceAvailable) para controlar el espacio disponible. Pulsamos botón derecho sobre "Directivas" y seleccionamos la opción "Nueva directiva". Introducimos en la pantalla que aparece los siguientes datos:
    Nombre de la directiva: dirDatabaseSpaceAvailable
    Condición comprobación (Check condition): conSpaceAvailableData100
    Para destinos (Against targets): Every File in Every Filegroup in conDatabaseName Database
    Modo de evaluación (Evaluation Mode): Al programar (On schedule)
    Programación: [Hay que crear una nueva pulsando el botón correspondiente, lo comento más adelante]
    

    Como comentaba anteriormente hay que crear una nueva programación pulsando el botón correspondiente, la llamaremos por ejemplo "schDatabaseAvailable" y la crearemos con los valores indicados en la siguiente imagen.


  5. Si en el SSMS expandimos el nodo " Administración\Administración de directivas" veremos que se nos ha creado la directiva "dirDatabaseSpaceAvailable" fuera de conexión. Si expandimos el nodo "Trabajos" vemos que se nos ha creado uno nuevo con un nombre generado automáticamente y tb fuera de conexión: lo renombramos a "jobDatabaseSpaceAvailable".


  6. Editamos el trabajo que hemos renombrado en el paso anterior, vamos a la sección de alertas y pulsamos el botón correspondiente para crear una nueva.


  7. Introducimos los datos para la nueva alerta en la sección "General":
    Nombre: aleDatabaseSpaceAvailable
    Tipo: Alerta de evento de SQL Server (SQL Server event alert)
    Número de error: 34052
    
    El error 34052 es el error correspondiente a infracción de una directiva en modo "Al programar". Se puede encontrar más información sobres los distintos errores en el siguiente enlace de la Technet.


  8. Vamos a la sección "Respuesta" (Response) de la alerta, seleccionamos la opción de notificar a operadores y marcamos el envío por email a uno de los operadores existentes. Si quieres saber más sobre envío de alertas a operadores de SQL Server puedes consultar nuestro artículo de julio de 2012.


  9. Para acabar con el tema de la alerta vamos a la sección "Opciones" (Options), marcamos la opción de incluir un texto de error en la alerta que enviamos por mail y definimos un mensaje adicional para que se vea más clara la directiva que se está infringiendo, algo del estilo "Directiva dirDatabaseSpaceAvailable: el espacio en el fichero de datos de la DB AdventureWorks es inferior a 100 Mb. Realice las opciones de mantenimiento oportunas.". Informamos el retardo entre respuestas y pulsamos aceptar para dar por concluida la configuración de la alerta.


  10. Ya volviendo al trabajo y para acabar también con su configuración en la sección "Notificaciones" aprovecharemos para hacer que si el trabajo falla se envíe por mail una notificación al operador y también se guarde en el registro de Windows el error correspondiente.


  11. Ya para finalizar, volvemos a la directiva que hemos creado en el punto 4, hacemos botón derecho del ratón sobre ella y pulsamos "Habilitar". Hacemos exactamente lo mismo para el trabajo que también creamos en el apartado 4, ahora tenemos directiva y trabajo habilitados. Si queremos probar el funcionamiento de la directiva pulsamos botón derecho del ratón sobre ella y pulsamos "Evaluar", me aparece la siguiente pantalla.


    Si pulsamos para ver los detalles...


    Puede parecer (por los iconos) que hay un error en la directiva pero no es así, sale el icono rojo porque se ha producido una infracción en la directiva que hemos creado: el espacio disponible en el fichero de datos de AdventureWorks es de 3.16 Mb cuando en la directiva hemos indicado que se produzca una infracción cuando el tamaño sea inferior a 100 Mb. El funcionamiento es correcto, con el trabajo que hemos programado, de lunes a viernes a las 00:00 se evaluará la directiva y si se produce una infracción se enviará un mail al operador indicado para que realice las operaciones oportunas.

Espero que os sea útil este artículo (mi tiempo me ha llevado escribirlo), con directivas, facetas y condiciones se simplifican notablemente las tareas de administración de SQL Server, os recomiendo que leáis sobre el tema. Recuerda que dentro de areaTIC puedes encontrar otros artículos interesantes, no dudes en consultar nuestro archivo.


LECTURAS RELACIONADAS RECOMENDADAS POR AREATIC.NET

1 comentario:

Jose Miguel Suarez dijo...

muy muy bueno, pero el lugar de una alerta quiero lanzar una tarea que me realiza un backup del transaction log para reducir su tamaño y evitar problemas del 100% de uso. ¿Cómo se que la verificación se ha realizado y que cumple la condición para lanzar la tarea, donde lo programo?

Publicar un comentario