Script para tamanho de uma database (com considerações)

O SQL Server disponibiliza várias maneiras de verificarmos o tamanho atual de uma determinada database. Vale lembrar, neste ponto, que o conceito de tamanho de uma database dependerá do ponto vista. Mas como assim? Um banco de dados sql server é composto de pelo menos dois arquivos: um arquivo de dados MDF e um arquito de log de transação LDF. Desta forma, ao analisar o tamanho você precisa definir se levará em conta o LDF ou não. Particularmente, procuro gerenciar cada arquivo em separado, porque os meus dados efetivamente estão salvos no arquivo de dados (MDF). Outro ponto importantíssimo é que o tamanho atual de um arquivo MDF (dados) não significa que é o tamanho máximo que você configurou para ele. Como assim? digamos que você criou uma database com 3MB de dados e 1MB de log. Ao consultar, o SQL te informará, por exemplo, 2.25MB para o arquivo de dados. Desta forma, o tamanho atual de um arquivo MDF é a soma da quantidade de espaço que o sql já reservou em disco para ele. Ok.

Apresento alternativas abaixo de você verificar o tamanho de uma database.

1) Recuperando o tamanho atual dos arquivos de log e mdf
SELECT df.name, df.type, (df.size*8)/1024.0 size FROM sys.database_files df;

No item 1 você pode verificar que o sql retorna o tamanho atualmente reservado em disco para o MDF. O log, entretanto, é retornado sempre com o tamanho máximo que você configurou.

2) Recuperando o tamanho atualmente reservado para o MDF e o total de espaço já utilizado dentro do tamanho reservado
WITH MyCte
AS
(
select
(select (SUM(ps.reserved_page_count)*8)/1024.0 from sys.dm_db_partition_stats ps) Total_Utilizado
, (select (SUM(size*8))/1024.0 from sys.database_files where type = 0) Total_Reservado
)
select
m.Total_Reservado
, m.Total_Utilizado
, (m.Total_Reservado – m.Total_Utilizado) Espaco_Livre
from MyCte m

O Total_reservado traz o tamanho atualmente alocado pelo banco no disco, referente ao mdf. O total_utilizado é, digamos, uma quantidade já comprometida do total_reservado. O total reservado inclui páginas de dados e páginas de índices.

3) Usando uma sp de sistema
exec sp_spaceused

A sp é bem mais simples de usar. Entretanto, é necessário compreender seu resultado. Veja a explicação:
1ª linha da sp_spaceused:
database_name database_size unallocated space
DBLoja 3.25 MB 0.77 MB

database_size = total reservado em disco atualmente pelo MDF + tamanho máximo configurado para o LDF.
unallocated space = espço livre dentro do total reservado em disco atualmente pelo MDF.

2ª linha da sp_spaceused:
reserved data index_size unused
1512 KB 664 KB 760 KB 88 KB

reserved = total reservado em disco atualmente pelo MDF
data = total de páginas de dados armazenadas
index_size = total de páginas de índices armazenadas
unused = quantidade de espaço livre dentro do reserved

A soma de data + index_size + unused formará o reserved.

Script para dropar todas as tabelas de uma database

Aí galera, segue um script que exclui todas as tabelas de uma database de um servidor Sql Server 2008. Bom proveito.

USE DBTest
go

SET NOCOUNT ON;
DECLARE @Tabela TABLE
( Seq INT,
Id INT,
Name sysname);
DECLARE @Loop INT, @Qt INT;

– Enquanto existir foreignkey Excluir os objetos que não são referenciados
WHILE (SELECT COUNT(*) FROM sys.foreign_keys) > 0
BEGIN
DELETE @Tabela;
SET @Loop = 1;
INSERT INTO @Tabela
SELECT
ROW_NUMBER() OVER(ORDER BY t.Name) AS Seq,
t.object_id AS Id,
t.name
FROM sys.tables t;
SET @Qt = @@ROWCOUNT;

WHILE @Loop <= @Qt
BEGIN
DECLARE @sql VARCHAR(100) = '';
IF NOT EXISTS( SELECT 1 FROM sys.foreign_keys fk
WHERE fk.referenced_object_id =
(SELECT Id FROM @Tabela o WHERE o.Seq = @Loop) )
BEGIN
SELECT @sql = 'DROP TABLE ' + o.name
FROM @Tabela o
WHERE o.Seq = @Loop;
PRINT 'Droping ' + @sql;
EXECUTE(@sql);
END;

SET @Loop = @Loop +1;
END;

END;

– Por garantia executa mais um drop geral, pois pode restar tabelas
exec sp_MSforeachtable 'DROP TABLE ?; ' ;
GO

Conceder permissão para o profile

O Profiler é uma ferramenta poderosa à disposição do dba, mas ela também pode ser de grande utilidade para desenvolvedores de sistemas. É claro que estou pensando em ambientes de desenvolvimento e homologação (jamais produção). Desta forma, o profiler pode auxiliar os desenvolvedores a monitorarem as queries e stored procedures desenvolvidas nas aplicações.

Segue a instrução para conceder permissão de execução do profile

// Você deve estar no banco de dados master, obrigatoriamente
USE master
GO

GRANT ALTER TRACE TO [usuario];

Última reinicialização do SQL Server

De vez em quando eu preciso consultar qual a data da última reinicialização do SQL Server. O método antigo é o de consultar a data de criação da database TempDB. Entretanto, no SQL 2008 temos uma DMV que pode ser utilizada para esse fim: sys.dm_os_sys_info. Vejamos como:

1) Pesquisando criação da TempDB
SELECT
d.create_date,
DAY(GETDATE()) – DAY(d.create_date) DaysUpTime
FROM sys.databases d
WHERE d.name like ‘tempdb’

2) Usando a DMV sys.dm_os_sys_info
select
i.sqlserver_start_time,
DAY(GETDATE()) – DAY(i.sqlserver_start_time) DaysUpTime
from sys.dm_os_sys_info i;

Quantidade de sessões por database

Às vezes quando precisamos fazer alguma manutenção em um servidor SQL, é necessário verificarmos quais usuários estão conectados ou quais databases estão sendo utilizadas. Uma forma simples e rápida de verificar a utilização do servidor é simplesmente identificar quais databases estão sendo acessadas naquele momento. Assim, apresento duas formas de fazermos tal verificação:

1) Usando sysprocesses (deprecado)
SELECT DB_NAME(p.dbid) db, COUNT(*) quantity
FROM master.dbo.sysprocesses p
WHERE p.spid > 50
group by DB_NAME(p.dbid)
ORDER BY 1

2) Usando DMV
SELECT db_name(l.resource_database_id) db, COUNT(*) quantity
FROM sys.dm_tran_locks l
GROUP BY db_name(l.resource_database_id)
ORDER BY 1

Using DMV to identify members roles

Some times is necessary retrieve a list of members of the roles of a database. Doing this is simple, using the DMV sys.database_role_members. Below follow an example of how to do this.

USE dbtest
GO

SELECT
roles.role_id
, roles.role_name
, roles.member_id
, principals.name AS member_name
FROM
(
SELECT
principals.name AS role_name
, roles.role_principal_id AS role_id
, roles.member_principal_id AS member_id
FROM sys.database_principals principals
join sys.database_role_members roles
ON principals.principal_id = roles.role_principal_id
) roles
JOIN sys.database_principals principals ON roles.member_id = principals.principal_id
ORDER BY member_name

Retrieving last backups of the databases

http://www.sqlservercentral.com/blogs/adeilson_brito/archive/2010/07/14/retrieving-last-backups-of-the-databases.aspx

Alguns cuidados com o Data Collection

Após um período de lab, testando a nova feature do sql server 2008 chamada Data Collection, acabei por configurar o recurso no ambiente de produção. Para minha surpresa apareceram detalhes que não consegui visualizar na fase de testes, e que requerem uma atenção dos DBAs:

1) Crescimento da MSDB: É sábido que a base de dados data warehouse, criada e configurada para armazenar a coleta do data collection, irá crescer de forma considerável (até assustadora), chegando facilmente a um tamanho médio de 5GB. Mas além de crescer a database do data collection, a db de sistema MSDB terá um incremento na sua taxa de crescimento. No meu ambiente, por exemplo, o MSDB que crescia quase nada, passou a crescer 9MB por dia. É bom policiar isso de perto.

2) Bug no step 2 do job collection_set_3_upload: É isso mesmo. Após configurar o data collection, os jobs são gerados automaticamente para realizar os trabalhos de coleta e upload para a base. Há um bug, inclusive comentado em sites de comunidade, que ocorre no job collection_set_3_upload. O job de forma aleatória acaba falhando, não sendo executado o step 2. Apesar disso, não se assuste. Haverá poucas falhas e isso não ocorre em todos os servidores (até isso é aleatório). Há um hot fix acumulativo para resolver esse problema para quem tem o sp1 do sql 2008 instalado, mas a própria microsoft recomenda que seja esperado o sp2. Então, minha recomendação é: tenha paciência com as falhas aleatórias (elas não impactam na coleta) e espere o SP2 do sql server 2008 (se ele vier neh, pois o R2 ta na área).

3) Report Disk Usage: esse relatório aponta, dentre outras coisas, a média de crescimento diário em MB das databases. Mas tome cuidado com essa informação: o crescimento é calculado com base nas operações de growth do banco, isto é, para cada vez que o arquivo de dados ou log sofre crescimento. Acho que isso não seja uma boa informação para os DBAs, pois temos que acompanhar diariamente o quanto de espaço dos arquivos do banco está alocado, de modo a prevermos quando ocorrerá o próximo growth. Normalmente faço isso com base no crescimento do uso de extents de cada arquivo (vou publicar uma matéria em breve ilustrando como fazer um base line de crescimento).

Bem, há outras coisas, mas essas são as que mais me chamaram a atenção. Apesar de tudo, estou usando o data collection, porque de fato ele é mais leve que o uso do perfmon, mais fácil e disponibiliza bons recursos de gerenciamento.

Reciclando o arquivo Error Log do SQL Server 2008

A cada restart do servidor sql um novo arquivo de log de erros é criado e o que era corrente é renomeado para 1, o antigo 1 para 2 e assim por diante. Se o servidor ficar muito tempo sem reiniciar o arquivo de log pode crescer demasiadamente e a sua consulta, via interface gráfica por exemplo, pode ser demorada, podendo causar um overhead no server. O ideal é provocar manualmente, a cada período de tempo x, o encerramento do ciclo do arquivo corrente, criando um novo arquivo de error log, como se fosse um restart do servidor. Essa é, inclusive, uma manutenção que o DBA deve fazer periodicamente.

Para tanto, use:
dbcc errorlog;
ou
sp_cycle_errorlog;

Nota: o período para encerrar o tal ciclo depende da necessidade de cada empresa, considerando também a necessidade de se manter o mais antigo arquivo de log, por padrão o de número 6, já que, por default, o sql server mantém 6 arquivos de log, nomeados de 1 a 6, além do corrente. Normalmente o padrão de período vai de 1 semana a 15 dias. Particularmente uso 10 dias.

Um site só de vídeos do SQL Server e outros produtos MS

Segue um site interessantíssimo com vídeos do SQL Server e de outros produtos da MS:

http://www.sqlshare.com

Será necessário criar uma conta, mas é free.