Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как определить максимальный размер будущего полного бэкапа?  [new]
Молодой
Member

Откуда: Москва
Сообщений: 167
Перед созданием бэкапа хочется гарантировано иметь достаточно места на диске.
Будет создаваться полный бэкап без сжатия.

Для оценки текущего размера всех файлов данных использую запрос:

select SUM(size * 8.0 / 1024) as [Size, Mb]
from sys.master_files
WHERE database_id = DB_ID()
AND type_desc = 'ROWS'
GROUP BY database_id


Вопросы:

1) Верно ли моё предположение, что максимальный размер будущего бэкапа не превысит размером
сумму текущих размеров всех файлов данных?

2) Правильно ли я рассчитываю размер всех файлов данных?
6 фев 19, 16:19    [21802843]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36461
1. В полный бэкап входят только занятые страницы, а просто зарезервированные - нет.
2. В полный бэкап кроме данных еще входит бэкап лога за время снятия полного бэкапа.
6 фев 19, 16:25    [21802853]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36461
Да и не понятно, зачем это все.
Как вы, к примеру, предотвратите ситуацию, когда на начала бэкапа место еще нет, а в процессе кто-то его занял?
6 фев 19, 16:27    [21802856]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Молодой
Member

Откуда: Москва
Сообщений: 167
Проблема в том что совершенно неизвестно какого размера будет база на момент
создания бэкапа, сколько файлов данных в ней будет, и как интенсивно она будет использоваться, но:
1) Бэкап надо гарантировано создать
2) Пользователю, который это будет делать, надо дать подсказки на какой диск его бэкап ТОЧНО влезет, а куда нет.

Как вы, к примеру, предотвратите ситуацию, когда на начала бэкапа место еще нет, а в процессе кто-то его занял?
- Никак не смогу. Это оставляем на совести пользователя


1. В полный бэкап входят только занятые страницы, а просто зарезервированные - нет.
- Это означает что бэкап обычно чуть меньше суммы размеров файлов данных.

2. В полный бэкап кроме данных еще входит бэкап лога за время снятия полного бэкапа.
- А вот это уже хуже. Это означает что при интенсивном использовании БД во время бэкапа, рамер всё таки может
превысить сумму файлов данных.
6 фев 19, 16:40    [21802875]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36461
Ваша задача в общем случае слаборешаема. В полный бэкап база попадает не на момент его начала, а на момент его окончания.

Можно, конечно, переводить базу перед бэкапм в read_only...
6 фев 19, 16:53    [21802894]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Молодой
Member

Откуда: Москва
Сообщений: 167
ОК

1) Вводим допущение, что все sysamin, на время создания бэкапа - вменяемые люди.
2) Переводим БД в RESTRICTED_USER.
3) Оценка размеров файлов данных.
4) Бэкап.


По идее всё должно работать.

Спасибо за консультацию
6 фев 19, 17:03    [21802907]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29293
Молодой
2) Переводим БД в RESTRICTED_USER.
ИМХО ненужная перестраховка.
Конечно, теоретически может базу во время бакапа раздуть так, что не хватит места на диске, но неужели это произойдёт в реальной жизни??? Тем более, что обычно в файлах ещё и есть свободное место.

Я бы даже ещё меньше перестраховался, и считал бы, что для бакапов нужно не сумма размеров файлов, а сумма занятого места в файлах. Как там SSMS это определяет:
SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id
6 фев 19, 21:09    [21803089]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
tunknown
Member

Откуда:
Сообщений: 692
Молодой
1) Вводим допущение, что все sysamin, на время создания бэкапа - вменяемые люди.
Если делаете такие оптимистичные допущения, то введите ещё одно. Папка, куда кладётся бекап, имеет NTFS атрибут- "сжатый". И нужно места в 3-4 раза меньше. Иначе рискуете получить ситуацию, что места для бекапа недостаточно ни на одном доступном диске. Обработайте и ситуацию, когда бекап пытаются сделать на сетевую шару.
7 фев 19, 09:24    [21803216]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
AndrF
Member

Откуда:
Сообщений: 2094
tunknown
Молодой
1) Вводим допущение, что все sysamin, на время создания бэкапа - вменяемые люди.
Если делаете такие оптимистичные допущения, то введите ещё одно. Папка, куда кладётся бекап, имеет NTFS атрибут- "сжатый". И нужно места в 3-4 раза меньше. Иначе рискуете получить ситуацию, что места для бекапа недостаточно ни на одном доступном диске. Обработайте и ситуацию, когда бекап пытаются сделать на сетевую шару.


Вообще-то бэкап и так обычно выполняется со сжатием. Так что это примерно как жать архив еще одним архиватором...
7 фев 19, 10:55    [21803301]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Dzianis
Member

Откуда:
Сообщений: 71
[quot AndrF]
tunknown
пропущено...

Вообще-то бэкап и так обычно выполняется со сжатием. Так что это примерно как жать архив еще одним архиватором...


Автор не собирается сжимать бекап

Молодой
Будет создаваться полный бэкап без сжатия.
7 фев 19, 11:14    [21803326]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29293
Dzianis
Автор не собирается сжимать бекап

Молодой
Будет создаваться полный бэкап без сжатия.
Это, конечно, странно.
Он быстрее, и требует меньше места.
Другое дело, что оценить размер будет невозможно...
7 фев 19, 14:59    [21803571]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
AndrF
Member

Откуда:
Сообщений: 2094
alexeyvg
Это, конечно, странно.
Он быстрее, и требует меньше места.
Другое дело, что оценить размер будет невозможно...


Точно по любому не вычислить, а сжатый явно не будет больше не сжатого. Потому не заморачиваясь брать суммарный размер данных и лога - тогда точно поместится..
7 фев 19, 15:26    [21803604]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6612
Молодой,
приблизительно sp_spaceused -> Reserved
7 фев 19, 15:40    [21803613]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
L_argo
Member

Откуда:
Сообщений: 934
alexeyvg
Другое дело, что оценить размер будет невозможно...
Я не уверен конеш, но мне кажется, что своб. место все равно должно быть не меньше, чем размер базы. Даже в случае режима сжатия.
Во всяком случае, в процессе создания бекап имеет сначала несжатый размер, а потом становится сжатым.
7 фев 19, 15:41    [21803615]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6612
L_argo
alexeyvg
Другое дело, что оценить размер будет невозможно...
Я не уверен конеш, но мне кажется, что своб. место все равно должно быть не меньше, чем размер базы. Даже в случае режима сжатия.
Во всяком случае, в процессе создания бекап имеет сначала несжатый размер, а потом становится сжатым.

да повесится проще чем такие объёмы под диски для создания бекапа иметь
7 фев 19, 15:43    [21803617]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29293
AndrF
alexeyvg
Это, конечно, странно.
Он быстрее, и требует меньше места.
Другое дело, что оценить размер будет невозможно...


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

Не пойму, чем хуже получить ошибку "не удалось сделать бакап, потому что не хватило места", чем ошибку "не дам сделать бакап, а вдруг не хватит места"?
Второе ИМХО намного хуже, по крайней мере, для меня, если бы я был тем админом.

На последней работе у меня бы вообще никогда не получилось бы сделать бакап, с таким подходом.

Я бы лучше показывал все доступные диски, но диски, на которых свободного места меньше, чем Reserved, отмечал бы красненьким.
7 фев 19, 19:09    [21803812]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2300
L_argo
alexeyvg
Другое дело, что оценить размер будет невозможно...
Я не уверен конеш, но мне кажется, что своб. место все равно должно быть не меньше, чем размер базы. Даже в случае режима сжатия.
Во всяком случае, в процессе создания бекап имеет сначала несжатый размер, а потом становится сжатым.
Сам становиться сжатым или кто помогает?
7 фев 19, 20:01    [21803832]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2300
alexeyvg
Молодой
2) Переводим БД в RESTRICTED_USER.
ИМХО ненужная перестраховка.
Конечно, теоретически может базу во время бакапа раздуть так, что не хватит места на диске, но неужели это произойдёт в реальной жизни??? Тем более, что обычно в файлах ещё и есть свободное место.

Я бы даже ещё меньше перестраховался, и считал бы, что для бакапов нужно не сумма размеров файлов, а сумма занятого места в файлах. Как там SSMS это определяет:
SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id
У меня так получилось даже меньше чем через
FILEPROPERTY(df.name, 'SpaceUsed')
7 фев 19, 20:04    [21803835]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
L_argo
Member

Откуда:
Сообщений: 934
Mind
Сам становиться сжатым или кто помогает?
В чем собственно сарказм ? Понаблюдайте за размером файла. Он возможно фальшивый, но ОС скорее всего считает именно его. Т.е. резервируется все равно большой файл.
Т.е. для предотвращения нехватки места бесполезно знать, насколько бекап сжимается, ИМХО.
7 фев 19, 22:46    [21803898]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6612
L_argo
Mind
Сам становиться сжатым или кто помогает?
В чем собственно сарказм ? Понаблюдайте за размером файла. Он возможно фальшивый, но ОС скорее всего считает именно его. Т.е. резервируется все равно большой файл.
Т.е. для предотвращения нехватки места бесполезно знать, насколько бекап сжимается, ИМХО.

Это ваши фантазии, у нас бы не сложилось ни единого бекапа на базах в 2-5тб
7 фев 19, 22:49    [21803900]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Col
Member

Откуда: Торонто
Сообщений: 177
Молодой
sp_spaceused показывает примерный размер нежатого бакапа
Линк на первоисточник где собственно именно это и написано:
https://support.microsoft.com/en-us/help/2001026/inf-space-requirements-for-backup-devices-in-sql-server

По поводу Вашей задачи, вот скрипт ее решаюший:

   USE [master]
   GO
   DECLARE @estimatedBackSizeMB INT
   DECLARE @estimatedDriveFreeSpaceMB INT
   DECLARE @dbCheckMessage varchar(80)
   DECLARE @enoughSpaceForBackupFlag INT
   DECLARE @drvLetter VARCHAR (5)
   SET NOCOUNT ON
   set @drvLetter = 'C'
   SET @dbCheckMessage = concat ('Checking database ', DB_NAME ())
   PRINT @dbCheckMessage

   SELECT @estimatedBackSizeMB = round (sum (a.total_pages) * 8192 / SQUARE (1024.0), 0)
   FROM sys.partitions p
   JOIN sys.allocation_units a
      ON p.partition_id = a.container_id
   LEFT JOIN sys.internal_tables it
      ON p.object_id = it.object_id

   CREATE TABLE #freespace (drive VARCHAR (5), MBFree DECIMAL (8, 2))

   INSERT INTO #freespace (
      Drive,
      MBFree) EXEC xp_fixeddrives

   SELECT @estimatedDriveFreeSpaceMB = MBFree 
   FROM #freespace
   WHERE drive = @drvLetter

   IF @estimatedBackSizeMB * 1.15 < @estimatedDriveFreeSpaceMB
      SET @enoughSpaceForBackupFlag = 1
   ELSE
      SET @enoughSpaceForBackupFlag = 0
/*
   SELECT DatabaseName = db_name(),	
      Estimated_Back_Size_MB = @estimatedBackSizeMB,
      Estimated_Drive_Free_Space_MB = @estimatedDriveFreeSpaceMB,
      EnoughSpaceForBackupFlag = @enoughSpaceForBackupFlag
*/
   DROP TABLE #freespace

   IF @enoughSpaceForBackupFlag = 1
   BEGIN
   PRINT 'Continue to Backup...'
   DECLARE @path NVARCHAR(256); -- Path for backup files
   DECLARE @fileName NVARCHAR(256); -- Filename for backup
   DECLARE @fileDate NVARCHAR(20); -- Used for file name
   DECLARE @name NVARCHAR(50); -- Database name

   -- Build the path and file name.
    SET @path = 'C:\Temp\'
	SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    SET @name = DB_NAME ()
    SET @fileName = @path + @name  + '_' + @fileDate + '.BAK'
	  -- Backup the database.
    BACKUP DATABASE @name TO DISK = @fileName WITH  COPY_ONLY, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
      -- Loop to the next database.
    END
ELSE 
   PRINT 'Drive Space Problem...'



   SET NOCOUNT OFF
7 фев 19, 23:39    [21803918]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Col
Member

Откуда: Торонто
Сообщений: 177
[quot alexeyvg]
AndrF
пропущено...
Не пойму, чем хуже получить ошибку "не удалось сделать бакап, потому что не хватило места", чем ошибку "не дам сделать бакап, а вдруг не хватит места"?
Второе ИМХО намного хуже, по крайней мере, для меня, если бы я был тем админом.

А может не надо ошибку получать совсем?
Можно ведь забакапиться в другое место, или подчистить место для бакапа, конструкция IF...ELSE позволяет и первое и второе, а то и первое после второго ;)
7 фев 19, 23:52    [21803920]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29293
Col
alexeyvg
Не пойму, чем хуже получить ошибку "не удалось сделать бакап, потому что не хватило места", чем ошибку "не дам сделать бакап, а вдруг не хватит места"?
Второе ИМХО намного хуже, по крайней мере, для меня, если бы я был тем админом.

А может не надо ошибку получать совсем?
Можно ведь забакапиться в другое место, или подчистить место для бакапа, конструкция IF...ELSE позволяет и первое и второе, а то и первое после второго ;)
Да, круто, хочу сделать бакап в NAS, а оно тихо делает бакап на диск C на том же сервере, потому что программист очень умный, он утром прочитал про IF...ELSE, это его первая работа, и он ещё не получал люлей за то, что все транзакции банка за год пропали :-)

Не, не надо нам такого сервиса, с IF...ELSE!
8 фев 19, 00:25    [21803934]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2300
L_argo
Mind
Сам становиться сжатым или кто помогает?
В чем собственно сарказм ? Понаблюдайте
Т.е. для предотвращения нехватки места бесполезно знать, насколько бекап сжимается, ИМХО.
Вам занятся нечем, вы и проверяйте ваши фантазии, ну или хотя бы включите мозг. Бэкап со сжатем выполняется быстрее чем без сжатия. Как вы думаете возможно ли такое если сервер будет сначала делать полный несжатый бэкап, а потом уже на диске его пережимать?
8 фев 19, 04:06    [21803964]     Ответить | Цитировать Сообщить модератору
 Re: Как определить максимальный размер будущего полного бэкапа?  [new]
Yasha123
Member

Откуда:
Сообщений: 1568
у него еще и базы в совместимости 2005 не жмутся.
все да, а эти нет, так что нет пределов фантазии:
L_argo
По своим наблюдениям скажу, что база тоже должна быть не ниже 2008.

Стоял склсервер 2008R2 этерпрайз.
Там была база с 2005 совместимостью. Она при бекапе не упаковывалась.
Когда я ей поставил совместимость 2008, то она начала паковаться.
Чисто эмпирическое наблюдение.

21786947
8 фев 19, 20:08    [21804870]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить