Como obtener el tamaño de las todas las tablas de una base de datos SQL Server

Muchas veces, el tamaño de una base de datos se nos dispara y no sabemos muy bien porqué. Suele ocurrir por tablas de traza principalmente, pero a veces es difícil saber exactamente donde está el "problema".

Hay varias opciones para obtener el tamaño de las tablas de una base de datos SQL Server, pero aquí os voy a poner el que más me gusta a mi, porque es un script que se ejecuta en la base de datos que queremos analizar:



SELECT a3.name + '.' + a2.name AS [name], 
   a1.rows AS [rows], 
   (a1.reserved + ISNULL(a4.reserved, 0)) * 8 AS [Reserved KB], 
   a1.data * 8 AS [Data KB], 
   (CASE
        WHEN(a1.used + ISNULL(a4.used, 0)) > a1.data
        THEN(a1.used + ISNULL(a4.used, 0)) - a1.data
        ELSE 0
    END) * 8 AS [Index_size KB], 
   (CASE
        WHEN(a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
        THEN(a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
        ELSE 0
    END) * 8 AS [unused KB], 
   CONVERT(DECIMAL(18, 2), (((a1.reserved + ISNULL(a4.reserved, 0)) * 8) - ((CASE
                                                                                 WHEN(a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
                                                                                 THEN(a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
                                                                                 ELSE 0
                                                                             END) * 8)) / 1024.0 / 1024.0) AS [Table_used_Space GB]
FROM
(
SELECT ps.object_id, 
       SUM(CASE
               WHEN(ps.index_id < 2)
               THEN row_count
               ELSE 0
           END) AS [rows], 
       SUM(ps.reserved_page_count) AS reserved, 
       SUM(CASE
               WHEN(ps.index_id < 2)
               THEN(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
               ELSE(ps.lob_used_page_count + ps.row_overflow_used_page_count)
           END) AS data, 
       SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id
) AS a1
LEFT OUTER JOIN
(
SELECT it.parent_id, 
       SUM(ps.reserved_page_count) AS reserved, 
       SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON(it.object_id = ps.object_id)
WHERE it.internal_type IN(202, 204)
GROUP BY it.parent_id
) AS a4 ON(a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON(a1.object_id = a2.object_id)
INNER JOIN sys.schemas a3 ON(a2.schema_id = a3.schema_id)
WHERE a2.type != N'S'
  AND a2.type != N'IT'

ORDER BY [Table_used_Space GB] DESC, [rows] desc ;

El resultado es este:

Este script no es mío. Es de Vivek Janakiraman que lo publicó en las galerías Technet de Microsoft.

Blog

Como obtener el tamaño de las todas las tablas de una base de datos SQL Server

Diego

Join Our Newsletter. No Spam, Only the good stuff.

Related