una - tamaño de todas las bases de datos sql server

Seleccionar el tamaño de la base de datos de SQL

Question

¿Cómo puedo consultar mi servidor sql para obtener solo el tamaño de la base de datos?

Usé esto:

use "MY_DB"
exec sp_spaceused

Tengo esto :

database_name   database_size   unallocated space
My_DB           17899.13 MB 5309.39 MB

Me devuelve varias columnas que no necesito, tal vez haya un truco para seleccionar la columna database_size de este procedimiento almacenado.

También probé este código:

SELECT DB_NAME(database_id) AS DatabaseName,
       Name AS Logical_Name,
       Physical_Name,
       (size * 8) / 1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = ''MY_DB''

Me da este resultado:

DatabaseName    Logical_Name    Physical_Name                    SizeMB
MY_DB           MY_DB           D:/MSSQL/Data/MY_DB.mdf          10613
MY_DB           MY_DB_log       D:/MSSQL/Data/MY_DB.ldf          7286

Entonces escribí esto:

SELECT SUM(SizeMB)
FROM (
    SELECT DB_NAME(database_id) AS DatabaseName,
           Name AS Logical_Name,
           Physical_Name,
           (size * 8) / 1024 SizeMB
    FROM sys.master_files
    WHERE DB_NAME(database_id) = ''MY_DB''
) AS TEMP

Tengo: 1183

¿Entonces funciona, pero tal vez hay una forma adecuada de obtener esto?

Answer 1

Funcionó perfectamente para calcular el tamaño de la base de datos SQL en SQL Server 2012

exec sp_spaceused

Answer 2

Prueba este -

Consulta:

SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = ''LOG'' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

Salida:

-- my query
name           log_size_mb  row_size_mb   total_size_mb
-------------- ------------ ------------- -------------
xxxxxxxxxxx    512.00       302.81        814.81

-- sp_spaceused
database_name    database_size      unallocated space
---------------- ------------------ ------------------
xxxxxxxxxxx      814.81 MB          13.04 MB

Función:

ALTER FUNCTION [dbo].[GetDBSize] 
(
    @db_name NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN

  SELECT 
        database_name = DB_NAME(database_id)
      , log_size_mb = CAST(SUM(CASE WHEN type_desc = ''LOG'' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
      , row_size_mb = CAST(SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
      , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
  FROM sys.master_files WITH(NOWAIT)
  WHERE database_id = DB_ID(@db_name)
      OR @db_name IS NULL
  GROUP BY database_id

ACTUALIZACIÓN 2016/01/22:

Mostrar información sobre el tamaño, el espacio libre, las últimas copias de seguridad de la base de datos

IF OBJECT_ID(''tempdb.dbo.#space'') IS NOT NULL
    DROP TABLE #space

CREATE TABLE #space (
      database_id INT PRIMARY KEY
    , data_used_size DECIMAL(18,2)
    , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT ''
    USE ['' + d.name + '']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''''SpaceUsed'''') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;''
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''')

EXEC sys.sp_executesql @SQL

SELECT
      d.database_id
    , d.name
    , d.state_desc
    , d.recovery_model_desc
    , t.total_size
    , t.data_size
    , s.data_used_size
    , t.log_size
    , s.log_used_size
    , bu.full_last_date
    , bu.full_size
    , bu.log_last_date
    , bu.log_size
FROM (
    SELECT
          database_id
        , log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
    FROM sys.master_files
    GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
    SELECT
          database_name
        , full_last_date = MAX(CASE WHEN [type] = ''D'' THEN backup_finish_date END)
        , full_size = MAX(CASE WHEN [type] = ''D'' THEN backup_size END)
        , log_last_date = MAX(CASE WHEN [type] = ''L'' THEN backup_finish_date END)
        , log_size = MAX(CASE WHEN [type] = ''L'' THEN backup_size END)
    FROM (
        SELECT
              s.database_name
            , s.[type]
            , s.backup_finish_date
            , backup_size =
                        CAST(CASE WHEN s.backup_size = s.compressed_backup_size
                                    THEN s.backup_size
                                    ELSE s.compressed_backup_size
                        END / 1048576.0 AS DECIMAL(18,2))
            , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
        FROM msdb.dbo.backupset s
        WHERE s.[type] IN (''D'', ''L'')
    ) f
    WHERE f.RowNum = 1
    GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC

Salida:

database_id name                             state_desc   recovery_model_desc total_size   data_size   data_used_size  log_size    log_used_size  full_last_date          full_size    log_last_date           log_size
----------- -------------------------------- ------------ ------------------- ------------ ----------- --------------- ----------- -------------- ----------------------- ------------ ----------------------- ---------
24                              ONLINE       SIMPLE              66339.88     65840.00    65102.06        499.88      5.05           NULL                    NULL         NULL                    NULL
11          AdventureWorks2012               ONLINE       SIMPLE              16404.13     15213.00    192.69          1191.13     15.55          2015-11-10 10:51:02.000 44.59        NULL                    NULL
10          locateme                         ONLINE       SIMPLE              1050.13      591.00      2.94            459.13      6.91           2015-11-06 15:08:34.000 17.25        NULL                    NULL
8           CL_Documents                     ONLINE       FULL                793.13       334.00      333.69          459.13      12.95          2015-11-06 15:08:31.000 309.22       2015-11-06 13:15:39.000 0.01
1           master                           ONLINE       SIMPLE              554.00       492.06      4.31            61.94       5.20           2015-11-06 15:08:12.000 0.65         NULL                    NULL
9           Refactoring                      ONLINE       SIMPLE              494.32       366.44      308.88          127.88      34.96          2016-01-05 18:59:10.000 37.53        NULL                    NULL
3           model                            ONLINE       SIMPLE              349.06       4.06        2.56            345.00      0.97           2015-11-06 15:08:12.000 0.45         NULL                    NULL
13          sql-format.com                   ONLINE       SIMPLE              216.81       181.38      149.00          35.44       3.06           2015-11-06 15:08:39.000 23.64        NULL                    NULL
23          users                            ONLINE       FULL                173.25       73.25       3.25            100.00      5.66           2015-11-23 13:15:45.000 0.72         NULL                    NULL
4           msdb                             ONLINE       SIMPLE              46.44        20.25       19.31           26.19       4.09           2015-11-06 15:08:12.000 2.96         NULL                    NULL
21          SSISDB                           ONLINE       FULL                45.06        40.00       4.06            5.06        4.84           2014-05-14 18:27:11.000 3.08         NULL                    NULL
27          tSQLt                            ONLINE       SIMPLE              9.00         5.00        3.06            4.00        0.75           NULL                    NULL         NULL                    NULL
2           tempdb                           ONLINE       SIMPLE              8.50         8.00        4.50            0.50        1.78           NULL                    NULL         NULL                    NULL

Answer 3

Simplemente puede ejecutar lo siguiente:

exec sp_spaceused

Le dará el espacio total utilizado para esa base de datos.

Answer 4

También compare los resultados con el resultado de la siguiente consulta

EXEC sp_helpdb @dbname= ''MSDB''

Produce un resultado similar al siguiente

Hay un buen artículo: diferentes formas de determinar el espacio libre para las bases de datos de SQL Server y los archivos de la base de datos

Answer 5

USTED verifica cómo funciona esta consulta en el siguiente enlace.

https://gallery.technet.microsoft.com/SIZE-OF-ALL-DATABASES-IN-0337f6d5/

IF OBJECT_ID(''tempdb..#spacetable'') IS NOT NULL 
DROP TABLE tempdb..#spacetable 
create table #spacetable
(
database_name varchar(50) ,
total_size_data int,
space_util_data int,
space_data_left int,
percent_fill_data float,
total_size_data_log int,
space_util_log int,
space_log_left int,
percent_fill_log char(50),
[total db size] int,
[total size used] int,
[total size left] int
)
insert into  #spacetable
EXECUTE master.sys.sp_MSforeachdb ''USE [?];
select x.[DATABASE NAME],x.[total size data],x.[space util],x.[total size data]-x.[space util] [space left data],
x.[percent fill],y.[total size log],y.[space util],
y.[total size log]-y.[space util] [space left log],y.[percent fill],
y.[total size log]+x.[total size data] ''''total db size''''
,x.[space util]+y.[space util] ''''total size used'''',
(y.[total size log]+x.[total size data])-(y.[space util]+x.[space util]) ''''total size left''''
 from (select DB_NAME() ''''DATABASE NAME'''',
sum(size*8/1024) ''''total size data'''',sum(FILEPROPERTY(name,''''SpaceUsed'''')*8/1024) ''''space util''''
,case when sum(size*8/1024)=0 then ''''divide by zero'''' else
substring(cast((sum(FILEPROPERTY(name,''''SpaceUsed''''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''''percent fill''''
from sys.master_files where database_id=DB_ID(DB_NAME())  and  type=0
group by type_desc  ) as x ,
(select 
sum(size*8/1024) ''''total size log'''',sum(FILEPROPERTY(name,''''SpaceUsed'''')*8/1024) ''''space util''''
,case when sum(size*8/1024)=0 then ''''divide by zero'''' else
substring(cast((sum(FILEPROPERTY(name,''''SpaceUsed''''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''''percent fill''''
from sys.master_files where database_id=DB_ID(DB_NAME())  and  type=1
group by type_desc  )y''
select * from #spacetable 
order by database_name
drop table #spacetable

Answer 6

SELECT      sys.databases.name AS [Database Name],  
        CONVERT(VARCHAR,SUM(size)*8/1024)+'' MB'' AS [Size]  
     FROM        sys.databases   
     JOIN        sys.master_files  
     ON          sys.databases.database_id=sys.master_files.database_id  
     GROUP BY    sys.databases.name  
     ORDER BY    sys.databases.name