[SQL Server] Script para deletar todos os registros de uma database

Segue um script que me tem sido muito útil quando estou testando e desenvolvendo um novo projeto de banco de dados. Ele tem um único objetivo: excluir todos os registros de todas as tabelas de uma determinada base. Basta você alterar o comando USE, de modo a apontar para o banco que você deseja. Apenas um detalhe: este script considera apenas o uso do esquema DBO. Portanto, se sua base utiliza outros esquemas, além do DBO, então você precisará ajustar.

use DBTest
GO

SET NOCOUNT ON;
DECLARE @sql varchar(4000);

— 0 – Recuperar a lista de constraints
DECLARE @tbl Table ( tabela varchar(500), restricao varchar(500) );

— 1 – Desabilitar constraints
insert INTO @tbl
SELECT
t.name, k.name
FROM sys.foreign_keys k
JOIN sys.tables t ON k.parent_object_id = t.object_id;
WHILE (select count(*) from @tbl) > 0
begin
select top 1 @sql = ‘ALTER TABLE dbo.’ + t.tabela + ‘ nocheck constraint ‘ + t.restricao + ‘;’
from @tbl t;
delete TOP(1) FROM @tbl;
EXEC(@sql);
END;

— 2 – Truncar
EXEC sys.sp_MSforeachtable ‘DELETE FROM ?’;

— 3 – Reabilita as constraints
insert INTO @tbl
SELECT
t.name, k.name
FROM sys.foreign_keys k
JOIN sys.tables t ON k.parent_object_id = t.object_id;
WHILE (select count(*) from @tbl) > 0
begin
select top 1 @sql = ‘ALTER TABLE dbo.’ + t.tabela + ‘ check constraint ‘ + t.restricao + ‘;’
from @tbl t;
delete TOP(1) FROM @tbl;
–print @sql;
EXEC(@sql);
END;


Script: Relatório diário via e-mail de instâncias SQL Server

Monitorar instâncias do SQL Server é uma tarefa que requer o uso de vários recursos, como DMVs, log de erros, contadores de performance, SQL Traces, etc; é um verdadeiro canivete suíço. Na busca por facilitar o meu trabalho,  eu incluí na minha estratégia de monitoramento diário uma stored procedure que é executada todos os dias, no início da manhã, coletando informações que considero vitais. Essa SP coleta os dados e gera um relatório em formato HTML, enviando-o para minha caixa de e-mail. Assim, quando chego na empresa a primeira coisa que faço é conferir os relatórios enviados para o meu e-mail. Além disso, essa SP também pode ser usada para capturar uma imagem de como está a instância naquele momento. A stored procedure tem apenas um parâmetro: @ExibirApenasHtm, cujo valor default é 0. Se você executar a SP passando o valor 1 então, ao invés de enviar um e-mail, será exibida uma string contendo o HTML do relatório. Copie essa string e cole no seu editor HTML preferido. Fique à vontade para adaptar a SP conforme as suas necessidades. Ela é compatível apenas com o SQL Server 2008 e versões superiores. ATENÇÃO: para usar o recurso de e-mail na stored procedure é necessário configurar o Database Mail.

 

Segue alguns trechos que o relatório exibe:

image

image

image

image

Exemplo de como executar a stored procedure enviando o relatório para uma conta de e-mail:

use master

go

EXEC dbo.uspRelatorioDiario;

 

Exemplo de como executar a stored procedure gerando apenas a string contendo o HTML do relatório:

use master

go

EXEC dbo.uspRelatorioDiario 1;

 

Segue nas linhas abaixo o código fonte da procedure. Se você preferir, pode baixá-la aqui.

USE DBAdmin
GO

/* SP para ser executada diariamente. Exibe resumo da situação atual do SGDB. */
if OBJECT_ID(‘dbo.uspRelatorioDiario’) IS NOT NULL
DROP PROCEDURE dbo.uspRelatorioDiario;
GO

CREATE PROCEDURE dbo.uspRelatorioDiario @ExibirApenasHtml BIT = 0
AS
SET LANGUAGE US_ENGLISH;

SET NOCOUNT ON;

— *** Variáveis
DECLARE @vSubject NVARCHAR(255) = ‘Relatório Diário do SQL Server: ‘+@@SERVERNAME;
DECLARE @vBody AS NVARCHAR(MAX) = ”;

— *** Parte 1: Informações de configuração da instância
IF OBJECT_ID(‘tempdb.dbo.#Tabela’) IS NOT NULL
DROP TABLE #Tabela;

DECLARE @vOnline_Since AS NVARCHAR (10) = ”;
DECLARE @vUptime_Days AS INT = 0;

SELECT
@vOnline_Since = CONVERT(NVARCHAR(10), DB.sqlserver_start_time,103)
, @vUptime_Days = DATEDIFF(DAY,DB.sqlserver_start_time,GETDATE())
FROM
sys.dm_os_sys_info DB;

SELECT
SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’) AS netbios_name
,@@SERVERNAME AS server_name
,SERVERPROPERTY(‘EDITION’) AS edition
,SERVERPROPERTY (‘ProductVersion’) AS version
,SERVERPROPERTY (‘ProductLevel’) AS [level]
,@vOnline_Since AS online_since
,@vUptime_Days AS uptime_days
INTO #Tabela;

SET @vBody =

<h3>Informações da Instância</h3>
<table border=”1″ cellpadding=”2″>
<tr>
<th>Nome NetBIOS</th>
<th>Nome da Instância</th>
<th>Edição</th>
<th>Versão</th>
<th>Level</th>
<th>Online desde</th>
<th>Qtde de dias online</th>
</tr>’;
SET @vBody = @vBody +
(
select
‘<tr>’+
‘<td>’+CONVERT(NVARCHAR,t.netbios_name)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,server_name)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,edition)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,version)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,level)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,online_since)+'</td>’+
‘<td>’+CONVERT(NVARCHAR,uptime_days)+'</td>’+
‘</tr>’
from dbo.#Tabela t
)
SET @vBody = @vBody + ‘</table>’;

— *** Parte 2: Log de error do SQL
DECLARE
@Qt INT = 0,
@Loop INT = 1;

DECLARE @LogSQL1 TABLE
(    Seq INT IDENTITY(1,1),
LogDate DATETIME,
ProcessInfo VARCHAR(50),
Text VARCHAR(4000)
);
INSERT INTO @LogSql1 exec sp_readerrorlog;

— Tabela para somente erros
DECLARE @LogSQL2 TABLE
(    Seq INT IDENTITY(1,1),
LogDate DATETIME,
ProcessInfo VARCHAR(50),
Text VARCHAR(4000)
);
INSERT INTO @LogSQL2
SELECT TOP 30
LogDate, ProcessInfo, Text
FROM @LogSQL1 l
WHERE l.Text LIKE ‘%erro%’
ORDER BY 1 DESC;
SET @Qt = @@ROWCOUNT;
SET @vBody = @vBody +

<br><br>
<h3>Últimos 30 registros contendo a palavra “erro” no Log de Erros do SQL Server</h3>
<table border=1 cellpadding=2>
<tr>
<th>Data do Log</th>
<th>Processo</th>
<th>Texto</th>
</tr>
‘;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+CONVERT(VARCHAR,LogDate)+'</td>’+
‘<td>’+CONVERT(VARCHAR,ProcessInfo)+'</td>’+
‘<td>’+CONVERT(NVARCHAR(4000),Text)+'</td>’+
‘</tr>’
FROM @LogSQL2 t
WHERE t.Seq = @Loop
);
SET @Loop = @Loop +1;
END;
SET @vBody = @vBody + ‘</table>’;

— últimos 30 registros
DECLARE @LogSQL3 TABLE
(    Seq INT IDENTITY(1,1),
LogDate DATETIME,
ProcessInfo VARCHAR(50),
Text VARCHAR(4000)
);
INSERT INTO @LogSQL3
SELECT TOP 30
LogDate, ProcessInfo, Text
FROM @LogSQL1 l
ORDER BY 1 DESC;
SET @Qt = @@ROWCOUNT;
SET @Loop = 1;
SET @vBody = @vBody +

<br><br>
<h3>Últimos 30 registros do Log de Erros do SQL Server</h3>
<table border=1 cellpadding=2>
<tr>
<th>Data do Log</th>
<th>Processo</th>
<th>Texto</th>
</tr>
‘;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+CONVERT(VARCHAR,LogDate)+'</td>’+
‘<td>’+CONVERT(VARCHAR,ProcessInfo)+'</td>’+
‘<td>’+CONVERT(VARCHAR(4000),Text)+'</td>’+
‘</tr>’
FROM @LogSQL3 t
WHERE t.Seq = @Loop
);
SET @Loop = @Loop +1;
END;
SET @vBody = @vBody + ‘</table>’;

— *** Parte 3: Tamanho dos discos
DECLARE @vFixed_Drives_Free_Space_Table AS TABLE
(    drive_letter VARCHAR (5),
free_space_mb BIGINT,
Seq INT IDENTITY(1,1));
INSERT INTO @vFixed_Drives_Free_Space_Table
(
drive_letter
,free_space_mb
)
EXEC master.dbo.xp_fixeddrives;

SET @Qt = @@ROWCOUNT;
SET @Loop = 1;

SET @vBody = @vBody +

<br><br>
<h3>Espaço livre nas unidades de disco</h3>
<table border=1 cellpadding=2>
<tr>
<th>Unidade</th>
<th>Espaço Livre (MB)</th>
</tr>
‘;

WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+CONVERT(VARCHAR,drive_letter)+'</td>’+
‘<td>’+REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, free_space_mb), 1)), 4, 15))+'</td>’+
‘</tr>’
FROM @vFixed_Drives_Free_Space_Table t
WHERE t.Seq = @Loop
);
SET @Loop = @Loop +1;
END;

SET @vBody = @vBody + ‘</table>’;

— *** Parte 4: tamanho das databases
CREATE TABLE #Tamanhos
(
Seq INT IDENTITY,
Banco VARCHAR(50),
ArquivoDeDados_EspacoReservadoEmDisco_MB DECIMAL(15,2),
ArquivoDeDados_EspacoUsado_MB DECIMAL(15,2),
ArquivoDeDados_EspacoLivre_MB DECIMAL(15,2)
);

EXEC sp_MSforeachdb ‘USE ?
INSERT INTO #Tamanhos
(
Banco
, ArquivoDeDados_EspacoReservadoEmDisco_MB
, ArquivoDeDados_EspacoUsado_MB
, ArquivoDeDados_EspacoLivre_MB
)
SELECT
DB_NAME() Banco
, CAST(a.EspacoReservadoEmDisco AS DECIMAL(15,2)) EspacoReservadoEmDisco_MB
, CAST(a.EspacoUsado AS DECIMAL(15,2)) EspacoUsado_MB
, CAST(a.EspacoReservadoEmDisco – a.EspacoUsado AS DECIMAL(15,2)) EspacoLivre_MB
FROM
(
select
(select SUM(ps.reserved_page_count)/128.0 from sys.dm_db_partition_stats ps) EspacoUsado
, (select SUM(size/128.0) from sys.database_files where type IN (0,2,4)) EspacoReservadoEmDisco
) a’;

DECLARE @Tamanhos TABLE
(
Seq INT IDENTITY,
Banco VARCHAR(50),
ArquivoDeDados_EspacoReservadoEmDisco_MB DECIMAL(15,2),
ArquivoDeDados_EspacoUsado_MB DECIMAL(15,2),
ArquivoDeDados_EspacoLivre_MB DECIMAL(15,2),
ArquivoDeLog_EspacoReservadoEmDisco_MB DECIMAL(15,2),
ArquivoDeLog_EspacoUsado_MB DECIMAL(15,2),
ArquivoDeLog_EspacoLivre_MB DECIMAL(15,2)
);

INSERT INTO @Tamanhos
SELECT
t.Banco
, t.ArquivoDeDados_EspacoReservadoEmDisco_MB
, t.ArquivoDeDados_EspacoUsado_MB
, t.ArquivoDeDados_EspacoLivre_MB
, l.EspacoReservadoEmDisco_MB AS ArquivoDeLog_EspacoReservadoEmDisco_MB
, l.EspacoUsado_MB AS ArquivoDeLog_EspacoUsado_MB
, CAST(l.EspacoReservadoEmDisco_MB-l.EspacoUsado_MB AS DECIMAL(10,2)) ArquivoDeLog_EspacoLivre_MB
FROM #Tamanhos t
JOIN
(
SELECT a.Banco, a.EspacoReservadoEmDisco_MB, b.EspacoUsado_MB
FROM
(
select
RTRIM(p.instance_name) AS Banco
, CAST(p.cntr_value/1024.0 AS DECIMAL(15,2)) AS EspacoReservadoEmDisco_MB
from sys.dm_os_performance_counters p
WHERE p.counter_name LIKE ‘Log File(s) Size (KB)%’
) a
JOIN
(
select
RTRIM(p.instance_name) AS Banco
, CAST(p.cntr_value/1024.0 AS DECIMAL(15,2)) AS EspacoUsado_MB
from sys.dm_os_performance_counters p
where p.counter_name LIKE ‘Log File(s) Used Size (KB)%’
) b ON a.Banco = b.Banco
WHERE a.Banco NOT IN (‘_Total’, ‘mssqlsystemresource’, ‘tempdb’, ‘master’, ‘model’, ‘msdb’)
) l ON t.Banco = l.Banco
ORDER BY Banco;

DROP TABLE #Tamanhos;

SET @vBody = @vBody +

<br><br>
<h3>Tamanho dos Bancos de Dados de Usuário</h3>
<table border=1 cellpadding=2>
<tr>
<th>Banco</th>
<th>Arquivo de Dados – Espaço Reservado em Disco (MB)</th>
<th>Espaço Usado (MB)</th>
<th>Espaço Livre (MB)</th>
<th>Arquivo de Log – Espaço Reservado em Disco (MB)</th>
<th>Espaço Usado (MB)</th>
<th>Espaço Livre (MB)</th>
</tr>
‘;

SELECT @Qt = COUNT(*) FROM @Tamanhos t;
SET @Loop = 1;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+ Banco +'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeDados_EspacoReservadoEmDisco_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeDados_EspacoUsado_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeDados_EspacoLivre_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeLog_EspacoReservadoEmDisco_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeLog_EspacoUsado_MB)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),t.ArquivoDeLog_EspacoLivre_MB)+'</td>’+
‘</tr>’
FROM @Tamanhos t
WHERE t.Seq = @Loop
);
SET @Loop = @Loop +1;
END;

SET @vBody = @vBody + ‘</table>’;

— *** Parte 5: TempDB Size
SET @vBody = @vBody +

<br><br>
<h3>Espaço no TempDB</h3>
<table border=1 cellpadding=2>
<tr>
<th>Páginas Livres</th>
<th>Espaço Livre em MB</th>
</tr>
‘;
SET @vBody = @vBody +
(
SELECT
‘<tr>’+
‘<td>’+CONVERT(VARCHAR(20),SUM(unallocated_extent_page_count))+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),CAST(SUM(unallocated_extent_page_count)/128.0 AS decimal(15,2)))+'</td>’+
‘</tr>’
FROM sys.dm_db_file_space_usage
);
SET @vBody = @vBody + ‘</table>’;

— *** Parte 6: Last backup with success
SET @vBody = @vBody +

<br><br>
<h3>Últimos Backups Realizados</h3>
<table border=1 cellpadding=2>
<tr>
<th>Banco</th>
<th>Descrição Opcional do Backup</th>
<th>Modelo de Recovery</th>
<th>Data e Hora de Início</th>
<th>Idade em dias</th>
<th>Tamanho do Backup em MB</th>
<th>Tipo</th>
<th>Arquivo</th>
</tr>
‘;
DECLARE @Backups TABLE
(
seq INT identity(1,1),
database_name NVARCHAR(128),
server_name NVARCHAR(128),
name  NVARCHAR(128),
recovery_model  NVARCHAR(60),
backup_start_date DATETIME,
days_ago VARCHAR(15),
backup_size_mb VARCHAR(15),
type char(1),
backup_type varchar(21),
physical_device_name nvarchar(260)
);

INSERT INTO @Backups
select
s.database_name
, s.server_name
, ISNULL(s.name,”)
, s.recovery_model
, s.backup_start_date
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, DATEDIFF (DAY, s.backup_start_date, GETDATE ())), 1)), 4, 15)) AS days_ago
,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (15), CONVERT (MONEY, ROUND (s.backup_size/1048576.0, 0)), 1)), 4, 15)) AS backup_size_mb
, s.type
,(CASE
WHEN s.type = ‘D’ THEN ‘Database’
WHEN s.type = ‘F’ THEN ‘File Or Filegroup’
WHEN s.type = ‘G’ THEN ‘Differential File’
WHEN s.type = ‘I’ THEN ‘Differential Database’
WHEN s.type = ‘L’ THEN ‘Log’
WHEN s.type = ‘P’ THEN ‘Partial’
WHEN s.type = ‘Q’ THEN ‘Differential Partial’
ELSE ‘N/A’
END) AS backup_type
, f.physical_device_name
from msdb.dbo.backupset s
JOIN msdb.dbo.backupmediafamily f ON s.media_set_id = f.media_set_id
WHERE s.backup_set_id =
(    SELECT TOP 1 a.backup_set_id
FROM msdb.dbo.backupset a
WHERE a.database_name = s.database_name
ORDER BY a.backup_set_id DESC    )
ORDER BY s.database_name;

SELECT @Qt = @@ROWCOUNT;
SET @Loop = 1;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
select
‘<tr>’+
‘<td>’+CONVERT(VARCHAR(128),database_name)+'</td>’+
‘<td>’+CONVERT(VARCHAR(128),name)+'</td>’+
‘<td>’+CONVERT(VARCHAR(60),recovery_model)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),backup_start_date, 13)+'</td>’+
‘<td>’+CONVERT(VARCHAR(10),days_ago)+'</td>’+
‘<td>’+CONVERT(VARCHAR(10),backup_size_mb)+'</td>’+
‘<td>’+CONVERT(VARCHAR(25),backup_type)+'</td>’+
‘<td>’+CONVERT(VARCHAR(260),physical_device_name)+'</td>’+
‘</tr>’
from @Backups
WHERE seq = @Loop
);
SET @Loop = @Loop +1;
END;
SET @vBody = @vBody + ‘</table>’;

— *** Parte 7: status execution jobs
SET @vBody = @vBody +

<br><br>
<h3>Status da Última Execução dos Jobs</h3>
<table border=1 cellpadding=2>
<tr>
<th>Nome</th>
<th>Status</th>
<th>Mensagem</th>
<th>Data da Execução</th>
<th>Hora da Execução</th>
</tr>
‘;
DECLARE @Jobs TABLE
(
Seq INT IDENTITY,
name SYSNAME,
status VARCHAR(50),
message NVARCHAR(4000),
run_date varchar(20),
run_time varchar(20)
);
INSERT INTO @Jobs
SELECT
j.name
, (CASE h.run_status
WHEN 0 THEN ‘Falha’
WHEN 1 THEN ‘Sucesso’
WHEN 2 THEN ‘Repetir’
WHEN 3 THEN ‘Cancelado’
WHEN 4 THEN ‘Em Progresso’
END) [status]
, h.message
, h.run_date
, h.run_time
FROM msdb.dbo.sysjobs j
CROSS APPLY
(    SELECT TOP 1 h.run_date, h.run_time, h.run_status, h.message
from msdb.dbo.sysjobhistory h
WHERE h.step_id = 0
AND h.job_id = j.job_id
ORDER BY h.instance_id DESC
) h
ORDER BY name;

SELECT @Qt = @@ROWCOUNT;
SET @Loop = 1;
WHILE @Loop <= @Qt
BEGIN
SET @vBody = @vBody +
(
select
‘<tr>’+
‘<td>’+CONVERT(VARCHAR(128),j.name)+'</td>’+
‘<td>’+CONVERT(VARCHAR(50),j.status)+'</td>’+
‘<td>’+CONVERT(NVARCHAR(4000),message)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),j.run_date)+'</td>’+
‘<td>’+CONVERT(VARCHAR(20),j.run_time)+'</td>’+
‘</tr>’
from @Jobs j
WHERE seq = @Loop
);
SET @Loop = @Loop +1;
END;
SET @vBody = @vBody + ‘</table>’;

— *** Envia
if @ExibirApenasHtml = 0
exec msdb.dbo.sp_send_dbmail
@recipients = ‘adeilsonrbrito@gmail.com’,
@subject=@vSubject,
@body=@vBody,
@body_format = ‘HTML’;
— *** Exibe como HTML ao invés de enviar por e-mail
else
SELECT @vBody;

— *** Final: elimina tabelas temporárias
IF OBJECT_ID(‘tempdb.dbo.#Tamanhos’) IS NOT NULL
DROP TABLE #Tamanhos;
IF OBJECT_ID(‘tempdb.dbo.#Tabela’) IS NOT NULL
DROP TABLE #Tabela;


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