Tamanho dos Datafiles e LogFiles

Depois de muito tempo sem atualizar o blog, hoje resolvi compartilhar meus scripts que eu utilizo diariamente para monitorar os bancos de dados que administro, essa procedure UspGetSpace é extremamente útil para levantar a ocupação de cada base de dados da instância, e ainda, possibilita o envio de e-mail se alguma base atingir um nível crítico de ocupação.

USE [Master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[UspGetSpace]
(
 @NomeBanco     VARCHAR(128) = NULL, 
 @FileGroupName VARCHAR(50)  = NULL, 
 @Threshold     BIT          = NULL, 
 @EnviaEmail    BIT          = NULL,
 @EmailDestino  VARCHAR(100) = NULL,
 @NomeCliente	VARCHAR(30)  = NULL
)
AS

SET NOCOUNT ON
/*
Criado: Amaury Germano Jr.
Data 30/12/2015
Revisado: 12/09/2018
Ajuste: 24/08/19 - Adicionando funcionalidade de envio de email dos bancos que estão acima da capacidade de ocupação

Obj: Verificar o tamanho dos databases

-Adicionado comentarios aos blocos
-Correcao no nome das colunas
-Opcao de buscar de apenas um banco ou de todos da instancia
-Filtrar por filegroup
-Caminho dos datafiles
-Nome Logico
- Warning e Critical para a porcentagem de ocupacao dos bancos com base no tamanho dos datafiles!

EXEMPLO DE UTILIZACAO: 
EXEC [UspGetSpace] @NomeBanco = 'master' , @FileGroupName='LOG'--Retorna apenas tamanho do banco master
GO
EXEC UspGetSpace @FileGroupName='LOG'--Retorna o Tamanho de todos os banco da instancia
GO
EXEC UspGetSpace @Threshold=1--Retorna todos os bancos que estão acima do limite de ocupação
GO

EXEC UspGetSpace  --Para envio de email dos bancos que estão acima do limite de ocupação
 @Threshold		= 1, 
 @EnviaEmail    = 1,
 @EmailDestino  ='seuemail@dominio.com.br',
 @NomeCliente	='SeuCliente'



*/

     IF OBJECT_ID('tempdb..##FileGroupSize') IS NOT NULL
         BEGIN
             DROP TABLE ##FileGroupSize;
     END;

     -- CRIA NOVAMENTE A TABELA TEMPORARIA
     CREATE TABLE ##FileGroupSize
     (NomeBanco       VARCHAR(128), 
      NomeLogico      VARCHAR(128), 
      FileGroupName   VARCHAR(100), 
      TamanhoTotalMB  DECIMAL(18, 2), 
      TamanhoUsadoMB  DECIMAL(18, 2), 
      TamanhoLivreMB  DECIMAL(18, 2), 
      [%LivreMB]      DECIMAL(5, 2), 
      [%OcupadoMB]    DECIMAL(5, 2), 
      CaminhoDatafile VARCHAR(255)
     );

     --COLETA O TAMANHO DOS BANCOS E INSERE NA TABELA TEMPORARIA
     EXEC Sp_msforeachdb 
          N'use [?]; 
INSERT INTO ##FileGroupSize
SELECT [NomeBanco]=f.NomeBanco
	   ,f.NomeLogico
	   ,[FilegroupName]=Isnull(g.groupname, ''LOG'')
       ,[TamanhoTotalMB]=CAST(Sum(Size) * ( 8192.0 / 1048576 ) AS DECIMAL (18,2))
       ,[TamanhoUsadoMB]=CAST(Sum(SpaceUsed) * ( 8192.0 / 1048576 ) AS DECIMAL(18,2)) 
       ,[TamanhoLivreMB]=CAST(( Sum(Size) * ( 8192.0 / 1048576 ) ) - ( Sum(SpaceUsed) * ( 8192.0 / 1048576 ) )  AS DECIMAL(18,2))
       ,[%LivreMB]=CAST(( ( ( Sum(Size) * ( 8192.0 / 1048576 ) ) - ( Sum(SpaceUsed) * ( 8192.0 / 1048576 ) ) ) / ( Sum(Size) * ( 8192.0 / 1048576 ) ) ) * 100 AS decimal (5,2))
	   ,[%OcupadoMB]=CAST(( ( Sum(SpaceUsed) * ( 8192.0 / 1048576 ) ) / ( Sum(Size) * ( 8192.0 / 1048576 ) ) ) * 100 AS decimal(5,2))
	   ,f.CaminhoDatafile 
FROM   (SELECT  db_name()                                       AS NomeBanco
			   ,name											AS NomeLogico	
			   ,filename										AS CaminhoDatafile
               ,groupid
               ,Fileproperty(NAME, ''IsLogFile'')                 AS Dados_Log
               ,CONVERT(FLOAT, size)                            AS Size
               ,CONVERT(FLOAT, Fileproperty(NAME, ''SpaceUsed'')) AS SpaceUsed
        FROM   sysfiles) f
       LEFT JOIN sysfilegroups g
              ON f.groupid = g.groupid
GROUP  BY NomeBanco,
          NomeLogico,
		  Dados_Log ,
          g.groupname,
		  CaminhoDatafile
ORDER BY 2';
     WITH cte
          AS (SELECT NomeBanco, 
                     NomeLogico, 
                     FileGroupName, 
                     TamanhoTotalMB, 
                     TamanhoUsadoMB, 
                     TamanhoLivreMB, 
                     [%LivreMB], 
                     [%OcupadoMB],
                     CASE
                         WHEN TamanhoTotalMB < 250000
                              AND FileGroupName <> 'LOG'
                         THEN '85'
                         WHEN TamanhoTotalMB BETWEEN 250000 AND 500000
                              AND FileGroupName <> 'LOG'
                         THEN '92'
                         WHEN TamanhoTotalMB BETWEEN 501000 AND 750000
                              AND FileGroupName <> 'LOG'
                         THEN '94'
                         WHEN TamanhoTotalMB > 751000
                              AND FileGroupName <> 'LOG'
                         THEN '96'
                         WHEN TamanhoTotalMB < 50000
                              AND FileGroupName = 'LOG'
                         THEN '60'
                         WHEN TamanhoTotalMB BETWEEN 50001 AND 100000
                              AND FileGroupName = 'LOG'
                         THEN '65'
                         WHEN TamanhoTotalMB BETWEEN 100001 AND 150000
                              AND FileGroupName = 'LOG'
                         THEN '70'
                         WHEN TamanhoTotalMB > 150000
                              AND FileGroupName = 'LOG'
                         THEN '75'
                     END AS [% Warning],
                     CASE
                         WHEN TamanhoTotalMB < 250000
                              AND FileGroupName <> 'LOG'
                         THEN '90'
                         WHEN TamanhoTotalMB BETWEEN 250000 AND 500000
                              AND FileGroupName <> 'LOG'
                         THEN '94'
                         WHEN TamanhoTotalMB BETWEEN 501000 AND 750000
                              AND FileGroupName <> 'LOG'
                         THEN '97'
                         WHEN TamanhoTotalMB > 751000
                              AND FileGroupName <> 'LOG'
                         THEN '98'
                         WHEN TamanhoTotalMB < 50000
                              AND FileGroupName = 'LOG'
                         THEN '80'
                         WHEN TamanhoTotalMB BETWEEN 50001 AND 100000
                              AND FileGroupName = 'LOG'
                         THEN '85'
                         WHEN TamanhoTotalMB BETWEEN 100001 AND 150000
                              AND FileGroupName = 'LOG'
                         THEN '90'
                         WHEN TamanhoTotalMB > 150000
                              AND FileGroupName = 'LOG'
                         THEN '95'
                     END AS [% Critical], 
                     [CaminhoDatafile], 
                     DataColeta = GETDATE()
              FROM ##FileGroupSize
			  )
          SELECT *
          INTO #Threshold
          FROM cte
		  order by [%OcupadoMB] desc;



     IF @Threshold = 1 AND @EnviaEmail <> 1
         BEGIN
             SELECT *
             FROM #Threshold
             WHERE TRY_CAST(replace([%OcupadoMB], '%', '') AS DECIMAL(5, 2)) >= [% Warning]
                   OR TRY_CAST(replace([%OcupadoMB], '%', '') AS DECIMAL(5, 2)) >= [% Critical]
                   AND NomeBanco = ISNULL(@NomeBanco, NomeBanco)
                   AND FileGroupName = ISNULL(@FileGroupName, FileGroupName)
		     ORDER BY [%OcupadoMB] DESC;
		END;
     ELSE IF @Threshold = 1 AND @EnviaEmail = 1
         BEGIN

             --VALIDA ENVIO DE EMAIL COM OS BANCOS QUE TIVERAM ERRO NO CHECKDB
             IF EXISTS
             (
                 SELECT TOP 1 1
                 FROM #Threshold
                 WHERE( TRY_CAST(replace([%OcupadoMB], '%', '') AS DECIMAL(5, 2)) >= [% Warning]
                       OR TRY_CAST(replace([%OcupadoMB], '%', '') AS DECIMAL(5, 2)) >= [% Critical])
					    )
             )
                 BEGIN
                     --CRIA A TABELA COM OS NOMES DOS BANCOS CRIADOS, SOMENTE SE ESTIVER PARA ENVIAR EMAIL 
                     IF @EnviaEmail = 1 AND @EmailDestino IS NOT NULL
                         BEGIN
                             ---- INICIO DAS CONFIGURACOES DE ENVIO DE EMAIL
                             DECLARE @Xml VARCHAR(MAX), @Body VARCHAR(MAX), @NomeInstancia VARCHAR(100), @AssuntoEmail VARCHAR(200), @ProfileMailName VARCHAR(50);
                             SET @ProfileMailName = 'PROFILE';
                             SET @NomeInstancia =
                             (
                                 SELECT @@SERVERNAME + '\' + @@SERVICENAME
                             );
                             SET @AssuntoEmail = 'Cliente: [' + @NomeCliente + '] - Bancos acima do limite de ocupação - [' + @NomeInstancia + ']';
                             SET @Xml = CAST(
                             (
                                 SELECT [NomeBanco] AS 'td', 
                                        '', 
                                        [NomeLogico] AS 'td', 
                                        '', 
                                        [FileGroupName] AS 'td', 
                                        '', 
                                        [TamanhoTotalMB] AS 'td', 
                                        '', 
										[TamanhoUsadoMB] AS 'td', 
                                        '', 
										[TamanhoLivreMB] AS 'td', 
                                        '', 
										[%LivreMB] AS 'td', 
                                        '', 
										[%OcupadoMB] AS 'td', 
                                        '', 
										[% Warning] AS 'td', 
                                        '', 
										[% Critical] AS 'td', 
                                        '',
										[DataColeta] AS 'td', 
                                        ''
                                    FROM #Threshold
									WHERE TRY_CAST(replace([%OcupadoMB], '%', '') AS DECIMAL(5, 2)) >= [% Warning]
									   OR TRY_CAST(replace([%OcupadoMB], '%', '') AS DECIMAL(5, 2)) >= [% Critical]
									    ORDER BY [%OcupadoMB] DESC
									   FOR XML PATH('tr'), ELEMENTS
                             ) AS VARCHAR(MAX));
                             SET @Body = '<html><body><H2> Bancos Acima da Capacidade de Ocupação SQL Server [' + @NomeInstancia + '] </H2>
            <table border = 1> 
            <tr>
			<th> Nome Banco </th>
			<th> Nome Logico </th>
			<th> FileGroupName </th>
			<th> TamanhoTotalMB </th>
			<th> TamanhoUsadoMB </th>
			<th> TamanhoLivreMB </th>
			<th> % LivreMB </th>
			<th> % OcupadoMB </th>
			<th> % Warning </th>
			<th> % Critical </th>
			<th> Data Coleta </th>
			</tr>';
                             SET @Body = @Body + @Xml + '</table></body></html>';
                             EXEC msdb.dbo.sp_send_dbmail 
                                  @profile_name = @ProfileMailName, -- replace with your SQL Database Mail Profile 
                                  @body = @Body, 
                                  @body_format = 'HTML', 
                                  @recipients = @EmailDestino, -- replace with your email address
                                  @subject = @AssuntoEmail;

                             /*FIM DAS CONFIGURAÇÕES PARA ENVIO DE EMAIL*/

                     END;
                         ELSE
                         BEGIN
                             IF @EnviaEmail = 1 AND @EmailDestino IS NULL
                                 BEGIN --SE VAI A VARIAVEL @EnviaEmail = 1 É OBRIGATORIO INFORMAR O EMAIL DE DESTINO
                                     RAISERROR('SE QUER QUE ENVIE E-MAIL, ENTÃO, É OBRIGATÓRIO QUE INFORME O EMAIL DE DESTINO NA VARIAVEL @EmailDestino', 15, 10);
                             END;
                     END;
             END;
     END;
         ELSE
         BEGIN
             SELECT *
             FROM #Threshold
             WHERE NomeBanco = ISNULL(@NomeBanco, NomeBanco)
                   AND FileGroupName = ISNULL(@FileGroupName, FileGroupName)
			 ORDER BY [%OcupadoMB] DESC;
     END;

SET NOCOUNT OFF
GO


Publicidade
Publicado em Monitoramento, T-SQL, Uncategorized | Deixe um comentário

Migrando as configurações do Database Mail

Salve, Galera!!

Esses dias precisei migrar um ambiente SQL Server 2008, até então sem problemas. Mas havia me esquecido de criar as configurações do database mail, fui no ambiente que estava sendo migrado e me deparei com diversos Profilers e Database Mails que eram utilizados para envio de e-mails para diversas áreas da empresa.

Tentei utilizar a ferramenta gráfica Management Studio, só que não há essa opção de gerar scripts das configurações do database mail, já pensei comigo mesmo, vou levar um bom tempo pra ter que criar um por um no ambiente novo, até que resolvi vasculhar nos meus scripts e para minha surpresa tinha um arquivo com o titulo de “MigrateDataBaseMail.SQL“, resolvi testar e funcionou perfeitamente, migrei todas as configurações do Database Mail em apenas 1 minuto.

Sendo assim, vou compartilhar esse excelente script.


USE msdb

GO

DECLARE @TheResults VARCHAR(max),
 @vbCrLf CHAR(2)

SET @vbCrLf = Char(13) + Char(10)
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'

SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings '
 + p.NAME
 + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = '''
 + p.NAME + ''') 
 BEGIN
 --CREATE Profile [' + p.NAME
 + ']
 EXECUTE msdb.dbo.sysmail_add_profile_sp
 @profile_name = ''' + p.NAME
 + ''',
 @description = '''
 + Isnull(p.description, '') + ''';
 END --IF EXISTS profile
 '
 + '
 IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = '''
 + a.NAME + ''')
 BEGIN
 --CREATE Account [' + a.NAME
 + ']
 EXECUTE msdb.dbo.sysmail_add_account_sp
 @account_name = ' + CASE WHEN a.NAME IS NULL THEN ' NULL ' 
 ELSE + '''' + a.NAME + '''' END + ',
 @email_address = ' + CASE WHEN a.email_address IS NULL THEN ' NULL ' 
 ELSE + '''' + a.email_address + '''' END + ',
 @display_name = ' + CASE WHEN a.display_name IS NULL THEN ' NULL ' 
 ELSE + '''' + a.display_name + '''' END + ',
 @replyto_address = ' + CASE WHEN a.replyto_address IS NULL THEN ' NULL ' 
 ELSE + '''' + a.replyto_address + '''' END + ',
 @description = ' + CASE WHEN a.description IS NULL THEN ' NULL ' 
 ELSE + '''' + a.description + '''' END + ',
 @mailserver_name = ' + CASE WHEN s.servername IS NULL THEN ' NULL ' 
 ELSE + '''' + s.servername + '''' END + ',
 @mailserver_type = ' + CASE WHEN s.servertype IS NULL THEN ' NULL ' 
 ELSE + '''' + s.servertype + '''' END + ',
 @port = ' + CASE WHEN s.port IS NULL THEN ' NULL ' 
 ELSE + '''' + CONVERT(VARCHAR, s.port) + '''' END + ',
 @username = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' 
 ELSE + '''' + c.credential_identity + '''' END + ',
 @password = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' 
 ELSE + '''NotTheRealPassword''' END + ', 
 @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' 
 ELSE ' 0 ' END + ',
 @enable_ssl = ' + CASE WHEN s.enable_ssl = 1 THEN ' 1 ' 
 ELSE ' 0 ' END + ';
 END --IF EXISTS account
 '
 + '
IF NOT EXISTS(SELECT *
 FROM msdb.dbo.sysmail_profileaccount pa
 INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
 INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id 
 WHERE p.name = ''' + p.NAME
 + '''
 AND a.name = ''' + a.NAME
 + ''') 
 BEGIN
 -- Associate Account [' + a.NAME
 + '] to Profile [' + p.NAME
 + ']
 EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = ''' + p.NAME
 + ''',
 @account_name = ''' + a.NAME
 + ''',
 @sequence_number = '
 + CONVERT(VARCHAR, pa.sequence_number)
 + ' ;
 END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.NAME
 + '
--#################################################################################################
/*
IF EXISTS(SELECT *
 FROM msdb.dbo.sysmail_profileaccount pa
 INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
 INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id 
 WHERE p.name = ''' + p.NAME
 + '''
 AND a.name = ''' + a.NAME
 + ''')
 BEGIN
 EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = '''
 + p.NAME + ''',@account_name = ''' + a.NAME
 + '''
 END 
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = '''
 + a.NAME
 + ''')
 BEGIN
 EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = '''
 + a.NAME
 + '''
 END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = '''
 + p.NAME
 + ''') 
 BEGIN
 EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = '''
 + p.NAME + '''
 END
*/
 '
FROM msdb.dbo.sysmail_profile p
 INNER JOIN msdb.dbo.sysmail_profileaccount pa
 ON p.profile_id = pa.profile_id
 INNER JOIN msdb.dbo.sysmail_account a
 ON pa.account_id = a.account_id
 LEFT OUTER JOIN msdb.dbo.sysmail_server s
 ON a.account_id = s.account_id
 LEFT OUTER JOIN sys.credentials c
 ON s.credential_id = c.credential_id;

WITH E01(N)
 AS (SELECT 1
 UNION ALL
 SELECT 1
 UNION ALL
 SELECT 1
 UNION ALL
 SELECT 1
 UNION ALL
 SELECT 1
 UNION ALL
 SELECT 1
 UNION ALL
 SELECT 1
 UNION ALL
 SELECT 1
 UNION ALL
 SELECT 1
 UNION ALL
 SELECT 1), -- 10 or 10E01 rows 
 E02(N)
 AS (SELECT 1
 FROM E01 a,
 E01 b), -- 100 or 10E02 rows 
 E04(N)
 AS (SELECT 1
 FROM E02 a,
 E02 b), -- 10,000 or 10E04 rows 
 E08(N)
 AS (SELECT 1
 FROM E04 a,
 E04 b), --100,000,000 or 10E08 rows 
 --E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need, 
 Tally(N)
 AS (SELECT Row_number()
 OVER (
 ORDER BY N)
 FROM E08),
 ItemSplit( ItemOrder, Item )
 AS (SELECT N,
 Substring(@vbCrLf + @TheResults + @vbCrLf, N + Datalength(@vbCrLf), 
 Charindex(@vbCrLf, @vbCrLf + @TheResults + @vbCrLf, N + 
 Datalength(@vbCrLf)) - N - Datalength(@vbCrLf))
 FROM Tally
 WHERE N < Datalength(@vbCrLf + @TheResults)
 --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf 
 AND Substring(@vbCrLf + @TheResults + @vbCrLf, N, Datalength(@vbCrLf)) = 
 @vbCrLf --Notice how we find the delimiter 
 )
SELECT Row_number()
 OVER (
 ORDER BY ItemOrder) AS ItemID,
 Item
FROM ItemSplit 

 

Até Mais!!!

 

Publicado em Migração, T-SQL | Marcado com , , , , , , | 1 Comentário

Quanto tempo falta para terminar o Backup ou Restore ?

Esse script é muito bom pra identificar o tempo estimado para terminar um backup ou restore de um banco de dados SQL Server.

 

SELECT 
session_id AS 'id_sessão',
start_time AS 'Data_inicio',
status,
command AS 'Comando',
percent_complete AS '% Concluído',
Dateadd(n,(estimated_completion_time/60/1000),Getdate())
as'Tempo Estimado Término'
FROM 
sys.dm_exec_requests
WHERE 
command IN ('BACKUP DATABASE','RESTORE DATABASE')

Resultado:

TempoBackupRestore

Lembrando que é apenas uma estimativa, mas da pra ter uma ideia de quanto tempo vai durar o processo.

Até o próximo post!!!!

Publicado em Backup, DMVs, Restore, T-SQL, Uncategorized | Marcado com , , , , , | Deixe um comentário

Problemas na Instalação ou aplicação de Service Pack no SQL Server – Reboot Computer Failed

Hoje, vou falaremos de um pequeno problema que pode acontecer quando realizamos a instalação ou aplicação do service pack do SQL Server, após entrar no setup do SQL Server e selecionar nova instalação, logo aparece uma tela de validações de pré-requisitos, e nesse momento ele pode ser que a validação de “Restart Computer” venha a falhar, conforme imagem abaixo:

Esse procedimento se aplica também para as versões do SQL Server 2008/2008 R2/2012 e 2014.

INSTALAÇÃO SQL SERVER

ErrorInstallSQL

Se tiver instalando em um ambiente em cluster que já possui instância ativas e não for possível movimentar para o outro nó para realizar o restart do servidor, tem uma solução para ignorar essa validação.

Entre no CMD e vá até o diretório da mídia de instalação e execute o comando abaixo:

Setup.exe /SkipRules=RebootRequiredCheck /Action=InstallFailOverCluster

InstallSkipDOS

Agora ele vai  fazer a validação e vai ignorar. .

SQLSkipReboot

 

Bom, agora é só instalar normalmente!!

APLICAR SERVICE PACK 

Quando você for aplicar o service pack, no caso aqui, aplicaremos o SP3 (x64) do SQL Server 2008 R2, teremos que realizar um comando parecido para pular a validação de reboot do servidor, conforme imagem abaixo:

ErrorInstallServicePack_Failed

 

Agora vamos ver como aplicamos o service pack, sem ter que fazer o restart do servidor.

Entre no CMD e vá até o diretório onde se encontro o executável do Service Pack e execute o comando abaixo:

SQLServer2008R2SP3-KB2979597-x64-ENU.exe /SkipRules=RebootRequiredCheck

InstallSkipDOS_servicePack

O setup vai abrir e também vai ignorar o “reboot computer”.

InstallServicePackSkip

 

Agora só prosseguir normalmente com à aplicação do service pack.

 

Obs. Lembrando que se a instalação for em um ambiente em cluster, esse procedimento deve se repetir em todos os nós!

Valeu!!!

 

Publicado em Instalação, Troubleshooting, Uncategorized | Deixe um comentário

Tamanho dos Arquivos de Dados e Log – [sp_dba_getspace]


Fala galera!!

Hoje, compartilharei a stored procedure (sp_dba_getspace) que utilizo diariamente para saber os tamanhos dos arquivos de dados e logs de todos os databases.

Com essas informações conseguimos identificar o tamanho ocupado por cada filegroup e com isso conseguimos tomar alguma ação de redimensionar os arquivos e ou identificar algum problema de ocupação em tempo real.

Sabendo-se que o processo de AutoGrowth (Auto-Crescimento)  é pesado e que pode causar impacto no ambiente, podemos nos basear na porcentagem retornada para realizar um monitoramento e realizar o redimensionamento dos datafiles em um horário apropriado.

Essa procedure é extremamente útil e acho interessante ter em todas as instâncias.

Olhem o retorno da procedure, se não estiver boa a visualização clique na imagem.

sp_dba_getspace


USE [master]
GO

/****** Object: StoredProcedure [dbo].[sp_dba_getspace] Script Date: 22/07/2015 11:56:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_dba_getspace] (@dbname varchar(100) = null,
 @trheshold decimal(5,2) = null)
AS

/*
Criado: Amaury Germano Jr.
Data 22/07/2015
Objetivo:Visualizar a ocupacao dos datafiles e logfiles de todos os bancos de dados,
possibilitando filtro por nome do banco e/ou por porcentagem de ocupacao
*/

DECLARE @dbname_cur varchar(100)
DECLARE @ComandoLista varchar(2000)
DECLARE @ComandoInsereLog varchar(100)
DECLARE @ConsultaFinal varchar(1000)

SET @ComandoInsereLog = 'dbcc sqlperf(''LOGSPACE'')'

CREATE TABLE #LOGSPACE
(
 dbname varchar(100),
 LogSize real,
 LogSpaceUsed real,
 Status int
)

CREATE TABLE #DBSPACE
(
 dbname varchar(100)
 ,FileId int
 ,FileSizeMB decimal(15,2)
 ,FileUsageMB decimal(15,2)
 ,FreeSpaceMB decimal(15,2)
 ,LogicalName varchar(100)
 ,filename varchar(255)
)

INSERT INTO #LOGSPACE
EXEC (@ComandoInsereLog)

DECLARE DBSIZE CURSOR FOR

SELECT NAME FROM master..SYSDATABASES
WHERE DATABASEPROPERTYEX(name,'Status') = 'Online'
ORDER BY 1 ASC

OPEN DBSIZE

FETCH NEXT FROM DBSIZE INTO @dbname_cur 

WHILE @@FETCH_STATUS = 0
BEGIN

SET @ComandoLista =
 'USE ['+@dbname_cur+']'+'
 SELECT
 DB_NAME(),
 FILEID,
 [FILE_SIZE_MB] = CONVERT(DECIMAL(15, 2), Round(size / 128.000, 2)),
 [SPACE_USED_MB] = CONVERT(DECIMAL(15, 2), Round(Fileproperty(NAME, ''SpaceUsed'') / 128.000, 2)),
 [FREE_SPACE_MB] = CONVERT(DECIMAL(15, 2), Round(( size - Fileproperty(NAME, ''SpaceUsed'') ) / 128.000, 2)),
 NAME,
 FILENAME
FROM dbo.sysfiles '

INSERT INTO #DBSPACE
exec (@ComandoLista)

FETCH NEXT FROM DBSIZE INTO @dbname_cur 

END

CLOSE DBSIZE
DEALLOCATE DBSIZE

SELECT @ConsultaFinal = '
SELECT
Servername = (select @@SERVERNAME),
Instance = (select @@SERVICENAME),
DatabaseName = a.dbname,
LogicalName = a.LogicalName,
DBSizeMB = a.FileSizeMB,
DBSizeFreeMB = a.FreeSpaceMB,
[%UsageData] = 100 - CONVERT(DECIMAL(5,2),A.FreeSpaceMB * 100.0 / a.FileSizeMB),
LogSize = CONVERT(DECIMAL(15,2),b.LogSize),
[%UsageLog] = CONVERT(DECIMAL(5,2),b.LogSpaceUsed),
[Path] = a.filename
FROM #DBSPACE a
INNER JOIN #LOGSPACE b
ON a.dbname = b.dbname '

IF @dbname IS NOT NULL AND @trheshold IS NULL
BEGIN
 SET @ConsultaFinal = @ConsultaFinal+ ' AND a.dbname = '''+@dbname+''''
END

ELSE IF @dbname IS NULL AND @trheshold IS NOT NULL
BEGIN
 SET @ConsultaFinal = @ConsultaFinal+ ' WHERE (100 - CONVERT(DECIMAL(5,2),A.FreeSpaceMB * 100.0 / a.FileSizeMB)) >= '''++ CONVERT(VARCHAR, @trheshold)+''''
END

ELSE IF @dbname IS NOT NULL AND @trheshold IS NOT NULL
BEGIN
 SET @ConsultaFinal = @ConsultaFinal+ ' AND a.dbname = '''+@dbname+''' WHERE (100 - CONVERT(DECIMAL(5,2),A.FreeSpaceMB * 100.0 / a.FileSizeMB)) >= '''++ CONVERT(VARCHAR, @trheshold)+''''
END

EXEC (@ConsultaFinal)

DROP TABLE #DBSPACE
DROP TABLE #LOGSPACE
GO

Valeu!

Publicado em T-SQL, Uncategorized | Marcado com , , | Deixe um comentário

Query Tricks: Criando Flags com OUTER APPLY

Otimizar queries é uma arte. Existem diversos “truques” para alterar a forma como uma query é executada, e isso sem nem sequer entrar em hints ou assuntos mais avançados. Justamente por isso é sempre válido ter alguns templates em mente para resolver certos tipos de problemas de diferentes formas na busca pela execução mais eficiente. Para o problema de hoje, o objetivo é resolver a seguinte questão (usando a base AdventureWorks2012 como base): para cada cliente (tabela Sales.Customer) identificar através de uma flag “S” ou “N” se ele possui pelo menos uma transação (tabela Sales.SalesOrderHeader). Existem, no geral, três formas distintas de chegar neste resultado, que irei apresentar a seguir juntamente com seus prós e contras:

1- LEFT JOIN e DISTINCT

SELECT
   DISTINCT
   a.CustomerID,
   CASE
      WHEN b.CustomerID IS NOT NULL
      THEN 'S'
      ELSE 'N'
   END AS Flag
FROM
   Sales.Customer a
   LEFT JOIN Sales.SalesOrderHeader b
          ON a.CustomerID = b.CustomerID

Essa é a pior implementação, uma vez que a tabela de vendas é muito maior que a de clientes, obrigando a utilizar um DISTINCT ou um GROUP BY para remover os duplicados que o próprio JOIN incluiu. De fato, a única vantagem desta query é que ela é a mais curta de todas (o que não é lá grande vantagem). A única situação em que ela não seria uma má opção é quando as tabelas são um para um, removendo a necessidade do DISTINCT. Entretanto, como a maioria dos casos (inclusive o do nosso problema atual) envolve tabelas onde um lado é muito maior do que o outro então esta implementação dificilmente será utilizada.

2- EXISTS e UNION

SELECT
   a.CustomerID,
   'S' AS Flag
FROM
   Sales.Customer a
WHERE
   EXISTS(
      SELECT TOP 1
         1
      FROM
         Sales.SalesOrderHeader a1
      WHERE
         a.CustomerID = a1.CustomerID)
UNION ALL
SELECT
   a.CustomerID,
   'N' AS Flag
FROM
   Sales.Customer a
WHERE
   NOT EXISTS(
      SELECT TOP 1
         1
      FROM
         Sales.SalesOrderHeader a1
      WHERE
         a.CustomerID = a1.CustomerID)

Esta é a implementação que provavelmente a maioria deve pensar. Lembrando-se de utilizar o UNION ALL (já que os conjuntos são distintos então não há a necessidade de fazer o SQL checar por duplicados) esta é uma solução bastante eficiente para uma grande gama de casos. Sua maior desvantagem, entretanto, é a repetição de código: caso você decida incluir um novo campo, filtro ou join, a alteração deverá ser feita nas duas queries. E, como nenhum erro ocorrerá caso você esqueça de fazer uma alteração, resultados incorretos serão apresentados. 3- OUTER APPLY

SELECT
   CustomerID,
   CASE
      WHEN b.exist IS NOT NULL
      THEN 'S'
      ELSE 'N'
   END AS Flag
FROM
   Sales.Customer a
   OUTER APPLY(
      SELECT TOP 1
         1 AS exist
      FROM
         Sales.SalesOrderHeader a1
      WHERE
         a.CustomerID = a1.CustomerID) AS b

Finalmente, a solução mais criativa faz uso do OUTER APPLY para fazer as vezes do EXISTS. Basicamente, o que acontece nessa query é que, para cada cliente, uma linha será buscada da tabela de vendas (caso exista alguma). A partir daí é só montar o SELECT como se fosse a query de LEFT JOIN. Em temos de código, essa é uma solução intermediária: sem a duplicação do EXISTS e sem a necessidade do DISTINCT do LEFT JOIN. Porém, algumas condições devem ser observadas para que esta solução seja eficiente: deve existir um índice na tabela da direita que atenda completamente a query. Essa tabela também deve ser grande o suficiente para que os seeks sejam mais eficientes que um simples scan. Por último, a tabela da esquerda não pode ser muito grande (ou deve ter um bom filtro).   Entrando mais a fundo nos detalhes deste tipo de implementação, o OUTER APPLY é um operador que executa seu conteúdo para cada linha da tabela à esquerda. Por isso, a query sempre segue o modelo de fazer um NESTED LOOPS. Tanto é que, forçando a execução a utilizar a PK, vemos o porquê da necessidade de um índice que cubra a query interna: Como pode ser observado, o SQL precisa fazer um index scan e criar um spool no tempdb para poder executar o TOP dentro do NESTED LOOPS. Obviamente, essa é uma péssima solução que gasta espaço no tempdb além de exigir a leitura completa do índice. Outro ponto é justamente o fato de que, como esta solução sempre é implementada com NESTED LOOPS, dependendo do número de registros da tabela à esquerda, a grande quantidade de seeks que o operador deverá fazer para cada cliente pode ser muito custoso frente a um MERGE/HASH JOIN. Ou seja, é preciso analisar com cuidado sua situação antes de decidir qual a melhor opção. Para demonstrar estes pontos, veja a seguir dois exemplos comparando o output do STATISTICS IO, juntamente com o plano de execução das soluções 2 e 3. No primeiro caso, utilizamos a tabela Sales.SalesOrderHeader que possui 31.465 registros: Neste caso o OUTER APPLY não compensa pela baixa quantidade de registros sendo o index scan com o MERGE JOIN uma melhor opção (note como a primeira query precisou de muito menos leituras que a segunda). No segundo caso, utilizamos a Sales.SalesOrderHeaderEnlarged, uma versão maior da tabela com 1.290.065 registros: Aqui o OUTER APPLY é bem mais eficiente, e é bem interessante notar como o próprio SQL decide utilizar a mesma solução para a query do NOT EXISTS, mas ele ainda precisa executar a query do EXISTS para fazer o UNION ALL, necessidade que não existe no caso do OUTER APPLY que resolve tudo no mesmo JOIN (note como a primeira query precisa fazer mais scans que a segunda).   Espero ter mostrado como o operador APPLY pode ser usado de formas diferentes para resolver certas categorias de problemas de maneira mais limpa e eficiente! “E isso é tudo pessoal!”

Publicado em Performance | Marcado com , , | Deixe um comentário

Como podemos diminuir o tempo de Rebuild de índices?

Bom dia pessoal,

Hoje vou falar de um assunto muito importante que é o Index Rebuild (Reconstrução de Índices). Sabemos que, para um bom funcionamento do ambiente, temos que sempre realizar o rebuild dos índices para que as consultas os utilizem e retornem os dados rapidamente.

O mais comum na maioria das rotinas administrativas de rebuild é utilizar cursor ou while para verificar em todos os bancos da instância se há índices fragmentados, mas o processo de coleta é demorado e acaba impactando no tempo de execução da rotina administrativa.

Pensando nisso, resolvi coletar essas informações antes de executar o rebuild para que, no momento de execução, essas informações já estivessem disponíveis. Com isso, diminuímos em torno de 40% o tempo de execução da rotina. Como exemplo, em uma das instâncias que administro, chega a demorar até 3 horas só para coletar essas informações.

 

O vilão disso é uma DMV que coleta as informações sobre a fragmentação dos índices, a sys.dm_db_index_physical_stats. Quando consultamos essa DMV, é comum demorar bastante para retornar o resultado, uma vez que ela irá, no momento em que for chamada, ler todos os índices requisitados para então calcular e retornar os dados. Essa demora aumentará dependendo da quantidade de índices criados nos banco de dados.

 

Para acelerar o processo de execução do rebuild, sugiro que criem um Job para armazenar esse resultado em uma tabela durante um horário menos crítico para que, no momento de execução do reindex, as informações de fragmentação já estejam disponíveis e, consequentemente, o tempo de execução da rotina seja menor.

Vou mostrar como fazer isso, passo a passo.

Bom, vamos colocar as mãos na massa.

1º Iremos criar o banco de dados DBA.

USE [master]
GO

CREATE DATABASE [DBA]
GO

ALTER DATABASE [DBA] SET RECOVERY SIMPLE
GO

2º Vamos criar a tabela que irá armazenar o resultado da consulta.

CREATE TABLE [DBA].[dbo].[DbaInfoIndex](
  [Instancia] [varchar](128) NULL,
  [NomeBase] [varchar](100) NULL,
  [NomeSchema] [varchar](50) NULL,
  [NomeTabela] [varchar](100) NULL,
  [NomeIndice] [varchar](100) NULL,
  [AvgFragmentationPercent] [numeric](5, 2) NULL,
  [FillFactor] [tinyint] NULL,
  [UtilizacaoIndice] [bigint] NULL,
  [UserScans] [int] NULL,
  [UserSeeks] [int] NULL,
  [UserUpdates] [int] NULL,
  [PrimaryKey] [bit] NULL,
  [DataColeta] [datetime] NULL
) ON [PRIMARY]
GO

3º Vamos criar uma procedure que irá realizar essa carga para a tabela DbaInfoIndex. O interessante dessa procedure é que, além de coletarmos as informações de fragmentação, iremos também armazenar as informações de utilização dos índices, Fill factor, se é índice Clustererd ou Non-Clustered. Lembrando que essa consulta pode demorar. Como normalmente o processo de rebuild ocorre aos finais de semana, você pode rodar um dia antes. Além disso, essa procedure pode ser executada a partir de qualquer base de dados da sua instância.

Nessa querie eu coleto informações de todos os índices e armazeno esse resultado em uma tabela, pois utilizo esses dados para gerar relatórios de sua utilização ao longo do mês e consigo mensurar se preciso fazer o rebuild mais vezes desses índices, além de analisar o impacto da utilização desses índices em nosso ambiente.

CREATE PROCEDURE [dbo].[UspMonitoraIndices]
AS
  /*
  Criado: Amaury Germano Jr.
  Data 19/02/2015
  Obj: Criado para Armazenar informações de fragmentação e utilização dos índices
  */
  SET NOCOUNT ON

  CREATE TABLE #ListaDatabases
  (
      id int IDENTITY PRIMARY KEY,
      NAME varchar(300)
  )

  INSERT INTO #ListaDatabases
  (
     NAME
  )
     SELECT
        NAME
     FROM
        master.sys.sysdatabases s
     WHERE
        dbid > 4 --Bancos de sistema
        AND DATABASEPROPERTYEX(NAME, 'Status') = 'ONLINE'
     ORDER  BY
        1

  SELECT
     *
  FROM
     #ListaDatabases

  DECLARE
     @id int,
     @cnt int,
     @Comando nvarchar(max),
     @NomeBanco varchar(300);

  SELECT
     @id = 1,
     @cnt = MAX(id)
  FROM
     #ListaDatabases

  WHILE @id <= @cnt
    BEGIN
      SELECT
         @NomeBanco = NAME
      FROM
         #ListaDatabases
      WHERE
         id = @id

      SET @Comando =
'SELECT
  InstanceName = ''' + @@SERVERNAME + '''
  ,DatabaseName = ''' + @NomeBanco + '''
  ,SchemaName = sc.name
  ,TableName = t.name
  ,IndexName = i.name
  ,ROUND(avg_fragmentation_in_percent,2) as ''%Fragmentation''
  ,fill_factor as ''%FillFactor''
  ,[Usage] = (user_seeks + user_scans + user_lookups)
  ,user_seeks
  ,user_scans
  ,user_lookups
  ,is_primary_key
  ,GETDATE() AS DataColeta
  FROM
    [' + @NomeBanco + '].sys.dm_db_index_usage_stats s
    INNER JOIN [' + @NomeBanco + '].sys.indexes i
            ON s.[object_id] = i.[object_id]
           AND s.index_id = i.index_id
    INNER JOIN [' + @NomeBanco + '].sys.dm_db_index_physical_stats(
       DB_ID(''' + @NomeBanco + '''),
       null,
       null,
       null,
       null
    ) a
            ON s.[object_id] = a.[object_id]
           AND s.index_id = a.index_id
    INNER JOIN [' + @NomeBanco + '].sys.tables t
            ON i.object_id = t.object_id
    INNER JOIN [' + @NomeBanco + '].sys.schemas sc
            ON t.schema_id = sc.schema_id
WHERE
  i.name IS NOT NULL -- HEAP INDEX
  and t.Is_Ms_Shipped = 0
ORDER BY
  TableName asc'

      INSERT INTO [dbo].[DbaInfoIndex]
      EXEC (@Comando)

      PRINT '------------COLETA DA BASE DE DADOS [' + @NomeBanco + '] REALIZADA COM SUCESSO'

      SET @id = @id + 1;
    END

  DROP TABLE #ListaDatabases

  SET NOCOUNT OFF
GO

4º Para criamos o Job para executar a procedure UpMonitoraIndices criada anteriormente, veja a seguir um passo a passo para quem tem pouco contato com o SQL Server:

Colocando o nome do JOB.

Criando o “Step”de execução, onde ficará o comando que será executado no SQL.

Preencha o “Step 1 “, conforme abaixo:

Clique em “New” para criar o agendamento.

Criamos o agendamento da rotina para executar aos sábados às 20h00, já que o rebuild executa na madrugada de domingo. Mas você deve configurar um horário de acordo com sua janela de manutenção.

 

Após criarmos o job, teremos as informações completas sobre utilização dos índices e você pode utilizar esses dados coletados para colocar em sua rotina de rebuild de índices, e você vai ver a diferença do tempo de execução, que deverá diminuir bastante.

Em breve, irei disponibilizar uma rotina de manutenção de índices que utilizo.

Valeu

 

Publicado em Performance | Marcado com | Deixe um comentário

sqlsaturday424

Fala pessoal,

As inscrições para o SQL Saturday 424 estão abertas através do link:

https://www.sqlsaturday.com/424/eventhome.aspx .

Esse é um evento organizado pelo PASS e pela NGR Solutions, essa edição será realizada em São Paulo no dia 26 de setembro de 2015, na UNIP Tatuapé, Rua Antônio Macedo, 505 – Parque São Jorge, Tatuapé – São Paulo – SP, São Paulo, 03087-040, Brasil, fica ao lado do Parque São Jorge, bem fácil de chegar.

Recomendo muito ir nesse evento fantástico e que reúne os melhores profissionais DBA em que eles palestram e passam dicas importantíssimas para o nosso dia-a-dia.

Fui ano passado e gostei bastante dos temas das palestras ministradas, e ainda reencontrei grandes amigos que trabalhei e aprendi ao longo desses poucos anos como DBA, além de conhecer os palestrantes que nos ajudam tanto com os posts em blogs.

Já estava me esquecendo, isso tudo tem um custo de R$ 0,00, sim de graça!! Então, porque não ir??

*Obs. Tem uma taxa de R$ 30,00 para ajudar no Coffee Break, porém o pagamento é OPCIONAL.

Valeu

Abraço

Publicado em por Amaury Germano Jr | Deixe um comentário

 

O TechEd é um evento que ocorre em diferentes países do mundo e é organizado pela Microsoft, tendo como foco palestras técnicas e apresentações de novas tecnologias e produtos. Depois de 4 anos, o evento estará de volta ao Brasil trazendo os últimos lançamentos da companhia. Contando com os principais nomes da comunidade brasileira e estrangeira (com tradução simultânea), é uma ótima oportunidade tanto para aprendizado quanto para o networking pessoal.

O evento já com data definida para os dias 20 e 21 de maio de 2015, porém ainda não sabemos sobre o local e valores para participar do evento. Assim que surgirem mais informações, iremos postando para mantê-los informados!!!

teched15

Até a próxima!

Publicado em por Nilson Almeida | Deixe um comentário

O que é BI?

 

O termo Business Intelligence (BI) pode ser traduzido como inteligência de negócio. Criado pelo Gartner Group que o definiu como “um termo abrangente que inclui as aplicações, infraestrutura, ferramentas e melhores práticas que permitem o acesso e análises de informações para melhorar e otimizar as decisões e desempenho.”.

 

O BI pode ser usado para ajudar na tomada de decisão das empresas, partindo desde variáveis do nível operacional até o nível estratégico. Para que isso aconteça, é necessário a transformação de dados brutos em informações significativas e úteis a fim de analisar o negócio, como se fôssemos lapidar um diamante bruto.

No início as fontes de dados são coletadas dentro da própria empresa, em diferentes sistemas: ERP, Site, Arquivos e etc (Dados Internos). Depois, buscamos fontes externas como aspectos econômicos e culturais das diferentes regiões e países, vendas de produtos, crescimento da população e até informações dos concorrentes (Dados Externos). Quando os dados externos e internos são combinados, eles podem fornecer um cenário mais completo e assertivo nas decisões a serem tomadas pelas empresas. 

Todas essas informações são armazenadas no Data Warehouse (DW), onde ocorre a integração das diferentes fontes de dados criando um repositório central que suportará todos os processos de BI. Com o DW será possível analisar dados históricos, atuais e efetuar projeções.

Um dos objetivos das ferramentas de BI é ser amigável, com os usuários proporcionando uma fácil interpretação do grande volume dados através de relatórios, gráficos bem elaborados, dashboards, mineração de dados (Data Mining), análises preditivas e etc.

Nos dias atuais o BI não está presente somente em grandes empresas, mas também nas empresas de pequeno e médio porte para ajudar a otimizar o trabalho e até mesmo trazer previsões de crescimento. Empresas de menor porte não precisam de ferramentas que requerem um alto investimento, incluindo diversos relatórios e gráficos. Existem soluções gratuitas (algumas inclusive são open source) com muitos profissionais qualificados que podem implementar e ajudar nessa transformação.

 

Veremos nas próximas publicações mais particularidades de BI e DW.

 

Coloque suas dúvidas e deixe sugestões através dos comentários!

 

Até a próxima!

 

Publicado em Business Intelligence | Marcado com , | Deixe um comentário