Определить размер базы данных SQL Server
Выпуск SQL Server 2005/2008 Express имеет ограничение в 4 ГБ на базу данных. Насколько мне известно, ядро базы данных учитывает только данные, исключая файлы журналов, неиспользуемое пространство и размер индекса.
Получение длины файла MDF не должно давать правильный размер базы данных с точки зрения ограничений SQL Server. У меня вопрос, как получить размер базы данных?
Ответы 8
sp_spaceused
В SQL Management Studio щелкните правой кнопкой мыши базу данных и выберите «Свойства» в контекстном меню. Посмотрите на цифру «Размер».
Согласно справке SQL2000, sp_spaceused включает данные и индексы.
Этот сценарий должен делать:
CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18),
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
EXEC sp_msforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'''
-- SELECT * FROM #t ORDER BY name
-- SELECT name, CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3)) FROM #t ORDER BY name
SELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) FROM #t
DROP TABLE #t
Это ОТЛИЧНЫЙ ответ. Спасибо за сценарий - очень полезно!
sp_helpdb
нет необходимости в цикле, в отличие от sp_spaceused.
Выдает выделенный размер БД (размер файла mdf), а не фактический используемый размер.
Лучшее решение - это, возможно, рассчитать размер каждого файла базы данных, используя представление sys.sysfiles, учитывая размер каждой страницы 8 КБ, следующим образом:
USE [myDatabase]
GO
SELECT
[size] * 8
, [filename]
FROM sysfiles
Столбец [поле] представляет размер файла в страницах (Ссылка MSDN на sysfiles).
Вы увидите, что будет как минимум два файла (MDF и LDF): сумма этих файлов даст вам правильный размер всей базы данных ...
+1 SELECT SUM(size)/128.0 AS size FROM sysfiles возвращает то же значение, что и sp_spaceused, но может быть проще анализировать автоматически.
Вы могли бы использовать и этот старомодный ...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
DECLARE @iCount int, @iMax int, @DatabaseName varchar(200), @SQL varchar (8000)
Select NAME, DBID, crdate, filename, version
INTO #TEMP
from MAster..SYSDatabASES
SELECT @iCount = Count(DBID) FROM #TEMP
Select @SQL='Create Table ##iFile1 ( DBName varchar( 200) NULL, Fileid INT, FileGroup int, TotalExtents INT , USedExtents INT ,
Name varchar(100), vFile varchar (300), AllocatedSpace int NUll, UsedSpace int Null, PercentageFree int Null ) '+ char(10)
exec (@SQL)
Create Table ##iTotals ( ServerName varchar(100), DBName varchar( 200) NULL, FileType varchar(10),Fileid INT, FileGroup int, TotalExtents INT , USedExtents INT ,
Name varchar(100), vFile varchar (300), AllocatedSpace int NUll, UsedSpace int Null, PercentageFree int Null )
WHILE @iCount>0
BEGIN
SELECT @iMax =Max(dbid) FROM #TEMP
Select @DatabaseName = Name FROM #TEMP where dbid =@iMax
SELECT @SQL = 'INSERT INTO ##iFile1(Fileid , FileGroup , TotalExtents , USedExtents , Name , vFile)
EXEC (''USE [' + @DatabaseName + '] DBCC showfilestats'') ' + char(10)
Print (@SQL)
EXEC (@SQL)
SELECT @SQL = 'UPDATE ##iFile1 SET DBName ='''+ @DatabaseName +''' WHERE DBName IS NULL'
EXEC (@SQL)
DELETE FROM #TEMP WHERE dbid =@iMax
Select @iCount =@iCount -1
END
UPDATE ##iFile1
SET AllocatedSpace = (TotalExtents * 64.0 / 1024.0 ), UsedSpace =(USedExtents * 64.0 / 1024.0 )
UPDATE ##iFile1
SET PercentageFree = 100-Convert(float,UsedSpace)/Convert(float,AllocatedSpace )* 100
WHERE USEDSPACE>0
CREATE TABLE #logspace (
DBName varchar( 100),
LogSize float,
PrcntUsed float,
status int
)
INSERT INTO #logspace
EXEC ('DBCC sqlperf( logspace)')
INSERT INTO ##iTotals(ServerName, DBName, FileType,Name, vFile,PercentageFree,AllocatedSpace)
select @@ServerName ,DBNAME, 'Data' as FileType,Name, vFile, PercentageFree , AllocatedSpace
from ##iFile1
UNION
select @@ServerName ,DBNAME, 'Log' as FileType ,DBName,'' as vFile ,PrcntUsed , LogSize
from #logspace
Select * from ##iTotals
select ServerName ,DBNAME, FileType, Sum( AllocatedSpace) as AllocatedSpaceMB
from ##iTotals
Group By ServerName ,DBNAME, FileType
Order By ServerName ,DBNAME, FileType
select ServerName ,DBNAME, Sum( AllocatedSpace) as AllocatedSpaceMB
from ##iTotals
Group By ServerName ,DBNAME
Order By ServerName ,DBNAME
drop table ##iFile1
drop table #logspace
drop table #TEMP
drop table ##iTotals
Мне всегда нравилось заниматься этим напрямую:
SELECT
DB_NAME( dbid ) AS DatabaseName,
CAST( ( SUM( size ) * 8 ) / ( 1024.0 * 1024.0 ) AS decimal( 10, 2 ) ) AS DbSizeGb
FROM
sys.sysaltfiles
GROUP BY
DB_NAME( dbid )
Имейте в виду, что в SQL 2005 + sysaltfiles не могут быть прочитаны базовой учетной записью пользователя (то есть учетной записью без специальных разрешений). Даже если вы добавите логин в основную базу данных и GRANT SELECT для master..sysaltfiles или sys.sysaltfiles, оператор select в этой таблице вернет пустой набор записей. Нет проблем с использованием учетной записи системного администратора.
Общий запрос для проверки размера базы данных в SQL Server, который поддерживает как Azure, так и локальную среду.
Метод 1. Использование системного представления «sys.database_files»
SELECT
DB_NAME() AS [database_name],
CONCAT(CAST(SUM(
CAST( (size * 8.0/1024) AS DECIMAL(15,2) )
) AS VARCHAR(20)),' MB') AS [database_size]
FROM sys.database_files;
Метод 2 - Использование системной хранимой процедуры sp_spaceused
EXEC sp_spaceused ;
sp_spaceused учитывает размер файла журнала, поэтому, если у вас действительно маленькая база данных и действительно большой файл журнала, результат sp_spaceused будет вводить в заблуждение при попытке определить, насколько вы близки к пределу в 4 ГБ.