Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
squidw
Member

Откуда:
Сообщений: 25
Каким образом можно собрать информацию о росте файлов БД? Рост mdf, ldf файлов либо bak за каждый месяц для отслеживания динамики роста?
Насколько я понимаю SQL не хранит такой информации, нежен скрипт для таких целей, который необходимо через job кидать или все же есть штатное средство какое-нибудь? Как эту информацию вывести в дальнейшем куда-нибудь за пределы SQL сервера, например, текстовый файл + отправка в почту или возможно даже на zabbix как-то отправить?
Находил на просторах https://www.sql.ru/forum/688383/dinamika-rosta-bazy-dannyh-kak-otsledit-sprognozirovat но тут вопрос, а есть ли более приспособленный вариант под текущий скрипт:
SELECT backup_size, backup_start_date
FROM msdb.dbo.backupset
WHERE database_name='MyDbName' and type='D'
ORDER BY backup_start_date

Здесь проблема нужно знать какие БД есть чтобы в скрипте указать руками вместо MyDbName. Есть ли возможность использовать какую-то системную команду SQL, которая обнаружит все БД в сервере и подсчитает их текущий объем либо историю по объему? Еще одна проблема на что ориентируется SQL при выполнении данного скрипта? У меня например стоит очистка бэкапов и очистка истории в плане обслуживания для каждой БД.
14 янв 19, 12:07    [21784604]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
AndrF
Member

Откуда:
Сообщений: 2194
Можно типа так:

-- Общий размер баз данных
SELECT База = D.name,
SUM(CASE F.type
WHEN 0 THEN CAST(F.[size] AS float) * 8 /1024 / 1024
ELSE 0
END) AS [Размер базы в GB],
SUM(CASE F.type
WHEN 1 THEN CAST(F.[size] AS float) * 8 /1024 / 1024
ELSE 0
END) AS [Размер лога в GB]
FROM sys.databases D
INNER JOIN sys.master_files F ON D.database_id =f.database_id
WHERE D.database_id>4
AND D.name LIKE '%'
GROUP BY D.name
ORDER BY 1, 2
14 янв 19, 12:21    [21784617]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
http://www.sqlnuggets.com/blog/extended-event-track-data-log-file-size-changes/
14 янв 19, 12:24    [21784619]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
squidw,

можно настроить сервис "сбор информации" на SQL2012+.
14 янв 19, 12:35    [21784628]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
komrad
Member

Откуда:
Сообщений: 5244
squidw
Еще одна проблема на что ориентируется SQL при выполнении данного скрипта? У меня например стоит очистка бэкапов и очистка истории в плане обслуживания для каждой БД.


попробуйте этот скрипт

+

select 
		bs.database_name [Database]
		, bs.recovery_model [Recovery]
		, convert(datetime,convert(varchar(20),bs.backup_start_date,106)) [BackupDate]
		, convert(numeric(10,2),sum(bf.file_size/1024/1024)) [DBSizeMB]
		, (select convert(numeric(20,2),sum(bf2.file_size)/1024/1024) from msdb..backupfile bf2 where bf2.backup_set_id=bs.backup_set_id and bf2.file_type='D') [DataFilesMB]
		, (select convert(numeric(20,2),sum(bf2.file_size)/1024/1024) from msdb..backupfile bf2 where bf2.backup_set_id=bs.backup_set_id and bf2.file_type='L') [LogFilesMB]
		, convert(numeric(20,2),bs.backup_size/1024/1024) [BackupSizeMB]
		, convert(numeric(20,2),bs.compressed_backup_size/1024/1024) [BackupCompressedMB]
from  msdb.dbo.backupset bs
	join msdb.dbo.backupfile bf on bs.backup_set_id=bf.backup_set_id
where 
 --bs.database_name = '[dbname]' and 
  bs.type='D'
group by
	bs.backup_start_date
	,bs.server_name
	,bs.backup_set_id
	,bs.database_name
	,bs.recovery_model
	,bs.backup_size
	,bs.compressed_backup_size
order by 
	bs.backup_set_id asc



информация берется из истории бекапов
чем ее больше, тем больше у вас данных о базе
14 янв 19, 14:24    [21784752]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
squidw
Member

Откуда:
Сообщений: 25
komrad,
автор
информация берется из истории бекапов
чем ее больше, тем больше у вас данных о базе

То есть лучше увеличить или вообще убрать из планов обслуживания "очистка истории" для наиболее полной информации по данному скрипту?
14 янв 19, 16:13    [21784890]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
squidw
Member

Откуда:
Сообщений: 25
Владислав Колосов,

автор
можно настроить сервис "сбор информации" на SQL2012+.

Речь идет о MS SQL Server Analysis Services?
14 янв 19, 16:16    [21784893]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
squidw
Member

Откуда:
Сообщений: 25
invm,
автор
http://www.sqlnuggets.com/blog/extended-event-track-data-log-file-size-changes/


по данному примеру запрос выполняется, но вроде как ожидается файл в директории C:\ExtendedEvent\DBSizeTracking.xel чего не происходит на выходе, при изменении на C:\ExtendedEvent\DBSizeTracking.txt та же ситуация. Кроме того при повторном выполнении необходимо менять значение "DB Size Tracking" на скажем "DB Size Tracking1" иначе ругается что такое выражение уже существует:
Сообщение 25631, уровень 16, состояние 1, строка 2
Объект сеанс событий, "DB Size Tracking", уже существует. Задайте уникальное имя сеанса событий.

Не практично. Более реально применить то, что написали в 21784617 и 21784752.
14 янв 19, 16:29    [21784912]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
squidw
Не практично.
Вы бы хоть для начала почитали что такое Extended Events и как с ними работать...
14 янв 19, 16:47    [21784931]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
komrad
Member

Откуда:
Сообщений: 5244
squidw
komrad,
автор
информация берется из истории бекапов
чем ее больше, тем больше у вас данных о базе

То есть лучше увеличить или вообще убрать из планов обслуживания "очистка истории" для наиболее полной информации по данному скрипту?

можно держать необходимую глубину данных
для некоторых это 3-6 месяцев, для некоторых это год, а некоторые вообще не чистят историю )

можно настроить отдельный джоб с вызовом sp_delete_backuphistory, запускать раз в неделю и передавать ему
getdate()-180
14 янв 19, 16:58    [21784948]     Ответить | Цитировать Сообщить модератору
 Re: Как собирать информацию о динамике роста БД Microsoft SQL Server?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
squidw
Владислав Колосов,

автор
можно настроить сервис "сбор информации" на SQL2012+.

Речь идет о MS SQL Server Analysis Services?


В SSMS в обозревателе объектов Сбор данных. Там есть отчет о приросте базы, в том числе.
14 янв 19, 17:22    [21784971]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить