Есть 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
вот пошаговое описание того что я делаю
1.
2.
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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
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
1.
2.
3.
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 таблицу (путь на диск впишите свой:)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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 чтобы увеличился лог файл
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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 , нам нужно сделать бекап лога
1.
2.
3.
4.
5.
6.
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
перед этим смотрим что нам возвращает
1.
2.
3.
declare @curDb int=db_id()
select * ,sum(vlf_size_mb) over ()
from sys.dm_db_log_info ( @curDb )
видим что у нас создались vlf файлы и большинство из vlf_active=1. Пока все на обеих базах одинакого.
Делаем бекап лога
1.
2.
3.
4.
5.
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
И далее смотрим что у нас получилось в
1.
2.
3.
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 структурами, что я не учитываю, как мне освободить лог ?
