Ir al contenido principal

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

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
  12. Buenas Tardes

    Tu script me resulto muy útil, gracias.

    Saludos

    ResponderEliminar
  13. Hola

    un script para saber el tamaño de un segmento de registros que existen en una tabla?

    ResponderEliminar
  14. Muchas gracias!!! me sirvió de mucho tu trabajo.

    ResponderEliminar
  15. Hola Excelentes Scrip e apoyo. Quisiera consultar si cuenta alguien con un scrip de las tablas más utilizadas en una instancia sql? Muchas gracias de antemano.

    ResponderEliminar
  16. Muchas gracias por aporte amigo me sivio mucho.

    ResponderEliminar
  17. Hola como estos ejemplos, habrá algun script que me permita saber pero el tamño de los logs de cada una de mis bases?

    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola:

      Si solamente necesitas saber el tamaño de los logs de cada una de tu base de datos, debes hacer uso de la DMV (Data Management View) sys.dm_db_log_space_usage.

      Puedes encontrar la documentación de esta vista en este enlace: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-space-usage-transact-sql?view=sql-server-ver15

      Observa que te devuelve el tamaño del log total y el usado en bytes, por lo que deberás dividir entre 1024 para verlo en KB o 1024*1024 para verlo en MB.

      Saludos.

      Eliminar
  18. Buenas tardes.
    Alguien podría pasarme un script }que me muestre el tamaño de todas las tablas en MB.
    Gracias.

    ResponderEliminar
  19. Excelentes aportaciones, muy utiles, super agradecido.

    ResponderEliminar
  20. Excelnete aporte amigo, me sirvio mucho el scrip, gracias!!!

    ResponderEliminar

Publicar un comentario

Entradas populares de este blog

Aprendiendo a usar LEFT OUTER JOIN

En esta entrada pretendemos explicar los diferentes resultados obtenidos por distintas construcciones de consultas que, aparentemente, deberían producir el mismo conjunto de resultados. Así, veremos las diferencias entre filtrar los resultados de una query en la unión (Join) mediante condiciones ON y mediante cláusulas WHERE.

Variantes del SELECT COUNT con DISTINCT

Seguramente, muchos de vosotros habréis usado en innumerables ocasiones la función de T-SQL COUNT , que no hace sino devolver un número de registros: de una tabla, de un conjunto de resultados, etc... En una de sus aplicaciones, combinado con el DISTINCT -uno de los dos argumentos que admite- COUNT nos devuelve el número de valores únicos no nulos de la tabla o conjunto de resultados que estemos consultando. Pero ¡ojo! Cuidado con la sintaxis , o podemos obtener el valor equivocado sin darnos cuenta. No es lo mismo: SELECT COUNT (DISTINCT NombreCampo) FROM NombreTabla que: SELECT COUNT(*), DISTINCT NombreCampo FROM NombreTabla