log – SV.Net

Shrink Logs de Transação de todas as bases do SQL Server via Stored Procedure

Criamos a stored procedure abaixo que automatiza o shrink …

-- ================================================================================= 
-- Author:         Eli Leiba 
-- Create date:    2020-03
-- Procedure Name: dbo.usp_ShrinkAllLogsExcludeSysDBS 
-- Description:    This procedure shrinks all user databases log files or a specific user database log 
-- ================================================================================== 
CREATE PROCEDURE dbo.usp_ShrinkAllLogsExcludeSysDBS (@dbname SYSNAME = '%')
AS
BEGIN
 
DECLARE @TSQLExec VARCHAR (MAX) = '';
SET NOCOUNT ON;
 
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
       DROP TABLE #temp

CREATE TABLE #temp (dbname sysname, dbid int, logFileSizeBeforeMB decimal(15,2), logFileSizeAfterMB decimal(15,2));

WITH fs
AS
(
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)
INSERT INTO #temp (dbname, dbid, logFileSizeBeforeMB)
SELECT 
    name, database_id,
    (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db
WHERE database_id > 4
AND NAME LIKE @dbname;
 
SELECT @TSQLExec = CONCAT (
  @TSQLExec,
  'USE [',
  d.NAME,
  ']; CHECKPOINT; DBCC SHRINKFILE ([',
  f.NAME + ']) with no_infomsgs;' ,
  Char (13),Char (10))
FROM sys.databases d,
     sys.master_files f
WHERE d.database_id = f.database_id
      AND d.database_id > 4
      AND f.type = 1
      AND d.NAME LIKE @dbname;
PRINT @TSQLExec;
EXEC (@TSQLExec);
 
WITH fs
AS
(
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)
UPDATE a
set a.logFileSizeAfterMB = (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id)
FROM #temp a
inner join sys.databases db on a.dbid = db.database_id
WHERE database_id > 4
AND NAME LIKE @dbname
 
SELECT * FROM #temp ORDER BY dbname
SET NOCOUNT OFF;
END;
GO

-- =================================================================================

-- Author: Eli Leiba

-- Create date: 2020-03

-- Procedure Name: dbo.usp_ShrinkAllLogsExcludeSysDBS

-- Description: This procedure shrinks all user databases log files or a specific user database log

-- ==================================================================================

CREATE PROCEDURE dbo.usp_ShrinkAllLogsExcludeSysDBS (@dbname SYSNAME = '%')

BEGIN

DECLARE @TSQLExec VARCHAR (MAX) = '';

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL

DROP TABLE #temp

CREATE TABLE #temp (dbname sysname, dbid int, logFileSizeBeforeMB decimal(15,2), logFileSizeAfterMB decimal(15,2));

WITH fs

(

SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE

FROM sys.master_files

)

INSERT INTO #temp (dbname, dbid, logFileSizeBeforeMB)

SELECT

name, database_id,

(SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id) LogFileSizeMB

FROM sys.databases db

WHERE database_id > 4

AND NAME LIKE @dbname;

SELECT @TSQLExec = CONCAT (

@TSQLExec,

'USE [',

d.NAME,

']; CHECKPOINT; DBCC SHRINKFILE ([',

f.NAME + ']) with no_infomsgs;' ,

Char (13),Char (10))

FROM sys.databases d,

sys.master_files f

WHERE d.database_id = f.database_id

AND d.database_id > 4

AND f.type = 1

AND d.NAME LIKE @dbname;

PRINT @TSQLExec;

EXEC (@TSQLExec);

WITH fs

(

SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE

FROM sys.master_files

)

UPDATE a

set a.logFileSizeAfterMB = (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id)

FROM #temp a

inner join sys.databases db on a.dbid = db.database_id

WHERE database_id > 4

AND NAME LIKE @dbname

SELECT * FROM #temp ORDER BY dbname

SET NOCOUNT OFF;

END;

Depois rodamos o comando abaixo ou podemos também colocar em um Job.

USE master
GO
EXEC dbo.usp_ShrinkAllLogsExcludeSysDBS 
GO

USE master

EXEC dbo.usp_ShrinkAllLogsExcludeSysDBS

Fonte: https://www.mssqltips.com/sqlservertip/6385/shrink-sql-server-transaction-log-for-all-databases/

Trunk / Shrink Arquivo de Log SQL Server 2012

A solução abaixo altera a forma que o SQL Server trata o log. Vamos mudar o Recovery Model de FULL para SIMPLE. A partir desta mudança o SQL para de gravar logs de transações e deixa de ser possível restaurar um backup para um ponto específico de data e hora baseado no log. Para quem […]

Procurar no log do Zimbra

As vezes precisamos saber “o que aconteceu” com um determinado e-mail. No caso do Zimbra, basta você procurar no arquivo /var/log/zimbra.log Neste arquivo fica logado tudo que o servidor recebe e entrega. Uma dica: Podemos usar o comando: cat arquivo |grep -i “item procurado” > novoarquivo Exemplo: cat zimbra.log |grep -t xpto.com.br > log.txt Neste […]

Tag: log

Shrink Logs de Transação de todas as bases do SQL Server via Stored Procedure

Trunk / Shrink Arquivo de Log SQL Server 2012

Procurar no log do Zimbra

A Empresa