Buscar este blog

martes, 13 de noviembre de 2012

Script para obtener el tamaño de todas las tablas de la base de datos

En algunas ocasiones podemos vernos con la necesidad de conocer qué tablas de nuestra base de datos están ocupando más espacio en disco. Por ejemplo, si disponemos de SQL Server Express, cuyas bases de datos están limitadas a 4GB o 10GB, según la versión que estemos usando -4, hasta 2005; 10, a partir de 2008-, aparte de usar las opciones de comprimir la base de datos, poner el log en el modo simple de recuperación o ajustar las políticas de crecimiento automático de nuestros ficheros, podemos necesitar averiguar qué tablas crecen más para tomar las decisiones oportunas.

Para ello, podemos mirar tabla por tabla sus propiedades o usar el siguiente script que nos devolverá una fila por cada tabla, con su espacio usado y reservado:


SET NOCOUNT ON 
DBCC UPDATEUSAGE(0) 
-- Table row counts and sizes.
DECLARE @sizes TABLE
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)
INSERT @sizes EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
SELECT *
FROM   @sizes
ORDER BY convert(int, substring(data, 1, len(data)-3)) desc

23 comentarios:

  1. Gracias, acabo de toparme con una base de la que no se puede generar reportes y tu Script me ahorro mucho trabajo.

    ResponderEliminar
    Respuestas
    1. ¡Hola!

      Me alegro de que haya sido así, intentaremos seguir ayudando desde este blog con próximas entradas.

      Saludos

      Eliminar
  2. Jaime un script para conocer el perfilamiento de tablas, es decir evaluar la calidad de datos

    ResponderEliminar
    Respuestas
    1. Hola, Luis Enrique: No acabo de entender si me estás preguntando cómo comprobar la integridad de los datos en una tabla. Si es así, debes usar DBCC CHECKTABLE o DBCC CHECKDB para la base de datos completa. Si consultas estos comandos en la MSDN verás que son sencillos de usar.

      Espero haberte aclarado la duda. Si no ha sido así, no dudes en hacer cualquier comentario adicional.

      Saludos.

      Eliminar
  3. Buen día Jaime

    Existe algún script que me ayude a saber el tamaño de todas mis bases de datos? Ordenándolas de Mayor a Menor.

    Requiero hacer reporte de cuales están creciendo semanalmente mas rápido.

    ResponderEliminar
    Respuestas
    1. Hola Irvin:

      Muy buena pregunta.
      Por supuesto, sí que es posible obtener lo que tú quieres.
      Si lo que quieres es el tamaño total de cada base de datos, sin importar cuánto ocupa cada fichero (Datos, Log), creo que ésta opción es la mejor:

      SELECT d.name,
      ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
      FROM sys.master_files mf
      INNER JOIN sys.databases d ON d.database_id = mf.database_id
      WHERE d.database_id > 4 -- Skip system databases
      GROUP BY d.name
      ORDER BY Size_MBs DESC

      La consulta la he obtenido de este enlace, y la he revisado y modificado para ordenar el resultado como tu pedías:

      http://www.codeproject.com/Tips/469070/SQL-Server-Get-All-Databases-Size

      Si necesitas algo más complejo, se puede cambiar esa query y adaptarla.

      Espero haberte ayudado.

      Saludos

      Eliminar
    2. Es posible que necesites algo más complejo, como saber qué parte de cada fichero está realmente en uso y qué parte está libre o reservada.
      En esos casos, existen varios artículos en inglés que puedes encontrar a través de tu buscador favorito.
      Por ejemplo, éste: https://www.mssqltips.com/sqlservertip/1629/determine-free-space-consumed-space-and-total-space-allocated-for-sql-server-databases/

      Saludos.

      Eliminar
  4. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  5. Dejo este escript que tiene como resultado el tamaño de tablas de las bases de datos que existan en una instancia

    SET NOCOUNT ON
    DBCC UPDATEUSAGE(0)
    -- Table row counts and sizes.
    DECLARE @sizes TABLE
    (
    Servidor NVARCHAR(128),
    DB NVARCHAR(128),
    Tabla NVARCHAR(128),
    [Rows] CHAR(11),
    Reserved VARCHAR(18),
    Data VARCHAR(18),
    Index_size VARCHAR(18),
    Unused VARCHAR(18)
    )
    --INSERT @sizes EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
    INSERT @sizes
    exec sp_msforeachdb @command1='USE ?; SELECT @@servername Servidor ,
    DB_NAME() DB,
    X.[name] Tabla,
    --REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), ''.00'', '')
    X.[rows],
    --REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), ''.00'', '')
    X.[reserved],
    --REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), ''.00'', '')
    X.[data],
    --REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), ''.00'', '')
    X.[index_size],
    --REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), ''.00'', '')
    X.[unused]
    FROM
    (SELECT
    CAST(object_name(id) AS varchar(50))
    AS [name],
    SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END)
    AS [rows],
    SUM(CONVERT(bigint, reserved)) * 8
    AS reserved,
    SUM(CONVERT(bigint, dpages)) * 8
    AS data,
    SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8
    AS index_size,
    SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8
    AS unused
    FROM sysindexes WITH (NOLOCK)
    WHERE sysindexes.indid IN (0, 1, 255)
    AND sysindexes.id > 100
    AND object_name(sysindexes.id) <> ''dtproperties''
    GROUP BY sysindexes.id WITH ROLLUP
    ) AS X
    WHERE X.[name] is not null
    ORDER BY X.[rows] DESC'

    SELECT *
    FROM @sizes
    --ORDER BY convert(int, substring(data, 1, len(data)-3)) desc

    ResponderEliminar
  6. Hola,

    Habrá algún query que me ayude a obtener el tamaño de las tablas de mi base de datos.

    Te lo agradeceria mucho.

    ResponderEliminar
    Respuestas
    1. Hola, Claudia:

      El script que hay en este artículo sirve precisamente para eso. Espero que te sea de utilidad.

      Saludos.

      Eliminar
  7. Gracias por el aporte, expandió mi consciencia en SQL

    ResponderEliminar
  8. Hola que tal, muy útil el script.
    tendrás uno como determinar el tamaño del log que usa una consulta, ya que al ejecutarla tarda y al final manda un mensaje de que se lleno el log de la base.

    ResponderEliminar
  9. Hola, existe alguna consulta para saber cual es el peso en KB que traficará el resultado de una consulta sql?


    Saludos

    ResponderEliminar
    Respuestas
    1. Hola, César:

      Si ejecutas la consulta desde el SQL Server Management Studio, puedes hacer uso de la opción "Include Client Stats / Incluir Estadísticas del Cliente (Shift+Alt+S)". En ellas podrás ver información como la que solicitas.

      Eliminar
  10. Hola Jaime. Excelente, justo lo que necesitaba.
    Muchas gracias
    César

    ResponderEliminar
  11. Hola, existe algún script que me permita ingresar el nombre de una base de datos y y la cantidad de registros que ingresarán a la base de datos para indicar el tamaño estimado Data y log, de acuerdo a si los indices son cluster o no cluster?

    ResponderEliminar