Exibindo espaço usado de uma base usando o conteúdo da procedure sp_spaceused

Olá galera, tudo bem?

Após alguns meses sem escrever nada aqui no blog, vou postar uma dica simples e bem legal. Necessitei recementemente coletar informações sobre o crescimento de uma base de dados de um ambiente. Geralmente a alternativa mais comum que a maioria dos profissionais de banco de dados usam é a procedure sp_spaceused, no qual de acordo com books online tem por objetivo: “Exibe o número de linhas, o espaço em disco reservado e o espaço em disco usado por uma tabela, exibição indexada ou fila Service Broker no banco de dados atual ou exibe o espaço em disco reservado e usado pelo banco de dados inteiro…”. É uma procedure formidável, porém quem já utilizou e utiliza sabe que o retorno são dois resultados com quantidade de colunas diferentes. A imagem abaixo explica melhor o retorno da consulta:

USE DBAdmin;
GO
EXEC sp_spaceused;
GO

resultado sp_spaceused

Figura 1. Resultado de procedure sp_spaceused.

Minha necessidade na coleta era de ter essas informações de forma individual, no qual pudesse também armazenar em tabelas para consultas posteriores. Então a ideia foi de subtrair o conteúdo da procedure e adaptar a minha realidade no momento. Utilizando a procedure sp_helptext foi possível obter o conteúdo e fazer as adaptações que seguem abaixo.

Script 1.:

set nocount on
declare
@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@pages bigint
,@objname nvarchar(776) = ‘DBAdmin’

select
@dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from
dbo.sysfiles

select
@reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from
sys.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
select
[Banco] = @objname,
[Tamanho Banco MB] = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))* 8192 / 1048576,15,2)),
[Espaço Não Alocado MB] = ltrim(str((
case
when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) – convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2));
go

 

Resultado 1.:

seleção do espaço bd

Figura 2. Resultado de espaço utilizado do banco.

Script 2.:

set nocount on
declare
@id int
,@type character(2)
,@pages bigint
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint
,@objname nvarchar(776) = ‘DBAdmin’

select
@reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
When a.type <> 1 and p.index_id < 2 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from
sys.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
select
[Banco] = @objname,
[Reservado KB] = LTRIM (STR (@reservedpages * 8, 15, 0)),
[Dados KB] = LTRIM (STR (@pages * 8, 15, 0)),
[Tamanho Índice KB] = LTRIM (STR ((
CASE
WHEN @usedpages > @pages
THEN (@usedpages – @pages)
ELSE 0
END) * 8, 15, 0)),
[Não Usado KB] = LTRIM (STR ((
CASE
WHEN @reservedpages > @usedpages
THEN (@reservedpages – @usedpages)
ELSE 0
END) * 8, 15, 0));
go

Resultado 2.:

seleção do espaço indices e dados

Figura 3. Resultado de espaço dados e índices.

Bem pessoal, acho que por hoje é só. Caso queiram entender mais sobre a procedure sp_spaceused comentada neste post, é possível verificar na fonte abaixo. Lembrando que é possível adaptar os scripts para definir uma nova procedure, view, etc. Deixo essas alternativas de acordo com as necessidades de cada um.

Até a próxima!

(Visão geral sp_spaceused): https://msdn.microsoft.com/en-us/library/ms188776.aspx

Anúncios