Caros colegas, hoje recebi um comentário que acabou se tornando uma sugestão bem interessante, visto que recebo muitas visitas sobre como obter o tamanho das tabelas de um banco. Bom, a solução que vou propor aqui vai mais a fundo, de forma a proporcionar uma ferramenta que facilite a gestão do tamanho de tabelas, quando isto é necessário. E é claro, eu preciso abrir 2013 com um post bacana, então aí vai!
É claro, como profissional da área, não posso negligenciar uma verdade: se você está tendo que monitorar tamanhos de tabelas no banco de dados, existem problemas bem mais graves por trás de tudo isso. Ficar resolvendo somente o resultado de um problema não resolve o problema em si, tem que atacar a causa, sempre. Nem preciso entrar em detalhes, certo? Mas se você não consegue resolver a fonte do problema (ex.: sistema de terceiros), essa dica vai facilitar sua vida.
Hoje vou postar duas alternativas bem simples para melhorar a utilização do cáculo, uma derivada da outra. A primeira procedure, que eu acho a mais interessante, realizará o envio automático ao seu e-mail com um relatório em HTML com as maiores tabelas, de todos os bancos de dados, sendo acionada por uma JOB, por exemplo. Eu já fiz uma formatação HTML que permita visualizar em dispositivos móveis (claro, qualquer aparelho que reproduza ao menos HTML, por favor). A segunda é o mesmo cálculo, porém uma procedure simplificada, para ser executada manualmente e que retorna uma tabela com todos os registros por linha, para visualizar no resultado da query, ou copiar e colar para uma planilha, por exemplo.
* Importante
- ambas as procedures podem ser salvas e executas em qualquer banco de dados, isso é indiferente.
- Para a utilização do primeiro modelo, será necessário conhecimento em JOBS (criar job) e DATABASE MAIL (serviço ativo e conta SMTP devidamente configurada).
Então vamos começar pelo procedimento que envia o relatório por email. Observe os comentários no código para melhores orientações sobre como personalizar a execução de acordo com suas necessidades:
-- exec sp_espacotabelabanco_email
create proc sp_espacotabelabanco_email
as
/*
Fabiano Cores 02.01.2013
http://sqlburger.blogspot.com
Segunda versão do cálculo do tamanho das tabelas, que trata todos os bancos da instância onde
for executada.
Versão 1.: Envio do relatório para um email pré-determinado
*/
set nocount on
declare @vname varchar(100)
declare @vbase varchar(100)
declare @sql varchar(max)
declare @email varchar(max)
declare @assunto varchar(200)
declare @vtexto varchar(max)
create table #tmpTamTabela (
db varchar(100) null
, name varchar(100) null
, rows int null
, reserved varchar(25) null
, data varchar(25) null
, index_size varchar(25) null
, unused varchar(25) null )
create table #tmpTabelas (
tabela varchar(100) null
)
create table #retorno (
texto varchar(max) not null
, ordem int identity
)
insert into #retorno (texto)
select '<table border=1 cellpadding=2 cellspacing=0 width=100%>'
insert into #retorno (texto)
select '<tr bgcolor=#000000>'
+ ' <td align=left Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Tabela</b></font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Linhas</b></font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Tamanho</b></font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Dados</b></font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Index</b></font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=2 color=#FFFFFF><b>Livre</b></font></td>'
+ '</tr>'
declare cp0 cursor local fast_forward read_only for
select name
from master..sysdatabases
/* ESPECIFIQUE AQUI AS EXCEÇÕES PARA CÁLCULO */
where name not in ('master', 'tempdb', 'model', 'msdb')
order by name
open cp0
while 0 =0
begin
fetch next from cp0 into @vbase
if @@fetch_status <> 0 break
truncate table #tmpTabelas
insert into #retorno (texto)
select '<tr bgcolor=#900000><td colspan=6><font face=tahoma size=2 color=#ffffff>DB: <b>' + rtrim(@vbase) + '</b></td></tr>'
select @sql = '
insert into #tmpTabelas (tabela)
select name
from [' + rtrim(@vbase) + ']..sysobjects
where type = ''U''
'
exec(@sql)
declare cp1 cursor local fast_forward read_only for
select tabela from #tmpTabelas
order by 1
open cp1
while 1 = 1
begin
fetch next from cp1 into @vname
if @@fetch_status <> 0 break
select @sql = '
insert into #tmpTamTabela (name, rows, reserved, data, index_size, unused)
exec [' + rtrim(@vbase) + ']..sp_spaceused ''' + rtrim(@vname) + '''
update #tmpTamTabela
set db = ''' + rtrim(@vbase) + '''
where db is null
'
exec(@sql)
end
close cp1
deallocate cp1
insert into #retorno (texto)
/* altere aqui a quantidade de tabelas a ser exibida no relatório */
select top 10 '<tr>'
+ ' <td align=left Valign=top><font face=tahoma size=1>' + rtrim(name) + '</font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=1>' + rtrim(rows) + '</font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=1>' + rtrim(reserved) + '</font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=1>' + rtrim(data) + '</font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=1>' + rtrim(index_size) + '</font></td>'
+ ' <td align=right Valign=top><font face=tahoma size=1>' + rtrim(unused) + '</font></td>'
+ '</tr>'
from #tmpTamTabela
where db = @vbase
order by convert(int, replace(reserved, ' KB','')) desc
end
close cp0
deallocate cp0
insert into #retorno (texto)
select '</table>'
declare cp2 cursor local fast_forward read_only for
select texto from #retorno order by ordem
open cp2
while 1 = 1
begin
fetch next from cp2 into @vtexto
if @@fetch_status <> 0 break
select @email = isnull(@email, '') + rtrim(@vtexto)
end
close cp2
deallocate cp2
set @assunto = 'Relatório tabelas - ' + rtrim(@@SERVERNAME)
exec msdb.dbo.sp_send_dbmail @profile_name = 'atlanta',
@recipients = 'fbcores@gmail.com',
@copy_recipients = 'fabiano.cores@nms.com.br',
@blind_copy_recipients = '',
@subject = @assunto,
@body = @email,
@body_format = 'HTML'
Não vou entrar em detalhes em como se utiliza a função de email ou como se cria uma JOB no SQL. Creio que sejam assuntos para outro artigo. Existem muito conteúdo explicativo sobre como ativar e utilizar o DATABASE MAIL e o SQL SERVER AGENT, basta dar uma procurada na internet. Mas se tiverem dificuldades em concluir essa configuração, me avisem pra eu saber se é necessário ou não fazer um artigo específico para ensinar como utilizar essas ferramentas.
A imagem abaixo é a visualização do relatório no meu tablet. No celular também ficou bem legal:
O segundo exemplo, é uma versão simplificada do procedimento, que não trata a formatação de texto nem nada parecido. É uma procedure para ser executada manualmente sempre que o administardor quiser consultar os tamanhos das tabelas:
-- exec sp_espacotabelabanco
create proc sp_espacotabelabanco
as
/*
Fabiano Cores 02.01.2013
http://sqlburger.blogspot.com
Segunda versão do cálculo do tamanho das tabelas, que trata todos os bancos da instância onde
for executada.
Versão 2: simplificada, para execução manual
*/
set nocount on
declare @vname varchar(100)
declare @vbase varchar(100)
declare @sql varchar(max)
create table #tmpTamTabela (
db varchar(100) null
, name varchar(100) null
, rows int null
, reserved varchar(25) null
, data varchar(25) null
, index_size varchar(25) null
, unused varchar(25) null )
create table #tmpTabelas (
tabela varchar(100) null
)
declare cp0 cursor local fast_forward read_only for
select name
from master..sysdatabases
/* ESPECIFIQUE AQUI AS EXCEÇÕES PARA CÁLCULO */
where name not in ('master', 'tempdb', 'model', 'msdb')
order by name
open cp0
while 0 =0
begin
fetch next from cp0 into @vbase
if @@fetch_status <> 0 break
truncate table #tmpTabelas
select @sql = '
insert into #tmpTabelas (tabela)
select name
from [' + rtrim(@vbase) + ']..sysobjects
where type = ''U''
'
exec(@sql)
declare cp1 cursor local fast_forward read_only for
select tabela from #tmpTabelas
order by 1
open cp1
while 1 = 1
begin
fetch next from cp1 into @vname
if @@fetch_status <> 0 break
select @sql = '
insert into #tmpTamTabela (name, rows, reserved, data, index_size, unused)
exec [' + rtrim(@vbase) + ']..sp_spaceused ''' + rtrim(@vname) + '''
update #tmpTamTabela
set db = ''' + rtrim(@vbase) + '''
where db is null
'
exec(@sql)
end
close cp1
deallocate cp1
end
close cp0
deallocate cp0
select db as 'Banco'
, name as 'Nome'
, rows as 'Linhas'
, convert(int, replace(reserved, ' KB','')) as 'Tamanho total'
, convert(int, replace(data, ' KB',''))as 'Dados'
, convert(int, replace(index_size, ' KB',''))as 'Index'
, convert(int, replace(unused, ' KB',''))as 'Não utilizado'
from #tmpTamTabela
order by db, convert(int, replace(reserved, ' KB','')) desc
Esta procedure acima retorna o resultado da seguinte forma:
Infelizmente não estou tendo tempo para escrever mais artigos, mas espero ter ajudado com este, tentei simplificar o máximo possível, só copiar, colar e desfrutar.
E feliz 2013!
