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

Откуда:
Сообщений: 56
Есть 2 одинаковых тестовых базы с 2мя тестовыми таблицами.
отличие в том что на второй тестовой базе создана FILEGROUP под MEMORY_OPTIMIZED_DATA и создана одна таблица с директивой (MEMORY_OPTIMIZED=ON) Данные в эту таблицу даже не вставляются.
(полный код скрипта на создание приложен ниже)
Recovery model в обеих случаях FULL
Делаем full database backup
в тестовые таблицы записываются данные.
После команды BACKUP LOG вижу что log_reuse_wait_desc = Nothing
однако в dm_db_log_info вижу что в первой базе VLF файлы транкейтятся и vlf_active=0 , во второй где есть MEMORY_OPTIMIZED таблица куда данные даже не записываются эти VLF файлы начинают пожизненно оставаться vlf_active=1

вот пошаговое описание того что я делаю


select @@VERSION
--Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 17134: ) 



Создаем 2 тестовые базы и тестовую table_1

create database LogShrinkTest1



CREATE TABLE LogShrinkTest1.[dbo].[Table_1](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[val] [varchar](50) NULL,
	[val2] [varchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
)

CREATE NONCLUSTERED INDEX [ix_1] ON LogShrinkTest1.[dbo].[Table_1]([val2])



create database LogShrinkTest2

CREATE TABLE LogShrinkTest2.[dbo].[Table_1](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[val] [varchar](50) NULL,
	[val2] [varchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
)
GO
CREATE NONCLUSTERED INDEX [ix_1] ON LogShrinkTest2.[dbo].[Table_1]([val2])


Делаем full backup
BACKUP DATABASE LogShrinkTest1 TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\LogShrinkTest1_FULL.bak' WITH NOFORMAT, NOINIT,  NAME = N'LogShrinkTest1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
go
BACKUP DATABASE LogShrinkTest2 TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\LogShrinkTest2_FULL.bak' WITH NOFORMAT, NOINIT,  NAME = N'LogShrinkTest2-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10



для второй базы создаем MEMORY_OPTIMIZED таблицу (путь на диск впишите свой:)
ALTER DATABASE LogShrinkTest2 ADD FILEGROUP LogShrinkTest2_MO CONTAINS MEMORY_OPTIMIZED_DATA   
	go
ALTER DATABASE LogShrinkTest2 ADD FILE (name='LogShrinkTest2_mo_data', filename='E:\SQL SERV Backups\LogShrinkTest2_mo_data') TO FILEGROUP LogShrinkTest2_MO  



CREATE TABLE LogShrinkTest2.dbo.Table_2 (   
    id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,  
    UserId INT  , 
    CreatedDate DATETIME2  NULL,   
    TotalPrice MONEY  
    ) WITH (MEMORY_OPTIMIZED=ON)    



Заполняем тестовые table_1 чтобы увеличился лог файл

insert into LogShrinkTest1.dbo.table_1 default values
go 100000

update LogShrinkTest1.dbo.table_1 set val='xxx1' , val2 ='zzz1' where val is null


insert into LogShrinkTest2.dbo.table_1 default values
go 100000

update LogShrinkTest2.dbo.table_1 set val='xxx1' , val2 ='zzz1' where val is null


Видим что для того чтобы начали освобождаться VLF т.к у нас full recovery , нам нужно сделать бекап лога
select recovery_model_desc
	      ,log_reuse_wait_desc 
		  ,log_reuse_wait
		  ,* 
from master.sys.databases
where name in ( 'LogShrinkTest1','LogShrinkTest2')


ворнул для обеих баз log_reuse_wait_desc = LOG_BACKUP

перед этим смотрим что нам возвращает
declare @curDb int=db_id()
	select * ,sum(vlf_size_mb) over ()
	from sys.dm_db_log_info ( @curDb ) 

видим что у нас создались vlf файлы и большинство из vlf_active=1. Пока все на обеих базах одинакого.

Делаем бекап лога
BACKUP LOG LogShrinkTest1 TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\log_for_LogShrinkTest1.bak' WITH NOFORMAT, NOINIT,  NAME = N'LogShrinkTest1-Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

go

BACKUP LOG LogShrinkTest2 TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\log_for_LogShrinkTest2.bak' WITH NOFORMAT, NOINIT,  NAME = N'LogShrinkTest2-Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10


И далее смотрим что у нас получилось в
declare @curDb int=db_id()
select * ,sum(vlf_size_mb) over ()
from sys.dm_db_log_info ( @curDb ) 


И в случае первой базы мы видим что почти все VLF файлы почищены, можно ужимать лог файл (если требуется)
а вот в случае второй бызы VLF файлы как были активны так и были, хотя якобы log_reuse_wait_desc = nothing.
прилагаю 2 скриншота.
(ps на всякий случай, уточню что с базами делается только то что выше описано кодом, DBCC OPENTRAN ествественно возвращает No active open transactions. в обоих случаях)

т.е лог на второй базе будет расти и расти пока не выжрет все место((
я пока мало знаком с MEMORY_OPTIMIZED структурами, что я не учитываю, как мне освободить лог ?
18 апр 19, 11:09    [21865858]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
vadimman
Member

Откуда:
Сообщений: 56
так и не понял как приложить jpg файл... choose file я делаю а дальше никакой реакции(

К сообщению приложен файл. Размер - 148Kb
18 апр 19, 11:12    [21865864]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
Владислав Колосов
Member

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

почему Вас это беспокоит?
18 апр 19, 11:19    [21865875]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
vadimman
Member

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

ды как, я ж написал, что VLF не освобождается, лог файл не может повторно использоваться и растет до опупения, на самом деле эта ситуация с которой столкнулись на реальной продакшн базе, просто тут я чтобы голову людям не забивать сделал 2 тестовых упрощенных базы... сейчас чтобы сжать такую базу ее приходится переводить в симпл рекавери , делать чекпоинт и потом обратно в фул рекавери но это изврат :) хочу разобраться в проблеме
18 апр 19, 12:14    [21865935]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4738
vadimman,

А почему вы решили, что виновником этого является IN-MEMORY?
18 апр 19, 12:57    [21865992]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
vadimman
Member

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

ну если посмотрите код выше, это единственное отличие )
предположил я об этом случайно, издевался над тестовой базой у которой не шринкался лог, и так и сяк... потом подумал ,а что если деатачнуть ее, удалить лог и заново прикрепить в восстановлением лога:) Но уперся в то что в базе уже была memory optimized file group , подумал что это может как-то влиять. Сделал для абсолютной чистоты 2 тестовые базы и похоже да, как-то оно влияет на освобождение виртуальных файлов лога, причем именно только в full recovery mode...
18 апр 19, 13:02    [21866001]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31364
vadimman,

Пишут про какие то ошибки с In-Memory в 2017 RTM, накатите какой нибудь CU поновее
18 апр 19, 14:11    [21866152]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31364
alexeyvg
vadimman,

Пишут про какие то ошибки с In-Memory в 2017 RTM, накатите какой нибудь CU поновее
Например, https://dba.stackexchange.com/questions/216982/sql-server-2017-log-file-growth-with-memory-optimized-table
18 апр 19, 14:12    [21866153]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
vadimman,

сильно подозреваю что нужен фулл после добавления файловой группы
18 апр 19, 14:12    [21866154]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
а не, дело в CHECKPOINT для memory optimized.
18 апр 19, 14:27    [21866169]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
так и пишут

автор
For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint. This 1.5 GB size includes transaction log records for both disk-based and memory-optimized tables.

после этого лог попустит.
18 апр 19, 14:28    [21866171]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
vadimman
Member

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

а для memory optimized свой чекпоинт? на обычный в режиме full recovery он не реагирует
18 апр 19, 14:36    [21866189]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
vadimman
TaPaK,

а для memory optimized свой чекпоинт? на обычный в режиме full recovery он не реагирует

в смысле не реагирует? руками CHECKPOIN или как написано 1,5gb

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/checkpoint-operation-for-memory-optimized-tables?view=sql-server-2017
18 апр 19, 14:37    [21866193]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
vadimman
Member

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

интересно, спасибо! посмотрю и в эту сторону)
18 апр 19, 14:40    [21866200]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
vadimman
alexeyvg,

интересно, спасибо! посмотрю и в эту сторону)

в общем по вашим же скриптам чек и бекап всё освободил
18 апр 19, 14:43    [21866206]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
Владислав Колосов
Member

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

не думаю, что проблема имеет такие масштабы, какие Вы описали. Для кого-то и 1.5 Гб "большой лог". Журнал растет из-за отсутствия резервного копирования и из-за незавершенных транзакций.
18 апр 19, 14:57    [21866247]     Ответить | Цитировать Сообщить модератору
 Re: Сжатие log файла при наличии MEMORY_OPTIMIZED таблиц  [new]
vadimman
Member

Откуда:
Сообщений: 56
накатывание последних обновлений не помогло... причем такое же поведение замечено если объявлен пользовательский table type c memory_optimized и в какой-нибудь процедуре использована такая табличная переменная...
19 апр 19, 08:45    [21866778]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить