SQL Server – 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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

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

-- 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

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

USE master
GO
EXEC dbo.usp_ShrinkAllLogsExcludeSysDBS 
GO

1

2

3

4

USE master

GO

EXEC dbo.usp_ShrinkAllLogsExcludeSysDBS

GO

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 […]

Executar um Script grande no SQL Server

Existem algumas situações que ao rodar um script grande no SQL Management Studio dá erro de memória porque o arquivo é muito grande. Nesta situação temos uma saída que é rodar o sqlcmd via comando. Abaixo um exemplo.

sqlcmd -S servidor -U login -P senha -i Script_full.sql

1	sqlcmd -S servidor -U login -P senha -i Script_full.sql

Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout] 
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit] 
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit] 
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

Sqlcmd [-U login id] [-P password]

[-S server] [-H hostname] [-E trusted connection]

[-d use database name] [-l login timeout] [-t query timeout]

[-h headers] [-s colseparator] [-w screen width]

[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]

[-c cmdend] [-L[c] list servers[clean output]]

[-q "cmdline query"] [-Q "cmdline query" and exit]

[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]

[-u unicode output] [-r[0|1] msgs to stderr]

[-i inputfile] [-o outputfile] [-z new password]

[-f | i:[,o:]] [-Z new password and exit]

[-k[1|2] remove[replace] control characters]

[-y variable length type display width]

[-Y fixed length type display width]

[-p[1] print statistics[colon format]]

[-R use client regional setting]

[-b On error batch abort]

[-v var = "value"...] [-A dedicated admin connection]

[-X[1] disable commands, startup script, environment variables [and exit]]

[-x disable variable substitution]

[-? show syntax summary]

Remover todo o conteúdo de um banco de dados SQL Server

Algumas vezes alguns provedores de hospedagem geram tantos problemas que precisamos nos virar nos 30 para conseguir manipular os dados que são nossos e não dos provedores. Pois bem, segue abaixo um script que apaga todo o conteúdo de um banco de dado SQL Server. Ele remove: – Todas tabelas – Todas Stored Procedures – […]

Select em um campo varbinary

Se você precisar fazer um select em um campo varbinary e precisa ver o conteúdo, use o comando abaixo.

select *,CAST(CAMPO AS VARCHAR(100)) from TABELA

1	select *,CAST(CAMPO AS VARCHAR(100)) from TABELA

Onde CAMPO é o campo que está como varbinary e TABELA é a tabela do banco que contém o campo.

Erro ao salvar a alteração de uma tabela

Para quem está começando a trabalhar com o SQL Server 2008 vai ai uma dica. Por default ele vem com uma configuração que não permite fazer alterações em tabelas já criadas. Seja tentando mudar pelo diagrama ou pelo design table. O aviso que aparece é “Saving changes is not permitted. The changes you have […]