Short Scripts – Março 2022 | Junior Galvão – MVP

Olá pessoal, bom dia…

Seja bem-vindo, em mais um post da sessão Short Scripts, o primeiro post desta sessão em 2022 e o de número 45 em seu total.

Estamos começando o outono de 2022, eu adoro este vento que fica o dia todo em conjunto com esta temperatura agradável, chega de passar calor.

Mantendo a tradição estou retornando com mais um conjunto dos “curtos ou pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, vou pagar a minha mais uma vez hoje, feita no encerramento do post anterior publicado no mês de julho de 2021, se por acaso você ainda não acessou o mesmo, calma, fique tranquilo, tudo vai dar certo, no encerramento deste post você terá a sua disposição a relação das últimas cinco publicações relacionadas aos Short Scripts.

Vamos caminhar mais um pouco então……..

O post de hoje

Para este post, não vou exatamente compartilhar um conjunto variado de scripts que foram catalogados nos últimos meses, ao contrário, vou disponibilizar um conteúdo que foi elaborado hoje, dedicado de forma única e exclusiva a demonstrar como podemos utilizar diversos arquivos de dados, log e filegroups nas configurações de um banco de dados existente no Microsoft SQL Server.

Os exemplos de código aqui compartilhados se relacionam com os seguintes comandos, funcionalidades ou recursos:

● Comando Alter Database;
● Comando Create Database;
● Comando Create Table;
● Comando Declare;
● Comando Drop Database;
● Comando Insert;
● Comando Select;
● Comando Use;
● Comando While;
● DBCC OpenTran();
● DBCC ShrinkFile();
● DBCC SQLPerf();
● Diretiva Set;
● Função Cast();
● Função FileProperty();
● Set Recovery;
● System Stored Procedure SP_Attach_db;
● System Stored Procedure SP_Detach_db;
● System Stored Procedure SP_HelpFile;
● System Stored Procedure SP_HelpFileGroup;
● Tabela de sistema sys.all_objects;
● Tabela de sistema sys.database_files;
● Tabela de sistema sys.databases;
● Tabela de sistema sys.indexes;
● Tabela de sistema sys.sysdatabase_files;
● Tabela de sistema sys.sysfilegroups;
● Tipo de Dados Int;e
● Variáveis.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Março 2022.

Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, tenha todo cuidado possível para evitar maiores problemas.

Fique à vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1 – Adicionando Filegroups e Arquivos de Dados —

-- Criando o Banco de Dados Aula 9 --
Create Database Aula9
Go

-- Acesso o Banco de Dados Aula 9 --
Use Aula9
Go

-- Obtendo o tamanho da tabela 'Tabela1' --
sp_spaceused 'Tabela1'
Go

-- Obtendo informações sobre os arquivos que compõem o banco de dados --
Exec SP_HelpFile
Go

-- Obtendo informações sobre o Grupo de Arquivos (FileGroup) atual --
Exec SP_HelpFileGroup
Go

-- Adicionando um novo FileGroup --
Alter DataBase Aula9
Add Filegroup Segundo
Go

-- Adicionando um novo Arquivo de Dados ao Banco de Dados Aula9 --
Alter Database Aula9
Add File
(Name = Aula9_Dados_Terceiro,
FileName = 'C:\Bancos\Aula9_Dados_Terceiro.ndf',
Size = 4MB,
MaxSize = Unlimited,
Filegrowth = 10%)
To Filegroup Segundo
Go

-- Criando a nova Tabela2 no FileGroup Segundo --
CREATE TABLE TABELA2
(Codigo bigint IDENTITY(1,1) NOT NULL Primary Key,
Valores1 int NOT NULL,
Valores2 int NOT NULL,
Texto varchar(max) NULL,
Texto2 text NULL,
DataInicial date NULL,
DataFinal datetime NULL)
ON [SEGUNDO]
Go

-- Copiando os Dados da Tabela1 para Tabela2 --
Insert Into TABELA2
Select Valores1, Valores2, Texto, Texto2, DataInicial, DataFinal From TABELA1
Go

Select * from Tabela2
Go

-- Obtendo informações sobre LogSpace --
DBCC SQLPERF(Logspace)
Go

-- Obtendo informações sobre transações ativas/execução --
DBCC OPENTRAN(Aula9)
Go

-- Obtendo Status de Reutilização do Transaction Log --
Select Log_Reuse_Wait, log_reuse_wait_desc from sys.databases
Where name='Aula9'

-- Alterando o Modelo de Recuperação do Banco de Dados --
ALTER DATABASE Aula9
Set Recovery Simple 
Go

-- Encolhendo o Arquivo de Log --
DBCC ShrinkFile(2,50);
Go

Dbcc Shrinkfile(2,TruncateOnly)
Go

-- Alterando o Modelo de Recuperação do Banco de Dados --
ALTER DATABASE Aula9
Set Recovery Full
Go

-- Alterando as propriedades do Arquivo de Log --
Alter Database Aula9
Modify File
(Name = AULA9_LOG,
MaxSize = Unlimited,
Filegrowth = 10%)
Go

— Short Script 2 – Identificando o Filegroup utilizado pelas tabelas —
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
Go

— Short Script 3 – Criando um novo Banco de Dados com dois FileGroups —

-- Criando o Banco de Dados com Dois FileGroups --
Create Database FatecBDTwoFileGroups
On Primary
(Name = 'FatecBDTwoFileGroups-Data',
FileName = 'S:\Databases\DATA\FatecBDTwoFileGroups-Data.MDF',
Size = 8 MB,
MaxSize = 4GB,
FileGrowth = 8 MB),
Filegroup Secondary Default -- Definindo o novo filegroup como default, deixa o primary para armazenar somente os objetos de sistema --
(Name = 'FatecBDTwoFileGroups-Data-Secondary',
FileName = 'S:\Databases\DATA\FatecBDTwoFileGroups-Data-Secondary.MDF',
Size = 8 MB,
MaxSize = 4GB,
FileGrowth = 8 MB),
(Name = 'FatecBDTwoFileGroups-Data-1-Secondary',
FileName = 'S:\Databases\DATA\FatecBDTwoFileGroups-Data-1-Secondary.NDF',
Size = 8 MB,
MaxSize = 8192 MB,
FileGrowth = 8 MB),
(Name = 'FatecBDTwoFileGroups-Data-2-Secondary',
FileName = 'S:\Databases\DATA\FatecBDTwoFileGroups-Data-2-Secondary.NDF',
Size = 8 MB,
MaxSize = 8192 MB,
FileGrowth = 8 MB)
Log On
(Name = 'FatecBDTwoFileGroups-Log-1-Secondary',
FileName = 'S:\Databases\LOG\FatecBDTwoFileGroups-Log-1-Secondary.LDF',
Size = 10 MB,
MaxSize = Unlimited,
FileGrowth = 100 MB)
Go

-- Acessando o Banco de Dados --
Use FatecBDTwoFileGroups
Go

-- Simulando a escrita e distribuição dos dados nos arquivos --
-- Criando a Tabela Jogadores --
Create Table Jogadores
(JogadorID Int Primary Key Identity(1,1),
JogadorNome Varchar(30) Not Null,
JogadorData DateTime Not Null)
Go

-- Identificando a relação de tabelas e filegroups --
Select o.[name], o.[type],
i.[name], i.[index_id],
f.[name]
From sys.indexes i Inner Join sys.filegroups f
On i.data_space_id = f.data_space_id
Inner Join sys.all_objects o
On i.[object_id] = o.[object_id]
Where i.data_space_id = f.data_space_id
And o.type In ('s','u')
Go

-- Apresentando a lista de arquivos que formam o banco de dados - SQL Server 2008 ou superior --
Select file_id, type, type_desc, data_space_id, name, physical_name, state_desc, size, max_size, growth
From sys.database_files
Go

-- Apresentando a lista de filegroups existentes no banco de dados --
Select * from sys.sysfilegroups
Go

-- Simulando escrita em Log --
Declare @Contador Int -- Declarando a variável @Contador

Set @Contador=1 -- Atribuindo um valor inicial igual á 1

While @Contador <=999999 -- Laço condicional
Begin
Insert Into Jogadores Values ('Pedro',GetDate()+@Contador)

Set @Contador=@Contador+1 -- Incrementando o valor da variável @Contador
End
Go

— Short Script 4 – Simulando a utilização de dois arquivos de dados e depois removendo um arquivo —

-- Verificando a existência do Banco de Dados TesteDoisArquivos --
If Exists (Select Name From sys.sysdatabases Where Name = 'TesteDoisArquivos')
Drop Database TesteDoisArquivos
Go

-- Criando o Banco de Dados TesteDoisArquivos --
Create Database TesteDoisArquivos
On Primary
(Name = 'TesteDoisArquivos-Data1',
FileName = 'S:\MSSQL-2019\DATA\TesteDoisArquivos-Data1.MDF',
Size=8MB,
MaxSize=8192MB,
FileGrowth=64MB),
(Name = 'TesteDoisArquivos-Data2',
FileName = 'S:\MSSQL-2019\DATA\TesteDoisArquivos-Data2.NDF',
Size=8MB,
MaxSize=8192MB,
FileGrowth=64MB)
Log On
(Name = 'TesteDoisArquivos-Log',
FileName = 'S:\MSSQL-2019\Log\TesteDoisArquivos-Log.LDF',
Size=8MB,
MaxSize=8192MB,
FileGrowth=64MB)
Go

-- Alterando o Modelo de Recuperação para Simple --
Alter Database TesteDoisArquivos
Set Recovery Simple
Go

-- Acessando --
Use TesteDoisArquivos
Go

-- Criando a Tabela TabelaTesteDoisArquivos --
Create Table TabelaTesteDoisArquivos
(Codigo Int Identity(1,1) Primary Key Clustered,
Descricao Char(100) Default 'Este é um teste de armazenamento de dados',
ValorBig1 BigInt Default Rand()*100000+1,
ValorBig2 BigInt Default Rand()*200000+2)
Go

-- Inserindo a Massa de Dados na Tabela TabelaTesteDois Arquivos --
Insert Into TabelaTesteDoisArquivos Default Values
Go 1000000

-- Validando o tamanho atual e espaço disponíveis nos arquivos de dados --
Select name, size/128.0 As FileSize, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 As AvailableSpaceInMB
From sys.database_files
Where type = 0
Go

-- Encolhendo o Arquivo de Dados TesteDoisArquivos-Data2 para 1 Mbs --
DBCC ShrinkFile('TesteDoisArquivos-Data2',1)
Go

-- Truncando o Arquivo de Dados TesteDoisArquivos-Data2 orientando o SQL Server para liberar o espaço alocado para o Disco --
DBCC ShrinkFile('TesteDoisArquivos-Data2', TruncateOnly)
Go

-- Limpando o conteúdo o arquivo, transferindo para os outros Data Files --
DBCC ShrinkFile('TesteDoisArquivos-Data2',EmptyFile)
Go

-- Removendo o arquivo de Dados TesteDoisArquivos-Data2 --
Alter Database TesteDoisArquivos
Remove File [TesteDoisArquivos-Data2]
Go

-- Validando se o ShrinkFile conseguiu liberar espaço e realizar área livre nos arquivos --
Select name, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 As AvailableSpaceInMB
From sys.database_files
Where type = 0
Go

-- Consultando a Tabela TabelaTesteDoisArquivos para garantir a existência dos dados --
Select * From TabelaTesteDoisArquivos
Go

— Short Script 5 – Realizando o detach e attach database com diversos arquivos de dados —

-- Verificando a existência do Banco de Dados TesteDoisArquivos --
If Exists (Select Name From sys.sysdatabases Where Name = 'TesteDoisArquivos')
Drop Database TesteDoisArquivos
Go

-- Criando o Banco de Dados TesteDoisArquivos --
Create Database TesteDoisArquivos
On Primary
(Name = 'TesteDoisArquivos-Data1',
FileName = 'S:\MSSQL-2019\DATA\TesteDoisArquivos-Data1.MDF',
Size=8MB,
MaxSize=8192MB,
FileGrowth=64MB),
(Name = 'TesteDoisArquivos-Data2',
FileName = 'S:\MSSQL-2019\DATA\TesteDoisArquivos-Data2.NDF',
Size=8MB,
MaxSize=8192MB,
FileGrowth=64MB)
Log On
(Name = 'TesteDoisArquivos-Log',
FileName = 'S:\MSSQL-2019\Log\TesteDoisArquivos-Log.LDF',
Size=8MB,
MaxSize=8192MB,
FileGrowth=64MB)
Go

-- Desanexando o Banco de Dados TesteDoisArquivos --
Exec sp_detach_db 'TesteDoisArquivos'
Go

-- Anexando o Banco de Dados TesteDoisArquivos, informando o conjunto de arquivos --
Exec sp_attach_db 'TesteDoisArquivos',
@FileName1 = N'S:\MSSQL-2019\DATA\TesteDoisArquivos-Data1.mdf',
@FileName2 = N'S:\MSSQL-2019\Log\TesteDoisArquivos-Log.ldf',
@FileName3 = N'S:\MSSQL-2019\DATA\TesteDoisArquivos-Data2.ndf'
Go

-- Alterando o Modelo de Recuperação para Simple --
Alter Database TesteDoisArquivos
Set Recovery Full
Go

-- Acessando o Banco de Dados --
Use TesteDoisArquivos
Go

-- Criando a Tabela TabelaTesteDoisArquivos --
Create Table TabelaTesteDoisArquivos
(Codigo Int Identity(1,1) Primary Key Clustered,
Descricao Char(100) Default 'Este é um teste de armazenamento de dados',
ValorBig1 BigInt Default Rand()*100000+1,
ValorBig2 BigInt Default Rand()*200000+2)
Go

-- Inserindo a Massa de Dados na Tabela TabelaTesteDois Arquivos --
Insert Into TabelaTesteDoisArquivos Default Values
Go 1000000

-- Consultando --
Select * From TabelaTesteDoisArquivos
Go

Muito bem, missão mais que cumprida!

Uma super e especial relação de short scripts acaba de ser compartilhada, mesmo sendo denominados shorts entre aspas “curtos ou pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.

Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Short Scripts – Junho 2021

Short Scripts – Janeiro 2021

Short Scripts – Setembro 2020

Short Scripts – Maio 2020

https://pedrogalvaojunior.wordpress.com/2020/02/05/short-scripts-fevereiro-2020/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no próximo post.

Tenha uma ótima semana.

Abraços.

S	T	Q	Q	S	S	D
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

Junior Galvão – MVP – Data Platform

Select 'MVP | MCC | MSTC | MIE | MTAC' As Awards

Short Scripts – Março 2022

Avalie isto:

Compartilhe isso: