Reducir el tamaño del log de transacciones de todas las bases de datos en SQL Server.

Dándole vueltas a lo que escribí en el post anterior se me ocurrió que podía crear un script para reducir el log de transacciones de todas las bases de datos (en SQL Server 2005 y superior), exceptuando aquellas que son del propio sistema.

El algoritmo se basa en los pasos siguientes:

1. Recorrer todas las bases de datos que no son de sistema y que tienen el modelo de recuperación en completo o registro de copias masivas (quedan almacenadas en la variable de tabla @tblBD).
2. Poner las bases de datos que cumplen las condiciones anteriores en modelo de recuperación simple.
3. Buscar los ficheros que almacenan los registros de transacciones para cada base de datos (quedan almacenados en la tabla temporal #tblLog).
4. Reducir el espacio de cada uno de los ficheros detectados en el paso anterior (mediante la instrucción DBCC_SHRINKFILE)
5. Poner el modelo de recuperación de la base de datos al estado original (completo o registro de copias masivas).

Así que me puse manos a la obra, y aquí os lo dejo:

DECLARE @tblBD TABLE
(
  IDAux SMALLINT IDENTITY(1,1), 
  BDID INT,
  BDName VARCHAR(4000),
  RecoveryModel SMALLINT
)

CREATE TABLE #tblLog 
(
  IDRowLog SMALLINT IDENTITY (1,1), 
  LogFilename VARCHAR(4000)
)

DECLARE @intRecoveryModel SMALLINT
DECLARE @intNumTotalBD SMALLINT
DECLARE @intContBD SMALLINT=1
DECLARE @intBDID INT
DECLARE @strBDName VARCHAR(4000)
DECLARE @strSQL AS NVARCHAR(4000)

INSERT INTO @tblBD
SELECT database_id,
  name,
  recovery_model 
FROM sys.databases
WHERE NAME NOT IN ('master','tempdb','model','msdb')
AND recovery_model IN (1,2)

SELECT @intNumTotalBD=COUNT(*) FROM @tblBD

WHILE @intContBD<=@intNumTotalBD
  BEGIN
 SELECT @intBDID=BDID,
   @strBDName=BDName,
   @intRecoveryModel=RecoveryModel
 FROM @tblBD WHERE IDAux=@intContBD
 
 SET @strSQL='ALTER DATABASE '+@strBDName+' SET RECOVERY SIMPLE'
 exec sp_executesql @strSQL
 --PRINT @strsQL
 
 DECLARE @intLogTotal AS SMALLINT
 DECLARE @intPos AS SMALLINT=1
 DECLARE @strLogFile AS VARCHAR(4000)

 INSERT INTO #tblLog (LogFilename)
 SELECT name FROM sys.master_files 
 WHERE database_id=@intBDID AND type=1

 SELECT @intLogTotal=COUNT(*) 
 FROM #tblLog

 WHILE @intPos<=@intLogTotal
   BEGIN
  SELECT @strLogFile=LogFileName 
  FROM #tblLog WHERE IDRowLog=@intPos
  
  SET @strSQL='USE '+@strBDName+' DBCC SHRINKFILE('+@strLogFile+')'
  EXEC sp_executesql @strSQL
  --PRINT @strSQL
  SET @intPos+=1
   END
   TRUNCATE TABLE #tblLog
 IF @intRecoveryModel=1 
   SET @strSQL='ALTER DATABASE '+@strBDName+' SET RECOVERY FULL'
 ELSE 
   SET @strSQL='ALTER DATABASE '+@strBDName+ ' SET RECOVERY BULK_LOGGED'
 
 exec sp_executesql @strSQL
 --PRINT @strSQL
 SET @intContBD+=1
  END
  DROP TABLE #tblLog

Aun así, también existen otras formas de lograr el mismo objetivo, con lo que podéis evaluar la opción que más os guste o entendáis mejor.

¡Enjoy T-SQL!