Километры логов и восстановление баз данных на MS SQL

[archimed7592]

Если хочется без нервотрепки, то должен быть на готове сервер с необходимыми ресурсами (место на диске, память, процессоры). Если есть такой сервер, то достаточно включить log shipping и не нужно будет никакие километры логов восстанавливать, да и время на восстановление уйдёт гораздо меньше.

[minamoto]

log shipping уже не очень актуален, лучше уж AlwaysOn группу сделать тогда.

[windcatcher]

AlwaysOn — это кончено замечательно. Но к сожалению не каждая организация может себе позволить иметь резервный сервер, и тем более DBA в штате. Также AlwaysOn не отменяет бэкапы.

[gep2005]

Поскольку предполагаю, что здесь в комментариях соберется большое количество знатоков SQL Server хочу спросить: А влияет ли на быстродействие базы модель восстановления (судя по названию нет :) )?

[AlanDenton]

Влияет. Иногда очень существенно. Почитайте про BULK INSERT и команды с минимальным протоколированием.

[windcatcher]

Не могу согласиться. Вопрос все-таки был немного другой. Основные модели восстановления это Полная и Простая. И с точки зрения производительности БД один одинаковы, т.к. в обоих случаях ведется журнал транзакций, только при Простой модели он усекается автоматически. Что касается модель восстановления с неполным протоколированием (Bulk Logged), то эта модель предназначена для временной замены Полной модели на период проведения массовых операций, например, массовой вставки данных или перестроением индексов.

[AlanDenton]

Разница все таки есть. Я не говорил, что она проявляется везде и всегда. Но она есть! Гипотетический пример. Удаление большого числа данных из таблицы:

SET NOCOUNT ON;

DECLARE @r INT = 1

WHILE @r > 0 BEGIN

    BEGIN TRANSACTION

        DELETE TOP(10000) dbo.tbl
        --WHERE ...

    COMMIT TRANSACTION

    SET @r = @@ROWCOUNT

    CHECKPOINT -- SIMPLE
    BACKUP LOG -- FULL

END

В случае SIMPLE модели восстановления, чтобы лог не рос нужно делать CHECKPOINT, для FULL спасает только бекап лога, который априори медленнее работает, чем CHECKPOINT. Хотя опять же с оговорками… Например, можно сделать бекап лога быстрее:

BACKUP LOG db TO DISK 'nul'

но ни к чему хорошему это не приведет, если мы говорим про продакшен.

[gep2005]

Т.е. получается Full даже медленнее в отдельных случаях?

[AlanDenton]

FULL модель восстановления в некоторых случаях более интенсивно использует лог файл. Полноценный пример приведу в комментарии ниже минут через 15.

[gep2005]

Спасибо. Всегда было интересно. Просто в моих задачах с точки зрения восстановления простая модель вполне устраивает, но переживал, за быстродействие.
Но рекомендация разносить базу и журнал на разные диски все равно актуальна так? И автоматическое усечение не влияет на производительность?

[AlanDenton]

SIMPLE:

SET NOCOUNT ON;

USE [master]
GO

IF DB_ID('shrink') IS NOT NULL BEGIN
    ALTER DATABASE [shrink] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [shrink]
END
GO

CREATE DATABASE [shrink]
ON
    PRIMARY (NAME = shrink_data, FILENAME = N'D:\shrink_data.mdf', SIZE = 25MB, MAXSIZE = 200MB, FILEGROWTH = 10%) 
    LOG ON (NAME = shrink_log, FILENAME = N'D:\shrink_log.ldf', SIZE = 3MB, MAXSIZE = 200MB, FILEGROWTH = 10%) 
GO 

ALTER DATABASE [shrink] SET RECOVERY SIMPLE

USE [shrink]
GO

CREATE TABLE dbo.tbl (
      c1 INT IDENTITY CONSTRAINT pk PRIMARY KEY NONCLUSTERED
    , c2 CHAR(3000) DEFAULT 'дефолтный бред'
)
GO

DECLARE @i INT = 1

WHILE @i <= 40000 BEGIN

    INSERT INTO dbo.tbl DEFAULT VALUES

    IF @i % 500 = 0 CHECKPOINT 

    SET @i += 1

END
GO 

DECLARE @i INT = 1

WHILE @i <= 20000 BEGIN

    DELETE FROM dbo.tbl WHERE c1 = @i 

    IF @i % 500 = 0 CHECKPOINT

    SET @i += 1

END
GO

CHECKPOINT
GO

SELECT name, size * 8192 / 1048576
FROM sysfiles 
GO 

DBCC SHRINKFILE(shrink_data, 40) WITH NO_INFOMSGS
GO

SELECT name, size * 8192 / 1048576
FROM sysfiles

До шринка файла данных:

---------------- -----------
shrink_data      169
shrink_log       3

После шринка:

name             
---------------- -----------
shrink_data      81
shrink_log       10

FULL:

SET NOCOUNT ON;

USE [master]
GO

IF DB_ID('shrink') IS NOT NULL BEGIN
    ALTER DATABASE [shrink] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [shrink]
END
GO

CREATE DATABASE [shrink]
ON
    PRIMARY (NAME = shrink_data, FILENAME = N'D:\shrink_data.mdf', SIZE = 25MB, MAXSIZE = 200MB, FILEGROWTH = 10%) 
    LOG ON (NAME = shrink_log, FILENAME = N'D:\shrink_log.ldf', SIZE = 3MB, MAXSIZE = 200MB, FILEGROWTH = 10%) 
GO 

ALTER DATABASE [shrink] SET RECOVERY FULL
BACKUP DATABASE [shrink] TO DISK = 'NUL' 

USE [shrink]
GO

CREATE TABLE dbo.tbl (
      c1 INT IDENTITY CONSTRAINT pk PRIMARY KEY NONCLUSTERED
    , c2 CHAR(3000) DEFAULT 'дефолтный бред'
)
GO

DECLARE @i INT = 1

WHILE @i <= 40000 BEGIN

    INSERT INTO dbo.tbl DEFAULT VALUES

    IF @i % 500 = 0
        BACKUP LOG [shrink] TO DISK = 'NUL' 

    SET @i += 1

END
GO 

DECLARE @i INT = 1

WHILE @i <= 20000 BEGIN

    DELETE FROM dbo.tbl WHERE c1 = @i 

    IF @i % 500 = 0
        BACKUP LOG [shrink] TO DISK = 'NUL' 

    SET @i += 1

END
GO

BACKUP LOG [shrink] TO DISK = 'NUL'
GO

SELECT name, size * 8192 / 1048576
FROM sysfiles 
GO 

DBCC SHRINKFILE(shrink_data, 40) WITH NO_INFOMSGS
GO

SELECT name, size * 8192 / 1048576
FROM sysfiles

До шринка:

-------------- -----------
shrink_data    169
shrink_log     3

После:

-------------- -----------
shrink_data    81
shrink_log     146

Итого: При FULL модели восстановления видим, что лог используется более интенсивно (10 метров против 146).

Но рекомендация разносить базу и журнал на разные диски все равно актуальна так?

Мало дисков не бывает. Если есть возможность, то разносите и радуйтесь жизни :)

И автоматическое усечение не влияет на производительность?

Очень сильно влияет. Увеличивает фрагментацию файлов базы, увеличивает фрагментацию индексов, много ресурсов тратиться на последующий AutoGrow. Почитайте при случае Auto Shrink Events.

[windcatcher]

Да, актуальна.
Усечение не влияет на производительность. По крайней мере так, чтобы это заметили пользователи.

[windcatcher]

Увидел пост коллеги сверху. Поэтому пояснение — выше я писал про автоматическое усечении лога БД в Простой модели восстановления.

[gep2005]

Да. Спасибо. Я тоже про Простую модель говорил. Про настройку Автоусечения в параметрах базы знаю и так не делаю. В целом у меня все же складывается мнение, что в моих случаях вернее использовать простую модель, т.к. ни разу не возникало потребности восстановить базу на конкретный момент времени. (только возврат к конкретному бэкапу). Огромное спасибо за разъяснение обоим.

[windcatcher]

Восстановление на конкретный момент времени, это скорее доп. фишка, но тоже весомая. Тут главное восстановление на максимально актуальный момент времени. И тут что бы понять Простая или Полная модель вам нужна задайте главный вопрос: за какой период мы можем себе позволить потерять данные? За 15 минут, за 2 часа, за 1 день?
Если толкового ответа получить не у кого, то попытайтесь просчитать последствия сами. Сколько народу вводят данные одновременно? реально ли будет ввести данные повторно, например за день или только с обеда? есть ли другие системы с которыми обменивается ваша БД и будут ли работать обмен, если вы откатите БД? и т.п.

[AlanDenton]

И автоматическое усечение не влияет на производительность?

Усечение не влияет на производительность.

Влияет… опять же не всегда и везде! Поэтому может не стоит быть таким категоричным. Вашу статью будут читать в дальнейшем и такие комментарии начинающими могут быть восприняты буквально.

[gep2005]

Да. Вы правы. Есть некоторая двусмысленность. Насколько я понял — не влияет автоусечение которое происходит само собой при модели восстановления "Простая". Но устанавливать такой параметр в настройках файлов базы и транзакционного лога не стоит.

[AlanDenton]

Еще раз повторюсь. Необоснованный SHRINK на производительность влияет всегда… При любой модели восстановления.

«Если Вы включаете AUTO_SHRINK на продакшене, то где-то в страшных муках умирает котенок...» об этом тоже нужно помнить :)