Reducir Base de Datos SQL

Normalmente nos podemos encontrar, con que nuestra base de datos vaya creciendo considerablemente en el servidor, y al observar detalladamente la base de datos, nos llevemos la sorpresa que el fichero de datos sea bastante más reducido que el fichero de datos (log).

En caso que queramos reducir el tamaño de la base de datos podemos utilizar varias opciones. Imprescindible antes de realizar cualquier acción de las que siguen, realizar copia de la base de datos en cuestión:

a) Opción sencilla y sin problemas

Botón derecho sobre la base de datos,  seleccionar la opción de tareas y allí la opción Reducir Base de Datos. Se nos abrirá una nueva ventana, y le damos aceptar para que nos reduzca automáticamente el tamaño del conjunto de base de datos (datos y registro).

b) Opción con problemas

Si al intentar la opción a, vemos que SQL pasa literalmente de nosotros o la reducción que ha realizado es mínima, deberemos proceder a picar algo de codigo, para lo cual nos iremos a ejecutar una consulta:

entre corchetes pondremos los valores que tengamos en nuestra base de datos:

use [nombre_base_de_datos]

1. Ejecutar dos o tres veces la instrucción CHECKPOINT. Esto asegurará que todas las páginas de memoria se han escrito en el fichero de datos.
2. Luego haz un BACKUP LOG [nombre_base_de_datos] WITH TRUNCATE_ONLY para que trunque el registro de transacciones.
3. Posteriormente ejecutar DBCC SHRINKDATABASE ( [nombre_base_de_datos] , TRUNCATEONLY ) indicando el nombre del fichero del log a reducir.

Opción alternativa

Si no nos interesa tener copia de seguridad de todos los datos del fichero de log (o no hay espacio para la copia de seguridad) podemos seguir un método más rápido pero que no hace copia de seguridad de este fichero, aunque sí del de la base de datos:

1- USE MiBase
2- CHECKPOINT
3- EXEC sp_addumpdevice ‘disk’, ‘CopiaMiBase’, ‘d:\LogMiBase.bak’
4- BACKUP DATABASE MiBase TO CopiaMiBase
5- BACKUP LOG MiBase WITH TRUNCATE_ONLY
6- DBCC SHRINKFILE (MiBase_Log, 100)

en el nombre del fichero log, pondremos el nombre lógico del fichero log, en caso de no coincidir con el nombre
real del fichero.
Automáticamente veremos que el volumen de la base de datos se habrá reducido considerablemente (sobre todo el fichero de registro).

Eliminar el fichero para que se genere de Nuevo (Esta solución es demasiado drástica, emplearla solo si falla la anterior):

1. Pon la base de datos en modo “single user”.
2. Ejecuta CHECKPOINT dos o tres veces. Esto asegurará que todas las páginas de memoria se han escrito en el fichero de datos.
3. Asegúrate de que no hay conexiones abiertas a la base de datos, con lo que no puede haber transacciones a medio ejecutar.
4. Utiliza sp_detach_db para desconectar dicha base de datos.
5. Elimina el fichero de log.
6. Utiliza sp_attach_db para reconectar la base de datos. SQL Server creará un nuevo fichero de log.
   

   ¡¡¡ IMPORTANTE !!!
   Si no ejecutas el proceso completamente y en este orden, podrías tener problemas de consistencia de información en el fichero de datos.
   Por ejemplo, si apagas el equipo sin más, SQL Server no ha tenido tiempo de volcar las páginas de datos de la memoria al disco. Al reiniciar SQL Server, el problema será corregido utilizando la información contenida en el registro de transacciones, pero si este no está presente, el archivo de datos se dará por bueno, y podría ser realmente inconsistente.

   Otro detalle importante a tener en cuenta es que el log no se limpia nunca completamente, ya que siempre hay operaciones internas que SQL Server necesita mantener en él.

   Fuentes consultadas:p HELPDNA

si después de todo esto, no hay manera, recientemente he encontrado esta opción que va de lujo

ALTER DATABASE NOMBRE_BASE_DATOS
SET RECOVERY SIMPLE
GO

— Reducimos tamaño del registro de transacciones
DBCC SHRINKFILE (NOMBRE_LOGICO_FICHERO_LOG)
GO

— Cambiamos modelo de recuperación a completa
ALTER DATABASE NOMBRE_BASE_DATOS
SET RECOVERY FULL
GO

el nombre lógico, lo podéis encontrar al hacer botón derecho sobre la base de datos y lo tenéis en el nombre del fichero log (al principio)

Gracias areatic por su aportación