Script | Proc para reduzir tamanho dos arquivos LDF

por **Admin** Qui Jun 09, 2016 12:05 pm

Olá Senhores,

Conforme solicitado segue abaixo script para reduzir o tamanho dos arquivos LDF de todos os bancos em uma instância SQL Server.

Código:: IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[UP_REDUZ_LOG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE UP_REDUZ_LOG GO CREATE PROCEDURE UP_REDUZ_LOG AS DECLARE @bd_nome char(50) DECLARE @bd_log char(50) DECLARE @bd char(50) DECLARE @CMD1 NVARCHAR(4000) DECLARE @CMD2 NVARCHAR(4000) DECLARE @tamanho int SET NOCOUNT ON if object_id('tempdb..#bd') is not null Drop table #bd create table #bd (bd_nome varchar(255), bd_log varchar(255), tamanho int) DECLARE BDCURSOR CURSOR FOR select bd.name from master.dbo.sysdatabases bd inner join master.sys.databases bd2 on bd.name=bd2.name and bd2.state_desc='ONLINE' where bd.dbid > 6 group by bd.name OPEN BDCURSOR FETCH BDCURSOR into @bd WHILE @@FETCH_STATUS <> -1 BEGIN select @CMD1='Insert #bd (bd_nome,bd_log)'+char(10) +char(10)+'select '''+rtrim(@bd)+''', a.name from '+rtrim(@bd)+'.sys.database_files a where type=1'+char(10) +char(10)+'declare @size int'+char(10) +char(10)+'select @size=a.size from '+rtrim(@bd)+'.sys.database_files a where type=0'+char(10) +char(10)+'update b set b.tamanho= ((@size*8)/(1024)) from #bd b where b.bd_nome ='''+rtrim(@bd)+''''+char(10) +char(10)+'set @size=0'+char(10) EXECUTE SP_EXECUTESQL @CMD1 FETCH BDCURSOR into @bd END CLOSE BDCURSOR DEALLOCATE BDCURSOR DECLARE LOGCURSOR CURSOR FOR select bd_nome, bd_log, tamanho from #bd order by bd_nome , bd_log, tamanho OPEN LOGCURSOR FETCH LOGCURSOR into @bd_nome, @bd_log, @tamanho WHILE @@FETCH_STATUS <> -1 BEGIN IF EXISTS(SELECT 1 FROM MASTER.SYS.DATABASES WHERE NAME=RTRIM(@bd_nome) AND RECOVERY_MODEL_DESC='SIMPLE') BEGIN select @CMD2='USE '+RTRIM(@bd_nome) +char(10)+'CHECKPOINT' +char(10)+'DBCC SHRINKFILE (N'''+RTRIM(@bd_log)+''','+rtrim(CONVERT(INT,(@tamanho*0.2)))+')'+char(10) END ELSE IF EXISTS(SELECT 1 FROM MASTER.SYS.DATABASES WHERE NAME=RTRIM(@bd_nome) AND RECOVERY_MODEL_DESC='BULK_LOGGED') BEGIN select @CMD2='USE '+RTRIM(@bd_nome) +char(10)+'CHECKPOINT' +char(10)+'ALTER DATABASE '+RTRIM(@bd_nome)+' SET RECOVERY SIMPLE'+char(10) +char(10)+'DBCC SHRINKFILE (N'''+RTRIM(@bd_log)+''','+rtrim(CONVERT(INT,(@tamanho*0.2)))+')'+char(10) +char(10)+'ALTER DATABASE '+RTRIM(@bd_nome)+' SET RECOVERY BULK_LOGGED'+char(10) END ELSE IF EXISTS(SELECT 1 FROM MASTER.SYS.DATABASES WHERE NAME=RTRIM(@bd_nome) AND RECOVERY_MODEL_DESC='FULL') BEGIN select @CMD2='USE '+RTRIM(@bd_nome) +char(10)+'CHECKPOINT' +char(10)+'ALTER DATABASE '+RTRIM(@bd_nome)+' SET RECOVERY SIMPLE'+char(10) +char(10)+'DBCC SHRINKFILE (N'''+RTRIM(@bd_log)+''','+rtrim(CONVERT(INT,(@tamanho*0.2)))+')'+char(10) +char(10)+'ALTER DATABASE '+RTRIM(@bd_nome)+' SET RECOVERY FULL'+char(10) END EXECUTE SP_EXECUTESQL @CMD2 PRINT @CMD2 FETCH LOGCURSOR into @bd_nome, @bd_log, @tamanho END CLOSE LOGCURSOR DEALLOCATE LOGCURSOR