Ver tamaño de las bases de datos SQL Server

Tamaños de las bases de datos en SQL Server

En este artículo se explica como obtener el tamaño de las bases de datos en una instancia de SQL Server. Es muy útil saber el tamaño de las bases de datos para tener un estimado del espacio necesario en el servidor y crear una línea base para mantener siempre disponibles las bases de datos. Una base de datos al crearla se puede definir el tamaño de los archivos que la componen, sea el archivo primario, los archivos secundarios y los de transacciones.

Para más información ver

Crear bases de datos
Archivos de base de datos
Grupos de archivos
Modificación de una base de datos

Para visualizar las bases de datos existentes en una instancia de SQL Server se usa la siguiente instrucción:

select
D.name As ‘Nombre BD’, D.physical_database_name As ‘Nombre en el disco’,
D.database_id As ‘Id’, D.create_date As ‘Fecha creación’,
D.compatibility_level As ‘Nivel Compatibilidad’,
D.Collation_name As ‘Intercalación’, D.state_desc As ‘Estado’
from sys.databases As D
go
Se han mostrado algunos campos, la imagen muestra el resultado.

Para mostrar los archivos de todas las bases de datos

select
[database_id] As ‘Id BD’, name As ‘Nombre SQL’,
[type_desc] As ‘Tipo’, [physical_name] As ‘Nombre y ubicación’,
Size As ‘Tamaño en Pages’
from sys.master_files
go
Se puede ver los tamaños de los archivos en Pages.

Para listar las bases de datos y los archivos de que las componen

select
B.name As ‘Base de datos’,
A.name As ‘Archivo de base de datos’,
A.size As ‘Tamaño en páginas’,
Format((SUM(A.Size)* 8 / 1024.0),’###,##0.00′) As ‘Tamaño MB’
from sys.databases As B
join sys.master_files As A on B.database_id = A.database_id
Group by B.name, A.name , A.size
go

Para listar los archivos de la base de datos abierta

use Northwind
go
select name as ‘Nombre’,
physical_name As ‘Ubicación y nombre’,
Size As ‘Tamaño en páginas’,
Format((SUM(Size)* 8 / 1024.0),’###,##0.00′) As ‘Tamaño MB’
from sys.database_files
Group by name, physical_name, size
go

Para listar todas las bases de datos y sus tamaños.

SELECT
db_name(database_id) AS ‘Base de datos’,
isNull(iif(type = 0, ‘Archivo de datos’,’Archivo de Transacciones’),’Total’)
As ‘Tipo de archivo’,
Format((SUM(Size)* 8 / 1024.0),’###,##0.00′) As ‘Tamaño MB’
FROM sys.master_files
Group by Grouping sets (
(db_name(database_id),
iif(type = 0, ‘Archivo de datos’,’Archivo de Transacciones’)), (db_name(database_id)))
Order by db_name(database_id),
iif(type = 0, ‘Archivo de datos’,’Archivo de Transacciones’) DESC
go
La imagen muestra los archivos de cada base de datos y al final de cada uno el total de la base de datos

Para listar una base de datos y el tamaño de sus archivos, ejemplo con la base de datos AdventureWorks

SELECT db_name(database_id) AS ‘Base de datos’,
isNull(iif(type = 0, ‘Archivo de datos’,’Archivo de Transacciones’),’Total’)
As ‘Tipo de archivo’,
Format((SUM(Size)* 8 / 1024.0),’###,##0.00′) As ‘Tamaño MB’
FROM sys.master_files
Where database_id = DB_ID(‘AdventureWorks’)
Group by Grouping sets (
(db_name(database_id),
iif(type = 0, ‘Archivo de datos’,’Archivo de Transacciones’)), (db_name(database_id)))
Order by db_name(database_id),
iif(type = 0, ‘Archivo de datos’,’Archivo de Transacciones’) DESC
go

Se puede incluir la instrucción en un SP para dar como parámetro el nombre de la base de datos

Create or alter procedure uspArchivosTamanioBDPorNombre
(
@NombreBD nvarchar(128)
)
As
SELECT
db_name(database_id) AS ‘Base de datos’,
isNull(iif(type = 0, ‘Archivo de datos’,’Archivo de Transacciones’),’Total’)
As ‘Tipo de archivo’,
Format((SUM(Size)* 8 / 1024.0),’###,##0.00′) As ‘Tamaño MB’
FROM sys.master_files
Where database_id = DB_ID(@NombreBD)
Group by Grouping sets (
(db_name(database_id),
iif(type = 0, ‘Archivo de datos’,’Archivo de Transacciones’)), (db_name(database_id)))
Order by db_name(database_id),
iif(type = 0, ‘Archivo de datos’,’Archivo de Transacciones’) DESC
go

Ejecutando el SP para la Base de datos Northwind

Execute uspArchivosTamanioBDPorNombre ‘Northwind’
go
La imagen muestra el resultado.