Partilhar via

sp_spaceused (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure PDW (Sistema de Plataforma de Análise) do Azure Synapse Analytics

O sp_spaceused procedimento armazenado do sistema exibe:

  • o número de linhas, espaço em disco reservado e espaço em disco usado por uma tabela, exibição indexada ou fila do Service Broker no banco de dados atual

  • o espaço em disco reservado e usado por todo o banco de dados

Convenções de sintaxe de Transact-SQL

Sintaxe

sp_spaceused
    [ [ @objname = ] N'objname' ]
    [ , [ @updateusage = ] 'updateusage' ]
    [ , [ @mode = ] 'mode' ]
    [ , [ @oneresultset = ] oneresultset ]
    [ , [ @include_total_xtp_storage = ] include_total_xtp_storage ]
[ ; ]

Observação

Não há suporte a essa sintaxe para o pool de SQL sem servidor no Azure Synapse Analytics.

Argumentos

Para o Azure Synapse Analytics e o PDW (Analytics Platform System), sp_spaceused deve especificar parâmetros nomeados (por exemplo sp_spaceused (@objname= N'Table1');), em vez de depender da posição ordinal dos parâmetros.

@objname [ = ] n'objname'

O nome qualificado ou não qualificado da tabela, exibição indexada ou fila para a qual as informações de uso de espaço são solicitadas. @objname é nvarchar(776), com um padrão de NULL. As aspas são obrigatórias apenas se um nome de objeto qualificado for especificado. Se um nome de objeto totalmente qualificado (incluindo um nome de banco de dados) for fornecido, o nome de banco de dados deve ser o nome do banco de dados atual.

Se @objname não for especificado, os resultados serão retornados para todo o banco de dados.

Observação

O Azure Synapse Analytics e o PDW (Analytics Platform System) dão suporte apenas a objetos de banco de dados e tabela.

@updateusage [ = ] 'updateusage'

Indica DBCC UPDATEUSAGE que deve ser executado para atualizar as informações de uso do espaço. @updateusage é varchar(5), com um padrão de false. Quando @objname não é especificado, a instrução é executada em todo o banco de dados. Caso contrário, a instrução será executada em @objname. Os valores podem ser true ou false.

@mode [ = ] 'modo'

Indica o escopo dos resultados. Para uma tabela ou banco de dados estendido, o parâmetro @mode permite incluir ou excluir a parte remota do objeto. Para obter mais informações, consulte Stretch Database.

Importante

O banco de dados de ampliação foi preterido no SQL Server 2022 (16.x) e no Banco de Dados SQL do Azure. Esse recurso será removido em uma versão futura do mecanismo de banco de dados. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.

@mode é varchar(11) e pode ser um desses valores.

Valor Descrição
ALL (padrão) Retorna as estatísticas de armazenamento do objeto ou banco de dados, incluindo a parte local e a parte remota.
LOCAL_ONLY Retorna as estatísticas de armazenamento apenas da parte local do objeto ou banco de dados. Se o objeto ou banco de dados não estiver habilitado para Stretch, retornará as mesmas estatísticas de quando @mode é ALL.
REMOTE_ONLY Retorna as estatísticas de armazenamento apenas da parte remota do objeto ou banco de dados. Essa opção gera um erro quando uma das seguintes condições é verdadeira:

A tabela não está habilitada para Stretch.

A tabela está habilitada para Stretch, mas você nunca habilitou a migração de dados. Nesse caso, a tabela remota ainda não tem um esquema.

O usuário soltou manualmente a tabela remota.

O provisionamento do arquivo de dados remoto retornou um status de Êxito, mas na verdade falhou.

@oneresultset [ = ] um conjunto de resultados

Indica se um único conjunto de resultados deve ser retornado. @oneresultset é bit e pode ser um destes valores:

Valor Descrição
0 (padrão) Quando @objname é nulo ou não é especificado, dois conjuntos de resultados são retornados.
1 Quando @objname é NULL ou não especificado, um único conjunto de resultados é retornado.

@include_total_xtp_storage [ = ] include_total_xtp_storage

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores e Banco de Dados SQL

Quando @oneresultset é definido como 1, esse parâmetro determina se o conjunto de resultados único inclui colunas para MEMORY_OPTIMIZED_DATA armazenamento. @include_total_xtp_storage é bit, com um padrão de 0. Se 1, as colunas XTP são incluídas no conjunto de resultados.

Valores do código de retorno

0 (sucesso) ou 1 (falha).

Conjunto de resultados

Se @objname for omitido e o valor de @oneresultset for 0, os conjuntos de resultados a seguir serão retornados para fornecer informações atuais sobre o tamanho do banco de dados.

Nome da coluna Tipo de dados Descrição
database_name nvarchar(128) Nome do banco de dados atual.
database_size varchar(18) Tamanho do banco de dados atual em megabytes. database_size Inclui arquivos de dados e de log.
unallocated space varchar(18) Espaço no banco de dados que não é reservado para objetos de banco de dados.
Nome da coluna Tipo de dados Descrição
reserved varchar(18) Total de espaço alocado por objetos no banco de dados.
data varchar(18) Total de espaço usado por dados.
index_size varchar(18) Total de espaço usado por índices.
unused varchar(18) Total de espaço reservado para objetos no banco de dados, mas ainda não usado.

Se @objname for omitido e o valor de @oneresultset for 1, o conjunto de resultados único a seguir será retornado para fornecer informações atuais sobre o tamanho do banco de dados.

Nome da coluna Tipo de dados Descrição
database_name nvarchar(128) Nome do banco de dados atual.
database_size varchar(18) Tamanho do banco de dados atual em megabytes. database_size Inclui arquivos de dados e de log.
unallocated space varchar(18) Espaço no banco de dados que não é reservado para objetos de banco de dados.
reserved varchar(18) Total de espaço alocado por objetos no banco de dados.
data varchar(18) Total de espaço usado por dados.
index_size varchar(18) Total de espaço usado por índices.
unused varchar(18) Total de espaço reservado para objetos no banco de dados, mas ainda não usado.

Se @objname for especificado, o conjunto de resultados a seguir será retornado para o objeto especificado.

Nome da coluna Tipo de dados Descrição
name nvarchar(128) Nome do objeto para o qual foram solicitadas informações de uso do espaço.

O nome do esquema do objeto não é retornado. Se o nome do esquema for necessário, use as exibições de gerenciamento dinâmico sys.dm_db_partition_stats ou sys.dm_db_index_physical_stats para obter informações de tamanho equivalente.
rows Char(20) Número de linhas existentes na tabela. Se o objeto especificado for uma fila do Service Broker, essa coluna indicará o número de mensagens na fila.
reserved varchar(18) Quantidade total de espaço reservado para @objname.
data varchar(18) Quantidade total de espaço usado pelos dados em @objname.
index_size varchar(18) Quantidade total de espaço usado por índices em @objname.
unused varchar(18) Quantidade total de espaço reservado para @objname mas ainda não utilizado.

Este modo é o padrão, quando nenhum parâmetro é especificado. Os conjuntos de resultados a seguir são retornados detalhando as informações de tamanho do banco de dados em disco.

Nome da coluna Tipo de dados Descrição
database_name nvarchar(128) Nome do banco de dados atual.
database_size varchar(18) Tamanho do banco de dados atual em megabytes. database_size Inclui arquivos de dados e de log. Se o banco de dados tiver um MEMORY_OPTIMIZED_DATA grupo de arquivos, esse valor incluirá o tamanho total em disco de todos os arquivos de ponto de verificação no grupo de arquivos.
unallocated space varchar(18) Espaço no banco de dados que não é reservado para objetos de banco de dados. Se o banco de dados tiver um MEMORY_OPTIMIZED_DATA grupo de arquivos, esse valor incluirá o tamanho total em disco dos arquivos de ponto de verificação com estado PRECREATED no grupo de arquivos.

Espaço usado por tabelas no banco de dados. Esse conjunto de resultados não reflete tabelas com otimização de memória, pois não há contabilidade por tabela do uso do disco:

Nome da coluna Tipo de dados Descrição
reserved varchar(18) Total de espaço alocado por objetos no banco de dados.
data varchar(18) Total de espaço usado por dados.
index_size varchar(18) Total de espaço usado por índices.
unused varchar(18) Total de espaço reservado para objetos no banco de dados, mas ainda não usado.

O conjunto de resultados a seguir será retornado somente se o banco de dados tiver um grupo de MEMORY_OPTIMIZED_DATA arquivos com pelo menos um contêiner:

Nome da coluna Tipo de dados Descrição
xtp_precreated varchar(18) Tamanho total dos arquivos de ponto de verificação com estado PRECREATED, em KB. Conta para o espaço não alocado no banco de dados como um todo. Por exemplo, se houver 600.000 KB de arquivos de ponto de verificação pré-criados, essa coluna conterá 600000 KB.
xtp_used varchar(18) Tamanho total dos arquivos de ponto de verificação com estados UNDER CONSTRUCTION, ACTIVEe MERGE TARGET, em KB. Esse valor é o espaço em disco usado ativamente para dados em tabelas com otimização de memória.
xtp_pending_truncation varchar(18) Tamanho total dos arquivos de ponto de verificação com estado WAITING_FOR_LOG_TRUNCATION, em KB. Esse valor é o espaço em disco usado para arquivos de ponto de verificação que estão aguardando limpeza, depois que o truncamento de log acontece.

Se @objname for omitido, o valor de @oneresultset é 1, e @include_total_xtp_storage é 1, o conjunto de resultados único a seguir é retornado para fornecer informações atuais sobre o tamanho do banco de dados. Se @include_total_xtp_storage for 0 (o padrão), as três últimas colunas serão omitidas.

Nome da coluna Tipo de dados Descrição
database_name nvarchar(128) Nome do banco de dados atual.
database_size varchar(18) Tamanho do banco de dados atual em megabytes. database_size Inclui arquivos de dados e de log. Se o banco de dados tiver um MEMORY_OPTIMIZED_DATA grupo de arquivos, esse valor incluirá o tamanho total em disco de todos os arquivos de ponto de verificação no grupo de arquivos.
unallocated space varchar(18) Espaço no banco de dados que não é reservado para objetos de banco de dados. Se o banco de dados tiver um MEMORY_OPTIMIZED_DATA grupo de arquivos, esse valor incluirá o tamanho total em disco dos arquivos de ponto de verificação com estado PRECREATED no grupo de arquivos.
reserved varchar(18) Total de espaço alocado por objetos no banco de dados.
data varchar(18) Total de espaço usado por dados.
index_size varchar(18) Total de espaço usado por índices.
unused varchar(18) Total de espaço reservado para objetos no banco de dados, mas ainda não usado.
xtp_precreated 1 varchar(18) Tamanho total dos arquivos de ponto de verificação com estado PRECREATED, em KB. Esse valor conta para o espaço não alocado no banco de dados como um todo. Retorna NULL se o banco de dados não tiver um grupo de MEMORY_OPTIMIZED_DATA arquivos com pelo menos um contêiner.
xtp_used 1 varchar(18) Tamanho total dos arquivos de ponto de verificação com estados UNDER CONSTRUCTION, ACTIVEe MERGE TARGET, em KB. Esse valor é o espaço em disco usado ativamente para dados em tabelas com otimização de memória. Retorna NULL se o banco de dados não tiver um grupo de MEMORY_OPTIMIZED_DATA arquivos com pelo menos um contêiner.
xtp_pending_truncation 1 varchar(18) Tamanho total dos arquivos de ponto de verificação com estado WAITING_FOR_LOG_TRUNCATION, em KB. Esse valor é o espaço em disco usado para arquivos de ponto de verificação que estão aguardando limpeza, depois que o truncamento de log acontece. Retorna NULL se o banco de dados não tiver um grupo de MEMORY_OPTIMIZED_DATA arquivos com pelo menos um contêiner.

1 Incluído apenas se @include_total_xtp_storage estiver definido como 1.

Comentários

O database_size valor geralmente é maior que a soma de porque inclui o tamanho dos arquivos de log, mas unallocated_space reserved considera apenas as páginas de reserved + unallocated space dados. Em alguns casos com o Azure Synapse Analytics, essa instrução pode não ser verdadeira.

As páginas usadas por índices XML e índices de texto completo são incluídas em index_size ambos os conjuntos de resultados. Quando @objname é especificado, as páginas dos índices XML e de texto completo do objeto também são contadas no total reserved e index_size nos resultados.

Se o uso do espaço for calculado para um banco de dados ou um objeto que seja um índice espacial, as colunas de tamanho de espaço, como database_size, reservede index_size, incluirão o tamanho do índice espacial.

Quando @updateusage é especificado, o Mecanismo de Banco de Dados do SQL Server verifica as páginas de dados no banco de dados e faz as correções necessárias nas exibições do catálogo e sys.partitions em sys.allocation_units relação ao espaço de armazenamento usado por cada tabela. Há algumas situações, por exemplo, depois que um índice é descartado, quando as informações de espaço da tabela podem não estar atualizadas. @updateusage pode levar algum tempo para ser executado em tabelas ou bancos de dados grandes. Use @updateusage somente quando suspeitar que valores incorretos estão sendo retornados e quando o processo não tiver um efeito adverso em outros usuários ou processos no banco de dados. Se preferir, DBCC UPDATEUSAGE pode ser executado separadamente.

Observação

Quando você descarta ou reconstrói índices grandes, ou descarta ou trunca tabelas grandes, o Mecanismo de Banco de Dados adia as desalocações de página atuais e seus bloqueios associados, até depois que a transação confirme. As operações de descarte adiadas não liberam o espaço alocado imediatamente. Portanto, os valores retornados imediatamente sp_spaceused após descartar ou truncar um objeto grande podem não refletir o espaço em disco real disponível.

Permissões

A permissão para executar sp_spaceused é concedida à função pública . Somente os membros da função de banco de dados fixa db_owner podem especificar o parâmetro @updateusage.

Exemplos

R. Exibir informações de espaço em disco sobre uma tabela

O exemplo a seguir relata informações de espaço em disco para a tabela Vendor e seus índices.

USE AdventureWorks2022;
GO
EXEC sp_spaceused N'Purchasing.Vendor';
GO

B. Exibir informações de espaço atualizadas sobre um banco de dados

O exemplo a seguir resume o espaço usado no banco de dados atual e usa o parâmetro opcional @updateusage para garantir que os valores atuais sejam retornados.

USE AdventureWorks2022;
GO
EXEC sp_spaceused @updateusage = N'TRUE';
GO

C. Exibir informações de uso de espaço sobre a tabela remota associada a uma tabela habilitada para Stretch

O exemplo a seguir resume o espaço usado pela tabela remota associada a uma tabela habilitada para Stretch usando o argumento @mode para especificar o destino remoto. Saiba mais em Stretch Database.

USE StretchedAdventureWorks2022;
GO

EXEC sp_spaceused N'Purchasing.Vendor', @mode = 'REMOTE_ONLY';

D. Exibir informações de uso de espaço para um banco de dados em um único conjunto de resultados

O exemplo a seguir resume o uso de espaço para o banco de dados atual em um único conjunto de resultados.

USE AdventureWorks2022;
GO
EXEC sp_spaceused @oneresultset = 1;

E. Exibir informações de uso de espaço para um banco de dados com pelo menos um grupo de arquivos MEMORY_OPTIMIZED em um único conjunto de resultados

O exemplo a seguir resume o uso de espaço para o banco de dados atual com pelo menos um MEMORY_OPTIMIZED grupo de arquivos em um único conjunto de resultados.

USE WideWorldImporters
GO

EXEC sp_spaceused @updateusage = 'FALSE',
    @mode = 'ALL',
    @oneresultset = '1',
    @include_total_xtp_storage = '1';
GO

F. Exibir informações de uso de espaço para um objeto de tabela MEMORY_OPTIMIZED em um banco de dados

O exemplo a seguir resume o uso de espaço para um MEMORY_OPTIMIZED objeto de tabela no banco de dados atual com pelo menos um MEMORY_OPTIMIZED grupo de arquivos.

USE WideWorldImporters
GO

EXEC sp_spaceused @objname = N'VehicleTemparatures',
    @updateusage = 'FALSE',
    @mode = 'ALL',
    @oneresultset = '0',
    @include_total_xtp_storage = '1';
GO

Conteúdo relacionado