OK, entendi. Encolher seu banco de dados está errado . Você odeia . Mas deixe-me explicar.
Eu tenho um banco de dados SQL do Azure P1 de 1 TB em produção com ~ 50 tabelas, onde ~ 5 delas são contêineres JSON. Esse era o design original e rapidamente percebi seus limites, então agora estou no processo de descarregar o armazenamento desses JSONs para uma conta de armazenamento do Azure mais apropriada.
Esse processo levará tempo (JSONs são usados em diferentes processos de negócios e estou migrando um de cada vez), então estou excluindo intervalos de linhas após uma migração bem-sucedida. Ainda assim, não consigo truncar ou descartar a tabela inteira.
Depois de migrar muitos processos de negócios, agora tenho 868,64 GB de espaço alocado versus 390,82 GB de espaço usado. Obviamente, gostaria de reduzir o tamanho do meu armazenamento para a camada de 400 GB para reduzir custos, mas quando tento fazer isso no Portal do Azure, recebo a seguinte mensagem de erro:
O tamanho de armazenamento do seu banco de dados não pode ser menor que o tamanho alocado no momento. Para reduzir o tamanho do banco de dados, o banco de dados primeiro precisa recuperar o espaço não utilizado executando
DBCC SHRINKDATABASE (<db_name>). Observe que essa operação pode afetar o desempenho durante a execução e pode levar várias horas para ser concluída.
Ok, bastante justo. Então eu prossigo para executar o comando (claro, com o nome correto do banco de dados) e depois de algumas horas e execução bem-sucedida, a situação é exatamente a mesma. Nenhum espaço recuperado.
Depois disso, continuei com as seguintes tentativas:
- Talvez eu tenha que forçar a reorganização + truncamento, então executei
dbcc shrinkdatabase(<db_name>, notruncate)seguido dedbcc shrinkdatabase(<db_name>, truncateonly): sem resultados. - Talvez eu tenha que reduzir os arquivos únicos, então executei
dbcc shrinkfile(<file_name>): ainda o mesmo. - Talvez eu tenha que reduzir os arquivos para um valor específico, então executei `dbcc shrinkfile(<file_name>, <free_space_on_file_in_mb>): novamente, sem sorte.
Esta consulta
with
[BaseData] as (
select
[DF].[type_desc] as [Type],
[DF].[name] as [FileName],
[DF].[size] / 131072.0 as [TotalSpaceInGB],
[UP].[size] / 131072.0 as [UsedSpaceInGB],
([DF].[size] - [UP].[size]) / 131072.0 as [FreeSpaceInGB],
[DF].[max_size] as [MaxSize]
from [sys].[database_files] as [DF]
cross apply (
select fileproperty([DF].[name], 'spaceused') as [size]
) as [UP]
)
select
[BD].[Type] as [Type],
[BD].[FileName] as [FileName],
format([BD].[TotalSpaceInGB], N'N2') as [TotalSpaceInGB],
format([BD].[UsedSpaceInGB], N'N2') as [UsedSpaceInGB],
format([BD].[FreeSpaceInGB], N'N2') as [FreeSpaceInGB],
case [BD].[MaxSize]
when 0 then N'Disabled'
when -1 then N'Unrestricted'
else format(([BD].[MaxSize] / 131072.0), N'N2')
end as [MaxSizeInGB]
from [BaseData] as [BD]
order by [BD].[Type] asc, [BD].[FileName];
sempre retorna o mesmo resultado:
| Modelo | Nome do arquivo | TotalSpaceInGB | Espaço usado em GB | FreeSpaceInGB | MaxSizeInGB |
|---|---|---|---|---|---|
| FILESTREAM | XTP | 2.03 | NULO | NULO | Sem restrições |
| REGISTRO | registro | 1,63 | 0,60 | 1,03 | 250,00 |
| LINHAS | data_0 | 509,47 | 231,58 | 277,89 | 512,00 |
| LINHAS | dfa_data_3 | 359,17 | 159,27 | 199,91 | 512,00 |
Além disso, esta consulta:
with
[BaseData] as (
select
[TB].[object_id] as [ObjectId],
max([PT].[rows]) as [RowCount],
count(distinct [IX].[index_id]) as [IndexCount],
sum([PS].[used_page_count]) / 131072.0 as [UsedSpaceInGB],
sum([PS].[reserved_page_count]) / 131072.0 as [ReservedSpaceInGB]
from [sys].[schemas] as [SC]
inner join [sys].[tables] as [TB]
on [SC].[schema_id] = [TB].[schema_id]
inner join [sys].[indexes] as [IX]
on [TB].[object_id] = [IX].[object_id]
inner join [sys].[partitions] as [PT]
on [TB].[object_id] = [PT].[object_id]
and [IX].[index_id] = [PT].[index_id]
left join [sys].[dm_db_index_usage_stats] as [IS]
on [TB].[object_id] = [IS].[object_id]
and [IX].[index_id] = [IS].[index_id]
left join [sys].[dm_db_partition_stats] as [PS]
on [PT].[partition_id] = [PS].[partition_id]
and [IX].[index_id] = [PS].[index_id]
and [TB].[object_id] = [PS].[object_id]
group by [TB].[object_id]
)
select top 5
[BD].[ObjectId] as [ObjectId],
[BD].[RowCount] as [RowCount],
[BD].[IndexCount] as [IndexCount],
format([BD].[UsedSpaceInGB], N'N2') as [UsedSpaceInGB],
format([BD].[ReservedSpaceInGB], N'N2') as [ReservedSpaceInGB]
from [BaseData] as [BD]
order by [BD].[ReservedSpaceInGB] desc;
mostra claramente que as tabelas não estão ocupando mais espaço do que o necessário:
| ObjectId | Contagem de linhas | IndexCount | Espaço usado em GB | ReservedSpaceInGB |
|---|---|---|---|---|
| 108579475 | 2892280 | 1 | 254,34 | 254,37 |
| 1952114095 | 834306760 | 1 | 79,73 | 79,74 |
| 418204640 | 20233590 | 1 | 23,52 | 23,53 |
| 1599396817 | 6346104 | 1 | 6,63 | 6,74 |
| 1939590048 | 596471 | 1 | 4,75 | 4,75 |
Também fiz as seguintes considerações:
- Encontrei esta postagem explicando um truque usando grupos de arquivos, mas até onde sei, não é possível gerenciá-los no Banco de Dados SQL do Azure.
- O problema pode estar relacionado ao fato de eu ter deletado muitos LOBs. Encontrei o
dbcc forceghostcleanup (<db_id>, 'visit_all_pages')comando, mas estou hesitante em experimentá-lo. - Para experimentar os comandos do dbcc, criei um clone do banco de dados a partir de um backup. Acho que isso exclui qualquer possível problema relacionado a transações ativas que mantêm versões de linha do armazenamento de versão da recuperação acelerada do banco de dados.
- Idealmente, eu gostaria de evitar o máximo possível (use como último recurso) o processo de copiar dados e descartar a tabela original ou coisas assim.
A maioria das tabelas no banco de dados são índices clusterizados de armazenamento de linhas, exceto o de 6,63 GB, que é um índice clusterizado de armazenamento de colunas, e sete heaps que se enquadram na marca de 40 MB, tanto alocados quanto usados. Todas as tabelas submetidas à exclusão se enquadram na primeira categoria e também não possuem índices não clusterizados.
Acabei de tentar 'DBCC UPDATEUSAGE , but it doesn't seem to change anything; sp_spaceused' retorna os mesmos valores.
Você tem alguma percepção?
