[SQL Server] Script para deletar todos os registros de uma database
Publicado; 04/09/2012 Arquivado em: SQL Scripts | Tags: deletar registros, Script para deletar todos os registros, script sql server 6 ComentáriosSegue 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
Publicado; 07/08/2012 Arquivado em: SQL Scripts, SQL Server 3 ComentáriosMonitorar 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:
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)
Publicado; 28/12/2010 Arquivado em: SQL Scripts, SQL Server | Tags: tamanho de uma database Deixe um comentárioO 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
Publicado; 27/12/2010 Arquivado em: SQL Scripts, SQL Server | Tags: dropar tabelas, dropar todas as tabelas, excluir todas as tabelas 4 ComentáriosAí 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
Publicado; 18/12/2010 Arquivado em: SQL Profiler, SQL Scripts, SQL Server 2 ComentáriosO 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
Publicado; 17/12/2010 Arquivado em: Artigos, SQL Scripts, SQL Server | Tags: Start sql Deixe um comentárioDe 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
Publicado; 16/12/2010 Arquivado em: Artigos, SQL Scripts, SQL Server | Tags: SQL Scripts, SQL SERVER 2008 Deixe um comentárioÀ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
Publicado; 12/08/2010 Arquivado em: SQL Scripts, SQL Server Deixe um comentárioSome 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
Reciclando o arquivo Error Log do SQL Server 2008
Publicado; 01/05/2010 Arquivado em: Artigos, SQL Scripts, SQL Server Deixe um comentárioA 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.
SQL Server 2008: Recuperando status de execução dos jobs via script
Publicado; 30/04/2010 Arquivado em: Artigos, SQL Scripts, SQL Server Deixe um comentárioUma das tarefas diárias do DBA é a de examinar cuidadosamente o status de execução de todos os jobs agendados. Isso pode ser feito pela interface gráfica, é claro. Mas um bom script pode facilitar esse procedimento, ainda mais se a lista de jobs for extensa e se você precisar saber apenas aqueles que apresentaram falha na execução. Desta forma, elaborei a query abaixo disponibilizada.
use msdb
/*
Basicamente usar as tabelas sysjob e sysjobhistory do MSDB.
O step “0” não existe de verdade, ele apenas indica o status geral da execução completa do job.
Os demais steps existem e indicam o status individual de cada step.
*/
— Set para 1 caso queira que seja listado apenas os jobs que apresentaram erro
DECLARE @ListarSohErros BIT = 1;
— Set para 1 caso queira visualizar apenas o status do job e não dos steps.
DECLARE @ListarSohStep0 BIT = 1;
—
SELECT
j.name,
h.step_id,
h.step_name,
(CASE h.run_status
WHEN 0 THEN ‘Failed’
WHEN 1 THEN ‘Succeeded’
WHEN 2 THEN ‘Retry’
WHEN 3 THEN ‘Canceled’
WHEN 4 THEN ‘In Progress’
END) [status],
h.message,
h.run_date,
h.run_time,
h.run_duration
FROM sysjobs j
JOIN sysjobhistory h on j.job_id = h.job_id
WHERE h.run_status = (CASE WHEN @ListarSohErros = 0 THEN h.run_status ELSE 0 END)
AND h.step_id = (CASE WHEN @ListarSohStep0 = 0 THEN h.step_id ELSE 0 END)
ORDER BY j.name, h.step_id;
Adeilson Blog 