La dimensione del database SQL non è corretta e il file del database cresce mentre è vuoto
RISOLTO - SOLUZIONE IN FONDO AL POST
PROBLEMA Ho un file di dati del database visualizzato come 200 GB su disco e 200 GB di spazio allocato nelle proprietà del database. sp_spaceused mostra anche 200 GB allocati. Tutti mostrano quasi zero spazio libero.
Tuttavia, l'esecuzione di query sulle dimensioni della tabella e dell'indice mostra che nel database sono presenti solo circa 4 GB di dati. Credo che questo sia corretto. Ho una copia del database che ho eseguito UPDATEUSAGE che ha aggiornato lo spazio allocato e libero come previsto mostrando 196 GB di spazio libero improvvisamente disponibile. Questo mi ha permesso di ridurre il database a un ragionevole 10 GB.
Tuttavia, sul database live (in una finestra di manutenzione) quando provo UPDATEUSAGE e sp_spaceused @updateusage = 'true' non si aggiorna e non mostra lo spazio libero disponibile corretto. Ho provato a riciclare il servizio SQL Server e di nuovo a eseguire questi comandi ma non cambia nulla.
Qualcuno ha notato problemi con le statistiche dello spazio allocato e disponibile e qualche idea su come risolverlo? Le tabelle di registro sono vuote e il backup viene eseguito ogni ora, quindi non contribuiscono molto a queste cifre.
Il secondo problema è ovviamente capire perché il database sta crescendo in questo modo. Sembra che SQL pensi che non ci sia spazio libero, quindi aumenta il file con ogni inserimento. Ho letto alcune operazioni DDL e gli heap non aggiornano correttamente lo spazio non allocato, quindi questo è qualcosa da esaminare. Ho controllato lo spazio utilizzato e disponibile da ciascuno dei cumuli ma questi non mi sembrano eccessivi. Sospetto che sia qualcosa correlato a un processo ETL del datawarehouse. Qualcun altro ha visto qualcosa di simile?
Per ora, devo essere in grado di ridurre questo database poiché sta crescendo rapidamente e in modo errato ed è quasi completamente vuoto. Conosco la frammentazione dopo la contrazione e lo farò.
Grazie.
EDIT Esecuzione della seguente query per ottenere le dimensioni della tabella:
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
Mostra le dimensioni della tabella, inclusi gli indici. Sommando ciò, vengono restituiti i circa 4 GB previsti. Ho anche controllato le viste indicizzate e la loro dimensione è trascurabile.
TotalSpaceMB
3775.16
Tuttavia sp_spaceused restituisce questo:
database_size unallocated space
197479.50 MB 181.38 MB
reserved data index_size unused
201396352 KB 200640656 KB 534448 KB 221248 KB
Questo mi suggerisce che il problema è con i cumuli. Ma non posso spiegare i 196 GB mancanti.
EDIT 2 Come suggerito da SQLpro, potrebbe essere internal_tables utilizzando lo spazio. Ciò sembrava probabile poiché utilizziamo il broker di servizi e il rilevamento delle modifiche. Mi è piaciuto anche il suggerimento perché potrebbe aver spiegato perché la copia di TEST potrebbe avere le informazioni sullo spazio allocato / inutilizzato aggiornate se il broker del servizio non era in esecuzione / abilitato.
Tuttavia, ho utilizzato la seguente query per sommare lo spazio riservato e utilizzato, ma rappresenta solo circa 1,5 GB.
SELECT SUM(ReservedMB) ReservedMB, SUM(UsedSpaceMB) UsedMB
FROM (SELECT s.name, it.name tname,
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used,
CAST(ROUND(((SUM(ps.reserved_page_count) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS ReservedMB,
CAST(ROUND(((SUM(ps.used_page_count) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
JOIN sys.schemas AS s ON it.schema_id = s.schema_id
GROUP BY it.parent_id, it.name, s.name)ds
risultati
ReservedMB UsedMB
1318.66 1288.65
SOLUZIONE
Come accennato da SQLpro, il problema si è rivelato essere stato causato dal broker del servizio. Tutte le mie precedenti query sulle dimensioni delle tabelle non hanno trovato la particolare tabella interna che era la causa del problema. Tuttavia, dopo un po 'di ricerche ho trovato questa domanda:
/* Size of all internal and normal table objects */
SELECT name = OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id),
rows = SUM(CASE
WHEN index_id < 2
THEN row_count
ELSE 0
END),
reserved_mb = 8 * SUM(reserved_page_count) / 1024,
data_mb = 8 * SUM(CASE
WHEN index_id < 2
THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
ELSE lob_used_page_count + row_overflow_used_page_count
END) / 1024,
index_mb = 8 * (SUM(used_page_count) - SUM(CASE
WHEN index_id < 2
THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
ELSE lob_used_page_count + row_overflow_used_page_count
END)) / 1024,
unused_mb = 8 * SUM(reserved_page_count - used_page_count) / 1024
FROM sys.dm_db_partition_stats
GROUP BY object_id
ORDER BY reserved_mb DESC;
Questo ha mostrato subito il problema. Record migliore:
name rows reserved_mb data_mb index_mb unused_mb
sys.sysxmitqueue 264598 198097 198082 3 11
Grazie a tutti coloro che hanno aiutato. Ho anche appreso del problema con i cumuli che era nuovo per me.
Risposte
Ho riscontrato un problema simile, quando si utilizza Service Broker che utilizza tabelle interne non visualizzate in molte query eseguite per calcolare la quantità di dati. Uso :
DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = @SQL + N'EXEC sp_spaceused ''' + s.name + '.' + it.name + ''';'
FROM sys.internal_tables AS it
JOIN sys.schemas AS s
ON it.schema_id = s.schema_id;
EXEC (@SQL);
Per vedere quei volumi
Ogni allocazione ha un elenco collegato di pagine IAM. Se ignoriamo il partizionamento e le LOB e simili per mantenere la discussione diretta, puoi immaginare che ogni indice abbia una serie di IAM; e i dati effettivi (siano essi indice cluster o heap) hanno anche una serie di IAM.
Un IAM mappa 2 GB nel file di database, riflettendo quali estensioni appartengono a questa allocazione (heap / indice). Se l'allocazione utilizza spazio al di fuori di questi 2 GB, quando ha un IAM in più (per 2 GB utilizza lo spazio nel file di database), gli IAM vengono collegati come un elenco collegato.
Le query che ti dicono quanto è grande una tabella o quanto è pieno un database non passano da questi IAM, ciò richiederebbe troppo tempo. Usano tabelle di sistema (esposte attraverso le viste) che contengono statistiche sull'utilizzo dello spazio. Questi possono essere disattivati, come sai. Ma il fatto che siano spenti non ha alcuna rilevanza quando viene assegnato lo spazio. Quando SQL Server ha bisogno di trovare spazio libero, utilizza pagine come GAM, SGAM e IAM (e, possibilmente, PFS). Quindi, se si dispone di un mucchio che secondo gli IAM (e indirettamente lo SGAM) possiede moltissime estensioni (anche se non ci sono dati lì), queste estensioni non possono essere utilizzate da qualcos'altro.
Fare UPDATEUSAGE non aiuterà, poiché quel comando leggerà semplicemente queste strutture (forse solo IAM) per riflettere nelle tabelle di sistema quanto è grande un'allocazione. Cioè, l'esecuzione di UPDATEUSAGE non era ciò che ti ha permesso di rimpicciolirti, lo shrink avrebbe funzionato altrettanto bene senza UPDATEUSAGE.
Quindi, i tuoi cumuli sono apparentemente (se il mio ragionamento è valido) il tuo problema. Cosa fare? Non avere cumuli o ricostruirli. Ma se mantieni i tuoi cumuli, il problema tornerà.