Pessoal, boa noite!

 

Resolvi mais uma vez compartilhar um pouco mais sobre o meu dia a dia com SQL Server, como já disse trabalho com um VLDB, e para gerenciá-lo, muitas vezes precisamos nos utilizarmos de técnicas como a "dividir para conquistar", sim existe um algoritmo com esse nome e sim estou falando de particionamento e filegroups.

 

Hoje infelizmente ainda sofremos com as dificuldades do dia a dia de um DBA, pois estamos com deficiência em ferramentas de monitoração reativa e principalmente pró-ativa no meu cliente isso não é diferente, estão implementando o MOM apenas agora, imaginem apenas um dos nossos databases possui exatos 11TB e falo de OLTP, trabalhamos hoje com um volume total de dados em torno de 40TB apenas em OLTP e temos desafios e dificuldades de bancos de alguns GBs.

 

Bom todos sabemos que os filegroups em conjunto com particionamento nos ajudam em diversas atividades, exemplo: backup, switch de dados históricos, distribuição de I/O e etc.

 

Pois bem, hoje o cliente veio me solicitar um SCRIPT para monitorar o Free Space dos Filegroups com seus respectivos arquivos individuais, pois bem a primeira coisa que pensei foi, bom no SSMS existe isso, vou capturar a query e trabalhar em cima disso e foi o realmente eu fiz.

 

A query abaixo foi adaptada e hoje nós passamos a utilizá-la em um processo batch diário de coleta do espaço por filegroup e também podemos disponibilizar um relatório em SSRS para monitoração ad-hoc.

 

 

if exists (select * from sys.objects where name = 'usp_FGCalc')

drop proc usp_FGCalc;

go

--Usar o novo database e calcular o espao dos FGs

CREATE

PROC usp_FGCalc (@dbname as nvarchar(128))

AS

/*

Funcionalidade: Calcular o espao livre em cada arquivo de um Filegroup,

funcionalidade baseada nas queries do SSMS.

Adapatado por: Rodrigo Fernandes de Souza

Empresa: Microsoft Brasil

Compatibilidade: SQL Server 2005/2008

Data: 13/04/2009

*/

SET

NOCOUNT ON;

DECLARE

@cmd nvarchar(4000), @dbid int;

SET

@dbid = DB_ID(@dbname)

if

@dbid is null

begin

RAISERROR('Database does not exists!', 16, 1);

RETURN;

end

CREATE TABLE #FGSpace

(FileId int, FileGroup int,

TotalExtents

int, UsedExtents int,

Name

sysname, FileName nchar(520))

SET @cmd = 'USE ' + @dbname + CHAR(10) + CHAR(13) +

'Insert #FGSpace EXEC (''DBCC SHOWFILESTATS() WITH NO_INFOMSGS'')';

EXEC sp_executesql @cmd;

Select

mf

.name As [LogicalName],

fg

.name As [FGName],

mf

.physical_name As [FileName],

mf

.size * CONVERT(float, 8) As [Current_FileSize],

CAST (CASE mf.[type]

WHEN 2 THEN 0 --Retira os FGs de FileStream

ELSE fgs.UsedExtents * CONVERT(float, 64) --Calcula o tamanho ocupado no arquivo

END As float) As [UsedSpace],

CAST (CASE mf.[type]

WHEN 2 THEN 0 --Retira os FGs de FileStream

ELSE 100 -(((fgs.UsedExtents * CONVERT(float, 64)) * 100) / (mf.size * CONVERT(float, 8)))

--Calcula o FreeSpace

END As int) As [FreeSpace],

mf

.[file_id] As [Id]

From

sys.filegroups As fg

INNER JOIN

sys.master_files As mf

ON (mf.type = 2

Or mf.type = 0

And mf.database_id = @dbid

And mf.drop_lsn IS NULL

And mf.data_space_id = fg.data_space_id)

LEFT OUTER JOIN

#FgSpace

As fgs

ON fgs.Fileid = mf.[file_id]

ORDER BY

[Id]

Asc

 

Quem está familiarizado com SQL Server desde a versão 2000 no mínimo, deve lembra da função DBCC SHOWFILESTATS, esse comando não documentando basicamente nos retorna o espaço alocado e utilizado em extents no arquivo.

Lembrando um Extent, corresponde a 8 pages ou seja 8192 * 8 = 64K.

Com isso é possível já utilizarmos as dmvs novas sys.filegroups e sys.master_files para calcularmos o espaço utilizado e a taxa de espaço livre.

Pessoal, sei que esse script já pode ser conhecido por alguns, porém vale a pena utilizá-lo, onde você tem casos com muitos filegroups em conjunto com particionamento onde não exista uma ferramenta adequada para monitoração e notificação.

Espero que seja útil a todos!

Grande Abraço,

Rodrigo Fernandes de Souza