Skip to main content

Liberar espacio en SQL Server 2008, 2008R2 y 2012

El aspecto más importante a cuidar dentro de un servidor de base de datos, es precisamente mantener espacio disponible para poder realizar procedimientos e ingreso de datos. Sin embargo muchas veces no tenemos muchas formas de identificar cuales son los archivos que están sobrando dentro del servidor (Sistema operativo).

En SQL Server 2008 una de las características que más presenta problemas con este tema, es el crecimiento de los Logs de transacciones, el cual mantiene toda la información transaccional de la base de datos, es decir, las instrucciones insert, update y delete.
Para consultar el espacio de los archivos de log podemos realizar la siguiente consulta:

DBCC SQLPERF(LOGSPACE)

El resultado de esta consulta nos entregará información acerca de las bases de datos de la instancia, el tamaño de su correspondiente log, el porcentaje del log ocupado y, finalmente, el estado.

El siguiente script tiene por finalidad recorrer todas las bases de datos y truncar los logs que están por sobre el 90% de su capacidad. La idea de esto es mantener archivos de logs más pequeños, por otro lado se reduce también el tamaño de los backups. Es importante destacar que este script ha sido probado exitosamente en las versiones 2008, 2008 R2 y 2012 de SQL Server. NO ES COMPATIBLE con versiones anteriores (7, 2000, 2005).
USE MASTER GO DECLARE @DBID INT, @PORC INT, @NAME VARCHAR(255), @RECOVERY VARCHAR(50), @NAMELOG VARCHAR(255), @SQL NVARCHAR(4000),@RM VARCHAR(255) DECLARE CURSOR_SP CURSOR FOR SELECT DATABASE_ID,INSTANCE_NAME,CNTR_VALUE ,RECOVERY_MODEL_DESC FROM SYSPERFINFO,SYS.DATABASES WHERE COUNTER_NAME = 'PERCENT LOG USED' AND INSTANCE_NAME != '_TOTAL' AND NAME = INSTANCE_NAME AND NAME NOT IN ('TEMPDB','MSDB','MASTER','MODEL') OPEN CURSOR_SP FETCH NEXT FROM CURSOR_SP INTO @DBID,@NAME, @PORC,@RM WHILE @@FETCH_STATUS = 0 BEGIN IF(@PORC >=90) BEGIN SELECT TOP 1 @NAMELOG = F.NAME FROM SYSALTFILES F WHERE GROUPID = 0 AND @DBID = DBID SET @SQL = 'USE [' + RTRIM(@NAME) +']' + CHAR(13) + 'CHECKPOINT' + (CASE @RM WHEN 'FULL' THEN CHAR(13) +'ALTER DATABASE '+ RTRIM(@NAME) +' SET RECOVERY SIMPLE ' ELSE '' END ) + CHAR(13) + 'DBCC SHRINKFILE('''+RTRIM(@NAMELOG)+''', 1)' + (CASE @RM WHEN 'FULL' THEN CHAR(13) +'ALTER DATABASE '+ RTRIM(@NAME) +' SET RECOVERY FULL ' ELSE '' END ) + CHAR(13) --PRINT @SQL EXEC(@SQL) END FETCH NEXT FROM CURSOR_SP INTO @DBID,@NAME, @PORC,@RM END CLOSE CURSOR_SP DEALLOCATE CURSOR_SP
Labels:

Comments

Post a Comment

Popular posts from this blog

How to "safely" truncate history tables in Zabbix 6 before enabling timescale.

The script was tested against Zabbix 6.0 and 6.2. I'll add a proper introduction soon. If you don't fully understand every single line better don't run it in production. --RECREATING HISTORY TABLES WITH DATA. INSTEAD OF "WHERE FALSE" YOU CAN USE "WHERE CLOCK > X" IF YOU WANT TO PRESERVE SOME DATA ALTER TABLE history RENAME TO history_big; CREATE TABLE history as SELECT * FROM history_big where false; ALTER TABLE history_uint RENAME TO history_uint_big; CREATE TABLE history_uint as SELECT * FROM history_uint_big where false; ALTER TABLE history_str RENAME TO history_str_big; CREATE TABLE history_str as SELECT * FROM history_str_big where false; ALTER TABLE history_log RENAME TO history_log_big; CREATE TABLE history_log as SELECT * FROM history_log_big where false; ALTER TABLE history_text RENAME TO history_text_big; CREATE TABLE history_text as SELECT * FROM history_text_big where false; ALTER TABLE history_text RENAME TO history_text_big; CREATE TABLE...
Post a Comment
Read more

Consulta de espacio ocupado de logs SQL SERVER

SQLPERF Pese a que en SQL SERVER disponemos de funciones simples que nos devuelven información relevante sobre el uso de la base de datos, muchas veces necesitamos especificar campos específicos sobre una funcion predeterminada. En el ejemplo siguiente, se muestra la consulta equivalente a realizar una de las DMF más usadas: DBCC sqlperf(logspace) La consulta anterior es exactamente igual a la siguiente: SELECT instance_name AS DatabaseName,        [Data File(s) Size (KB)],        [LOG File(s) Size (KB)],        [Log File(s) Used Size (KB)],        [Percent Log Used] FROM (    SELECT *    FROM sys.dm_os_performance_counters    WHERE counter_name IN    (        'Data File(s) Size (KB)',        'Log File(s) Size (KB)',        'Log File(s) Used Size (KB)',        'Per...
Post a Comment
Read more

Uso de memoria en SQL Server

Configuración de memoria Muchas veces, nos encontramos con que un servidor SQL Server está consumiendo demasiada memoria. Las causas de esto pueden ser muchas; Demasiados usuarios conectados o un exceso de carga de trabajo pueden ser las causantes. Sin embargo, una de las características que mas destacan de SQL Server , es que una vez que le asignamos memoria , la instancia es la encargada de administrarla, me explico. Cuando miramos el administrador de tareas de Windows (Task manager) nos daremos cuenta de que en la pestaña de rendimiento se observa muy poca memoria disponible. Esto no quiere decir que sea esa la memoria utilizada (aunque explícitamente lo diga), debido a que la instancia administra su propia memoria libre dentro de su memoria utilizada. Por ejemplo, si tenemos un servidor con 100GB de memoria RAM, y Task manager nos indica que tenemos 2 GB libres, y además, SQL Server está ocupando 90GB de RAM, tenemos que tomar en cuenta que dentro de esos 90GB ocupados por la i...
Post a Comment
Read more