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
32
| WITH
PG AS
/******************************************************************************
* Fred Brouard, SQLpro - Sté SQL spot - https://www.sqlspot.com/ - 2024-06-25 *
*******************************************************************************
* REQUÊTE DE MÉTADONNÉES : volume occupation fichiers des objets de la base *
* ATTENTION : dmv "sys.dm_db_database_page_allocations" non documentée par MS *
******************************************************************************/
(
SELECT DISTINCT object_id, index_id, extent_file_id AS FILEID,
CAST(COUNT(allocated_page_page_id)/128 AS DECIMAL(18,2)) AS SIZE_MB,
COUNT(DISTINCT partition_id) AS NB_PARTITIONS
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, N'LIMITED')
GROUP BY object_id, index_id, extent_file_id
)
SELECT s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME, o.type_desc AS TABLE_TYPE,
COALESCE(i.name, '<<<HEAP>>>') AS INDEX_NAME,
fg.name AS STORAGE_FILEGROUP, df.name AS STORAGE_FILE,
SIZE_MB AS OBJECT_SIZE_MB, NB_PARTITIONS,
SUM(SIZE_MB) OVER(PARTITION BY o.object_id) AS TOTAL_TABLE_SIZE_MB
FROM PG
JOIN sys.objects AS o
ON PG.object_id = o.object_id
JOIN sys.indexes AS i
ON PG.object_id = i.object_id AND PG.index_id = i.index_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.database_files AS df
ON PG.FILEID = df.file_id
JOIN sys.filegroups AS fg
ON df.data_space_id = fg.data_space_id
WHERE o.type IN ('U', 'V'); |
Répartir "manuellement" les données entre fichiers d'une même base
)
Répondre avec citation
Envoyé par StringBuilder
Partager