none
Base de datos con Problemas de Crecimiento constante RRS feed

  • Pregunta

  • Tengo una aplicación que constantemente esta escribiendo en la base de datos a una tasa de crecimiento diara de mas o menos 7 Gb diarios, esta aplicación monitorea equipos de comunicaciones y servidores. 

    Versión MSSQLSERVER Enterprise edition 2014

    Windows 2012 R2 

    El diagrama es básicamente el siguiente:

    El problema es que se me esta consumiendo el espacio en server y necesito detener el crecimiento, pese a haber configurado el data grow a menos de 102,00 kb por archivo sds.

    Hace días viene creciendo de la siguiente forma:

    Días después: 

    Este tema me tiene la cabeza volada por que necesito detener el crecimiento de la base de datos, pese a ser una base de datos incremental y se que en la versión enterprise de SQL no puedo compactar y recuperar los espacios ya reservados.

    Que acciones puedo tomar ya que me urge, agradeciendo de antemano su apoyo.


    viernes, 24 de mayo de 2019 15:32

Respuestas

  • ¿Qué es lo que crece, el .mdf o el .ldf?

    Si es el .ldf, que suele ser lo más típico en estos casos, entonces el problema es que tienes el modelo de recuperación configurado en modo "Full" y no estás haciendo backups del Log. Cuando se configura en este modo (¡que es el predeterminado!), el log va acumulando indefinidamente todos los cambios de la base de datos hasta que se hace un backup del Log, y en ese momento se libera el espacio ocupado. Claro, si no haces el backup del log la ocupación sigue creciendo.

    Si no tienes intenciones de hacer backups del Log, entonces cambia el modo de recuperación a "Simple" (desde las Propiedades de la base de datos en SSMS). Después de cambiarlo, usa "Tasks -> Shrink Files" para recuperar el espacio que antes ocupaba el Log. No tiene nada que ver la versión Enterprise, en todas las versiones se puede compactar la base de datos y recuperar el espacio. Para un .mdf en producción no es recomendable, pero si es un .ldf que ha crecido de manera anómala entonces no hay problema en reducirlo.

    viernes, 24 de mayo de 2019 19:01
  • No lo entiendo. ¿Qué sentido tiene operar de esa manera? No estás haciendo copias del Log, pero lo tienes en modo Full. Y lo cambias a Simple, lo compactas, y lo vuelves a cambiar a Full. ¿Para qué sirve todo esto? ¿Por qué no dejarlo en modo Simple, y así no crece nunca y no hay que compactarlo? La única razón por la que se usa el modo Full es para hacer copias del Log, pero si no las estás haciendo, ¿por qué pasar a modo Full?

    El paso a paso para compactar es: Usando SSMS, click derecho en la base de datos -> Tasks -> Shrink -> Files (o Database). Seleccionar las opciones deseadas, y pulsar OK. Opcionalmente, en lugar de pulsar OK pulsar en "Script" y guardarse el Script para volverlo a ejecutar cada vez que se desee. Observaciones: no conviene abusar de compactar el mdf en una base de datos cambiante porque se fragmenta. El ldf se puede compactar sin problemas si es que ha sufrido un crecimiento anómalo.

    > Que riesgos existen si elimino data historica o registros antiguos que no necesite? --> Ninguno, salvo el de que por casualidad los necesites. En ese caso, los recuperas desde un backup, que sabiamente habrás guardado antes de borrarlos.

    > La consulta de datos histórica [...] --> ¿A qué le llamas "consulta de datos histórica? ¿Cómo la estás haciendo?

    > La integridad de datos se ve vulnerada? --> ¿En qué circunstancias? ¿Qué es lo que planeas hacer y sospechas que la integridad se podría vulnerar con ello?

    > Que riesgos existen por estar la BD en modo recovery simple? --> Si se produce un error, riesgo de perder los datos desde la última vez que se hizo un backup completo. Es exactamente el mismo riesgo que corres si el modo de recovery es Full pero no haces copias del Log. Este modo solo es ventajoso si se hacen dichas copias.

    viernes, 24 de mayo de 2019 21:10
  • Sobre el tema de relaciones entre las tablas: Si tenemos la suerte de que la base de datos esté bien hecha, debería tener creados los foreign keys entre las tablas para garantizar la integridad referencial de los datos. Si es así, puedes usar la opción que hay en SSMS para generar diagramas de base de datos. Esto te dibuja el esquema con todas las tablas y sus conexiones, y de esa manera puedes saber en qué orden tienes que hacer el borrado. Si lo haces mal, las propias foreign keys impedirán la operación (o la propagarán en cascada dependiendo de cómo estén configuradas) por lo que en todos los casos la integridad quedará garantizada.

    Dicho sea de paso, esto responde también a la otra pregunta de si se podría vulnerar la integridad de datos al hacer limpieza: Si los Foreign Keys están bien puestos entonces no, no se puede vulnerar. De lo contrario sí que se puede.

    lunes, 27 de mayo de 2019 18:26

Todas las respuestas

  • ¿Qué es lo que crece, el .mdf o el .ldf?

    Si es el .ldf, que suele ser lo más típico en estos casos, entonces el problema es que tienes el modelo de recuperación configurado en modo "Full" y no estás haciendo backups del Log. Cuando se configura en este modo (¡que es el predeterminado!), el log va acumulando indefinidamente todos los cambios de la base de datos hasta que se hace un backup del Log, y en ese momento se libera el espacio ocupado. Claro, si no haces el backup del log la ocupación sigue creciendo.

    Si no tienes intenciones de hacer backups del Log, entonces cambia el modo de recuperación a "Simple" (desde las Propiedades de la base de datos en SSMS). Después de cambiarlo, usa "Tasks -> Shrink Files" para recuperar el espacio que antes ocupaba el Log. No tiene nada que ver la versión Enterprise, en todas las versiones se puede compactar la base de datos y recuperar el espacio. Para un .mdf en producción no es recomendable, pero si es un .ldf que ha crecido de manera anómala entonces no hay problema en reducirlo.

    viernes, 24 de mayo de 2019 19:01
  • Ok gracias dejame darte un poco mas de información:

    • En efecto tal como dices es el archivo MDF viene creciendo y necesito recuperar el espacio que esta ya reservado. En consecuencia como puedo recuperar ese espacio.
    • Si es cierto, no estoy haciendo backups de el archivo de logs de hecho lo estoy borrando de forma manual con la consulta: (Ya que tengo entendido que los jobs también incrementan el espacio usado).

    USE MY DATABASE;
    GO

    ALTER DATABASE MY_DATABASE
    SET RECOVERY SIMPLE;
    GO


    DBCC SHRINKFILE (MY_DATABASE_log, 1000);
    GO

    ALTER DATABASE MY_DATABASE
    SET RECOVERY FULL;
    GO

    • Tienes un paso a paso para hacer esa compactación que puedas hacerme llegar?
    • Que riesgos existen si elimino data historica o registros antiguos que no necesite?
    • La consulta de datos histórica puede sufrir algún daño irreparable? en caso de ser positivo ya que no tengo ambiente de pruebas como puedo recuperarme de ese fallo.
    • La integridad de datos se ve vulnerada?
    • Que riesgos existen por estar la BD en modo recovery simple?

    Gracias por el apoyo y pronta respuesta, amigo muy amable


    Juan C. Landa Desarrollo de App Landa Soluciones



    viernes, 24 de mayo de 2019 19:46
  • No lo entiendo. ¿Qué sentido tiene operar de esa manera? No estás haciendo copias del Log, pero lo tienes en modo Full. Y lo cambias a Simple, lo compactas, y lo vuelves a cambiar a Full. ¿Para qué sirve todo esto? ¿Por qué no dejarlo en modo Simple, y así no crece nunca y no hay que compactarlo? La única razón por la que se usa el modo Full es para hacer copias del Log, pero si no las estás haciendo, ¿por qué pasar a modo Full?

    El paso a paso para compactar es: Usando SSMS, click derecho en la base de datos -> Tasks -> Shrink -> Files (o Database). Seleccionar las opciones deseadas, y pulsar OK. Opcionalmente, en lugar de pulsar OK pulsar en "Script" y guardarse el Script para volverlo a ejecutar cada vez que se desee. Observaciones: no conviene abusar de compactar el mdf en una base de datos cambiante porque se fragmenta. El ldf se puede compactar sin problemas si es que ha sufrido un crecimiento anómalo.

    > Que riesgos existen si elimino data historica o registros antiguos que no necesite? --> Ninguno, salvo el de que por casualidad los necesites. En ese caso, los recuperas desde un backup, que sabiamente habrás guardado antes de borrarlos.

    > La consulta de datos histórica [...] --> ¿A qué le llamas "consulta de datos histórica? ¿Cómo la estás haciendo?

    > La integridad de datos se ve vulnerada? --> ¿En qué circunstancias? ¿Qué es lo que planeas hacer y sospechas que la integridad se podría vulnerar con ello?

    > Que riesgos existen por estar la BD en modo recovery simple? --> Si se produce un error, riesgo de perder los datos desde la última vez que se hizo un backup completo. Es exactamente el mismo riesgo que corres si el modo de recovery es Full pero no haces copias del Log. Este modo solo es ventajoso si se hacen dichas copias.

    viernes, 24 de mayo de 2019 21:10
  • Buenas Tardes

    Antes de todo muchas gracias por aclararme las dudas en algunos casos de todas formas te comento en relación a tus preguntas:

    • Nosotros hacemos backups semanales de la aplicación, el tema esta en que la base de datos a crecido muchísimo" Casi llega a 2 Tb" , por ser una base de datos de una app de monitoreo esta no deja de escribir.
    • Por estar conectada a una aplicación de que recoge métricas y las gráficas se refrescan constantemente por el tiempo de pooleo en la app el histórico puede verse afectado con la eliminación de los datos manuales a eso me refería con data histórica.
    • La integridad de datos se ve vulnerada? Nosotros estamos planeando eliminar datos de forma manual porque debemos recuperar el espacio que ya fue reservado por la base de datos.
    • Por ultimo creo que la única alternativa que tenemos es la eliminación de datos manual, el tema esta en la relación de las tablas, ya que el fabricante, no nos ha suministrado ni lo hara el modelo de entidad relación, que consejos puedes darme en relación a este punto en particular.


    Juan C. Landa Desarrollo de App Landa Soluciones

    lunes, 27 de mayo de 2019 16:42
  • Sobre el tema de relaciones entre las tablas: Si tenemos la suerte de que la base de datos esté bien hecha, debería tener creados los foreign keys entre las tablas para garantizar la integridad referencial de los datos. Si es así, puedes usar la opción que hay en SSMS para generar diagramas de base de datos. Esto te dibuja el esquema con todas las tablas y sus conexiones, y de esa manera puedes saber en qué orden tienes que hacer el borrado. Si lo haces mal, las propias foreign keys impedirán la operación (o la propagarán en cascada dependiendo de cómo estén configuradas) por lo que en todos los casos la integridad quedará garantizada.

    Dicho sea de paso, esto responde también a la otra pregunta de si se podría vulnerar la integridad de datos al hacer limpieza: Si los Foreign Keys están bien puestos entonces no, no se puede vulnerar. De lo contrario sí que se puede.

    lunes, 27 de mayo de 2019 18:26