Определение размеров всех баз данных в MS SQL Server 2008

Быстрый ответ

Чтобы определить размер баз данных в SQL Server 2008, можно использовать следующий SQL-запрос:

SELECT 
    DB_NAME(database_id) AS DatabaseName, 
    CAST(SUM(size) * 8. / 1024 AS DECIMAL(10,2)) AS Size_MB 
FROM 
    sys.master_files 
GROUP BY 
    database_id 
ORDER BY 
    Size_MB DESC;

Выбранный запрос оценивает системные представления и рассчитывает общий размер файлов данных и журнала для каждой базы данных, сгруппированные в упорядоченный список.

Ускорение работы в крупных системах

В крупных системах с 500 и более базами данных важно сокращать время ответа. Повышение производительности запросов экономит время и упрощает управление БД по мере их роста.

Практический совет: запускайте запросы в период наименьшей нагрузки или используйте реплики для чтения, чтобы не влиять на активность транзакций.

Глубокий анализ

Необходимость определения размера

Контролирование размера базы данных необходимо для:

• разработки стратегии построения индексов;
• планирования сегментации и архивации данных;
• обоснования увеличения объема хранения на бюджетных встречах.

Подробнее об этом расскажет наш спикер на видео

Файлы MDF и LDF

Опытные администраторы следят за размерами файлов данных (MDF) и файлов журналов (LDF):

Файлы данных (MDF):

• Хранят данные и объекты (таблицы и представления);
• Их размер коррелирует с объемом хранимых данных.

Файлы журналов (LDF):

• Сохраняют журналы транзакций, необходимые для восстановления и отмены операций;
• Рост может говорить об увеличении числа транзакций или проблемах с бэкапами.

Совершенствование скрипта

Чтобы отображать размеры в гигабайтах, используйте:

CAST(SUM(size) * 8. / 1024 / 1024 AS DECIMAL(10,2)) AS Size_GB

Для детального анализа полезно разбить каждую базу данных отдельно.

Визуализация

Считайте каждую базу данных величиной тома на полке, а ее размер – это объем информации:

[📙](10 МБ)     [📘](200 МБ)     [📗](150 МБ)     [📕](300 МБ)
База А       База B        База C       База D

Запрос к SQL аналогичен обращению к библиотекарю (🧑‍🔬), который сообщит вам толщину каждого тома:

SELECT 
    db.name AS 'Database', 
    SUM(size * 8 / 1024) AS 'Size_MB'
FROM 
    sys.databases db 
    INNER JOIN sys.master_files mf ON db.database_id = mf.database_id
GROUP BY 
    db.name;

Точность и практичность

Правильный выбор типа данных

Для расчета размеров предпочтительно использовать DECIMAL или NUMERIC для достижения большей точности.

Настройка метрик

Представление размера в МБ и ГБ с учетом всех необходимых направлений анализа:

SELECT 
    db.name AS 'Database', 
    CAST(SUM(size) * 8. / 1024 AS DECIMAL(10,2)) AS 'Size_MB',
    CAST(SUM(size) * 8. / 1024 / 1024 AS DECIMAL(10,2)) AS 'Size_GB'
FROM 
    sys.databases db 
    INNER JOIN sys.master_files mf ON db.database_id = mf.database_id
GROUP BY 
    db.name;

Cортировка по размеру поможет определить самые объемные базы данных, требующие проверки.

Полезные материалы

1. sp_spaceused (Transact-SQL) – SQL Server — комплексная информация о размерах баз данных от Microsoft.
2. SQL Server Row Count for all Tables in a Database — информация о количестве таблиц и строк в вашей БД.
3. Managing Data Growth in SQL Server — советы по управлению увеличением данных и размерами баз данных в SQL Server.