“Por que valores nulos ocupam espaço e como fazer para não ocupar?”

Primeiro, um valor nulo é um valor, não é a mesma coisa que um valor em branco.

Segundo, podemos otimizar o espaço utilizado pelas colunas de valores nulos utilizando a cláusula SPARSE, porém vale verificar se:

• quando utilizarmos sparse numa coluna e ela receber um valor esse irá receber um byte a mais;
• quando o valor for nulo, esse não irá ocupar espaço;
• se a coluna for de tamanho fixo ou precisão dependerá de quatro bytes a mais;
• se a coluna for de tamanho variável isso tomará dois bytes a mais.

Esse recurso não pode ser utilizado nas condições abaixo:

• colunas com as propriedades identity ou rowguidcol;
• colunas do tipo text, ntext, timestamp, geometry, geography ou em tipos definidos pelo usuário;
• varbinary(max) com a propriedade filestream;
• colunas computadas;
• colunas que sejam parte de um clustered index ou primary key.

Para fazer o teste criei duas tabelas com duas colunas, sendo que uma coluna de cada tabela aceita nulo e apenas a coluna da primeira tabela é criada com a opção sparse. Veja o script de criação abaixo:

CREATE table TesteE (
    cdteste int PRIMARY KEY ,
    nmteste char(200) SPARSE NULL)
GO
CREATE table TesteF (
    cdteste int PRIMARY KEY ,
    nmteste char(200)  NULL)

Agora para inserir dados utilizei o SQL Data Generator da RedGate para gerar uma carga de 100 registros na primeira tabela, sendo que na coluna sparse marquei 50% de campos nulos.

Para a carga da segunda tabela, foi feita uma importação da primeira tabela, isso para ter certeza que a quantidade de bytes na coluna alvo da pesquisa seria a mesma quantidade que a da primeira tabela.

Depois de verificar que ambas as tabelas possuem o mesmo conteúdo, podemos validar o espaço ocupado por cada tabela.

Como podemos visualizar na imagem, podemos concluir que tivemos vantagem com a coluna SPARSE otimizando o espaço.

Entretanto, recomendo uma análise muito criteriosa antes de utilizar esse recurso.

 Abraço, Rodrigo

Pessoal,

Algumas vezes precisamos gerar um backup de todos os bancos de dados, sem utilizar Maintenance Plans ou até mesmo sem acesso ao Management Studio.

Se passarem por alguma situação similar a essa, o script abaixo poderá ser útil.

USE Master

DECLARE @lComando VARCHAR(8000)

SELECT  @lComando = COALESCE(@lComando + 'Backup Database ' + [Name] +
                             ' To Disk = ''D:BackupDBSQLServer2008' +
                             [Name] +
                             '.bak'' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, 

                              NOUNLOAD, COMPRESSION;' +
                             CHAR(13), '')
FROM    Master..Sysdatabases
WHERE   [Name] NOT IN ( 'tempdb' ) AND
        DATABASEPROPERTYEX([Name], 'Status') = 'online'

PRINT @lComando

--Execute(@lComando)

Esse script consulta a “Sysdatabases” e para cada item encontrado gera uma string com o comando de backup, logo eu desconsiderei na clausula “where” os itens existente no “tempdb”.

Da forma como o script foi publicado ele irá imprimir o conteúdo na tela, para executar o backup tire o comentário da ultima linha.

Vale ressaltar que esse comando de backup esta com os parâmetros pertinentes a minha necessidade, antes de executá-lo recomendo avaliar o caminho do backup e os parâmetros passados pelo comentado.

Abraço, Rodrigo

Na última semana um DBA me questionou como criar um script para listar os backups executados, a ideia dele é criar um script que gera essa lista periodicamente.

Então nesse post vou descrever como criar um script para resolver esse problema. Para iniciar o assunto vou apresentar duas tabelas do MSDB que serão importantes para a criação deste script.

 

1.     backupset;

2.     backupmediafamily.

 

A primeira tabela diz respeito ao registro dos conjuntos de backup, cada linha de retorno corresponderá a um backup, para esse script em especial vamos prestar atenção nas colunas: Type, backup_start_date e media_set_id.

• Type: indica o tipo de backup e abaixo segue os tipos de backups possíveis. É importante salientar que é possível o valor nulo:
  • D = Full Backup;
  • I = Differential;
  • L = Log;
  • F = File/Filegroup;
  • G = Differential file;
  • P = Partial;
  • Q = Differential partial.
• backup_start_date: o retorno deste campo é um datetime com o registro da data e horário em que foi realizado o backup;
• media_set_id: identificador do número da mídia de backup.

A segunda tabela refere-se ao registro das famílias de backups, desta tabela vamos utilizar o campo identificador media_set_id que servirá para fazer uma junção com a tabela backupset.

Além dessas duas tabelas utilizaremos a sysdatabases que pertence ao banco de dados MASTER. Será através desta tabela que descobriremos os bancos de dados existentes.

Agora que já estamos familiarizados com as principais consultas que vamos executar, podemos analisar a consulta abaixo. A mesma apresentará os últimos backups executados dos databases existentes.

SELECT  A.database_name [Banco de dados] ,
        CASE WHEN a.type = 'D' THEN 'Full'
             WHEN A.type = 'I' THEN 'Differential'
             WHEN A.type = 'L' THEN 'Log'
             WHEN a.type = 'F' THEN 'File/Filegroup'
             WHEN a.type = 'G' THEN 'Differential - file'
             WHEN A.type = 'P' THEN 'Partial'
             WHEN A.type = 'Q' THEN 'Differential partial'
        END AS [Tipo de backup] ,
        MAX(A.backup_start_date) [Data do Backup]
FROM    msdb..backupset AS A
        LEFT JOIN msdb..backupmediafamily AS B
        ON A.media_set_id = B.media_set_id
WHERE   A.database_name IN ( SELECT name
                             FROM   sysdatabases c )
                             GROUP BY A.database_name, A.type

O resultado desta consulta será algo similar à imagem abaixo. Ressalto que a imagem que segue é uma representação do meu ambiente.

Apesar desse script ser muito útil, uma nova formatação do mesmo poderá facilitar o diagnóstico de falha de algum backup. Por exemplo, se o ambiente possuir muitos bancos de dados, será difícil perceber a ausência de algum backup.

Para isso, podemos reformular a consulta buscando todos os bancos de dados existentes na sysdatabases que tenham registros na backupset.

Vejamos o script abaixo:

SELECT  A.name [Banco de dados] ,
        CASE WHEN B.type = 'D' THEN 'Full'
             WHEN B.type = 'I' THEN 'Differential'
             WHEN B.type = 'L' THEN 'Log'
             WHEN B.type = 'F' THEN 'File/Filegroup'
             WHEN B.type = 'G' THEN 'Differential - file'
             WHEN B.type = 'P' THEN 'Partial'
             WHEN B.type = 'Q' THEN 'Differential partial'
        END AS [Tipo de backup] ,
        MAX(B.backup_start_date) [Data do Backup]
FROM    master..sysdatabases A
        LEFT JOIN msdb..backupset B
        ON A.name = B.database_name
        LEFT JOIN msdb..backupmediafamily AS C
        ON B.media_set_id = C.media_set_id
GROUP BY A.name ,
        B.type

O retorno desta consulta será algo similar à imagem abaixo:

Desejo que esse script seja útil a todos que necessitam automatizar o monitoramento dos backups.

 

Abraço, Rodrigo