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.
