Файл транзакций sql для чего нужен
Каждая база данных SQL Server имеет как минимум два файла, с ней ассоциирующихся: один файл данных, в котором непосредственно хранятся данные и как минимум один файл журнала транзакций. Журнал транзакций это основной компонент системы управления базами данных (СУБД). Все изменения в базе данных записываются в журнал транзакций. Используя эту информацию, СУБД может определить какая транзакция какие изменения внесла в данные SQL Server.
Оператор CREATE DATABASE используется для создания базы данных Microsoft SQL Server. Опция этой команды LOG ON используется для определения журнала транзакций создаваемой базы данных. Впервые созданные данные помещаются в файл данных, а запись изменений этих данных помещается в файле журнала транзакций.
Как только делаются изменения в базе, журнал транзакций растет. Поскольку большинство изменений вносимых в базу, журналируются, Вам нужно будет отслеживать размер журнала транзакций, потому что, если данные постоянно меняются, журнал соответственно вырастает.
Каждая контрольная точка Microsoft SQL Server гарантирует что все записи в журнале и все модифицированные страницы данных корректно записаны на диск. Файл журнала транзакций используется Microsoft SQL Server в процессе операции восстановления базы данных, чтобы зафиксировать завершенные транзакции и откатить незавершенные. Информация, записывающаяся в журнал транзакций, включает:
- Время начала каждой транзакции;
- Изменения внутри каждой транзакции и информацию для их отката (для этого используются снимки страниц данных до, и после транзакции);
- Информация о распределении памяти для страниц БД (выделении и изъятии экстента);
- Информация о завершении или откате каждой транзакции.
Эти данные Microsoft SQL Server использует в целях повышения целостности данных. Журнал транзакций используется при старте SQL Server, для того чтобы отменить сделанные изменения и установить состояние базы данных на момент, предшествующий началу изменений.
При запуске SQL Server для каждой БД начинается процесс регенерации (recovery). SQL Server определяет те транзакции, которые необходимо откатить. Это происходит в том случае, когда неизвестно все ли изменения из кэша записаны на диск. Поскольку при выполнении контрольной точки все изменения сбрасываются на диск, то с нее и стартует процесс регенерации, который производит фиксацию транзакций на диск. Все изменения на страницах, сделанные до контрольной точки, уже записаны на диск, поэтому нет смысла для сброса их на диск еще раз и изменения, выполненные до контрольной точки, не берутся к рассмотрению.
При необходимости отката транзакции SQL Server копирует снимки страниц данных до изменений, сделанных с момента запуска оператора BEGIN TRANSACTION.
Вы можете использовать журнал транзакций при восстановлении базы данных. В этом случае журналируется фиксация транзакций. В процессе фиксации транзакций SQL Server сохраняет все сделанные изменения в базе данных на диске.
Журнал транзакций полезен для устранения ошибок в базе данных, ошибок транзакций и позволяет обеспечить целостность данных.
Некоторые операции не всегда журналируются
Microsoft SQL Server не выполняет журналирование в тех случаях, когда могут возникнуть проблему с нехваткой дискового пространства при быстром увеличении журнала транзакций.
Для некоторых операций, таких как CREATE INDEX, Microsoft SQL Server не ведет протоколирование для каждой новой страницы. Вместо этого SQL Server записывает достаточно информации, чтобы определить, как CREATE INDEX отработал, и принять решение о том фиксировать изменения или сделать откат.
Если опция базы данных select into/bulkcopy установлены в TRUE, Microsoft SQL Server не записывает в журнал транзакций информацию о следующих операциях: операции массового копирования, Select into, WRITETEXT и UPDATETEXT. Поскольку эти операции не регистрируются в журнале транзакций, то SQL Server не сможет использовать восстановление журнала транзакций для отмены этих операций.
Если же выполняется одно из этих действий, когда опции select into/bulkcopy установлены в TRUE, то необходимо убедиться в том что резервная копия содержала изменения, сделанные этими операциями, в случае если потребуется последующее восстановление.
Резервное копирование журнала транзакций
Для того чтобы повысить эффективность стратегии резервирования и восстановления БД, необходимо периодически делать резервные копии журнала транзакций. Создать резервную копию журнала транзакций можно с помощью команды BACKUP LOG. При использовании копирования журнала транзакций, при необходимости, базу данных можно восстановить на любой момент времени, содержащийся в копии журнала. Если Вы не резервируете журнал перед его усечением, то восстановить сможете только последнюю копию базы данных, все изменения прошедшие с этого времени будут потеряны.
После того как Microsoft SQL Server заканчивает резервное копирование журнала транзакций, он усекает его неактивную часть, тем самым, высвобождая место. SQL Server может повторно использовать высвобожденное место, т.к. журнал транзакций непрерывно растет и ему требуется свободное пространство. Активная часть журнала содержит изменения, которые были сделаны в базе и еще не зафиксированы на диске.
Microsoft SQL Server пытается запустить процесс контрольной точки всякий раз когда журнал транзакций заполняется более чем на 70 процентов, или при получении ошибки переполнения журнала транзакций, а также при останове SQL Server (если используется SHUTDOWN WITH NOWAIT) операция контрольной точки будет запущена для каждой базы данных. При включенной опции 'trunc. log on chkpt.' становится бесполезным выполнение резервного копирования журнала транзакций, поскольку информация о производимых изменениях постоянно уничтожается и неактивная часть журнала транзакций урезается каждый раз после выполнении процесса контрольной точки. По существу эта опция показывает, что Вы не сможете использовать журнал транзакций при восстановлении. Журнал транзакций необходим для отката изменений и в процессе регенерации при старте SQL Server. Используйте эту опцию только для тех систем, для которых не важны потери изменений, сделанных в течение всего дня, потому что в этом случае Вы сможете восстановить только последнюю копию базы данных, а сделанные позже изменения восстановить будет невозможно. Применяется это редко.
Если журнал транзакций урезается с помощью оператора BACKUP LOG , то нельзя делать его копию до тех пор, пока не будет создана полная копия базы данных или дифференциальная копия. Дифференциальная копия содержит в себе только те изменения, которые произошли с момента последней полной копии базы данных.
Также желательно избегать резервирования журнала транзакций после любых не журналируемых операций, которые произошли после последнего полного резервного копирования базы данных. Сделайте лучше полную копию базы данных или разностное резервное копирование.
И в заключении, при добавлении или удалении любого файла из базы данных Вы должны создать полную копию. Восстановить в этом случае базу данных на момент, предшествующий ее изменению, используя журнал транзакций, не удастся.
Изменение опций базы данных
Усечение журнала транзакций после запуска процесса контрольной точки может быть выполнено на уровне базы данных, используя хранимую процедуру sp_dboption, которая изменяет конфигурационные настройки базы. Например:
exec sp_dboption pubs 'trunc. log on chkpt.', 'false'
Эта команда отменит усечение журнала транзакций для базы данных pubs. Чтобы увидеть список всех текущих настроек базы данных, можно просто запустить эту процедуру без дополнительных параметров. Например:
exec sp_dboption pubs
Также опции БД можно изменить в Enterprise Manager. Для впервые созданной базы данных наибольшая часть опций установлена в значение False. В Microsoft SQL Server Desktop edition, однако, опция усечения журнала транзакций в контрольной точке установлена в значение True. На практике это может и не создавать проблем с восстановлением данных, все зависит от схемы резервного копирования и восстановления.
Также Вы можете установить опцию усечения журнала транзакций после контрольной точки на серверах разработчиков прикладных программ, поскольку в этом случае не так важно сохранять каждую тестовую транзакцию.
Эта статья дает лишь сжатое представление о том, как использовать журнал транзакций Microsoft SQL Server. Тема резервного копирования и восстановления баз данных достаточно сложна и мы ее коснулись лишь только поверхностно. Главная задача этой статьи показать какое важное значение имеет журнал транзакций. Часто новые базы данных создаются с очень маленьким размером журнала транзакций и с использованием опции 'trunc. log on chkpt.'. Эта опасная комбинация потому как в этом случае журнал транзакций нельзя будет использовать после сбоев оборудования или программных ошибок, а также ошибок системы. Убедитесь в том, что Ваши базы данных SQL Server надежно защищены, планируя и осуществляя резервное копирование журнала транзакций, а также продумав эффективный план восстановления.
Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction.
The transaction log is a critical component of the database. If there is a system failure, you will need that log to bring your database back to a consistent state.
For information about the transaction log architecture and internals, see the SQL Server Transaction Log Architecture and Management Guide.
Never delete or move this log unless you fully understand the ramifications of doing so.
Known good points from which to begin applying transaction logs during database recovery are created by checkpoints. For more information, see Database Checkpoints (SQL Server).
Что происходит при простой модели восстановления?
Теперь давайте установим для базы данных tranlogexperiment простой режим восстановления.
Если выполнить код, представленный в листинге 4, мы получим несколько отличное поведение.
На рис.6 показан рост журнала транзакций при простом режиме восстановления, когда мы выполняем код из листинга 4. Размер физического файла журнала всего 15Мб. Это вдвое меньше, чем он был ранее при использовании полной модели восстановления. Также заметим, что свободное пространство составляет 11,5Мб.
Рис.6: Рост журнала после выполнения кода в листинге 4 при простом режиме восстановления
Означает ли это меньший рост журнала?
Нет. На рис.7 показано, что в процессе выполнения сессии SQL Server установил несколько контрольных точек. Это произвело усечение журнала и дало возможность транзакциям возобновлять рост журнала через определенные промежутки времени.
Рис.7: Захват контрольных точек при помощи расширенных событий
Operations that can be minimally logged
Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery. This topic identifies the operations that are minimally logged under the bulk-logged recovery model (as well as under the simple recovery model, except when a backup is running).
Minimal logging is not supported for memory-optimized tables.
Under the full recovery model, all bulk operations are fully logged. However, you can minimize logging for a set of bulk operations by switching the database to the bulk-logged recovery model temporarily for bulk operations. Minimal logging is more efficient than full logging, and it reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction. However, if the database is damaged or lost when minimal logging is in effect, you cannot recover the database to the point of failure.
The following operations, which are fully logged under the full recovery model, are minimally logged under the simple and bulk-logged recovery model:
- Bulk import operations (bcp, BULK INSERT, and INSERT. SELECT). For more information about when bulk import into a table is minimally logged, see Prerequisites for Minimal Logging in Bulk Import.
When transactional replication is enabled, BULK INSERT operations are fully logged even under the Bulk Logged recovery model.
When transactional replication is enabled, SELECT INTO operations are fully logged even under the Bulk Logged recovery model.
Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data. Note that minimal logging is not used when existing values are updated. For more information about large value data types, see Data Types (Transact-SQL).
WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns. Note that minimal logging is not used when existing values are updated.
The WRITETEXT and UPDATETEXT statements are deprecated; avoid using them in new applications.
If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online. The minimally logged index operations are as follows:
CREATE INDEX operations (including indexed views).
ALTER INDEX REBUILD or DBCC DBREINDEX operations.
The DBCC DBREINDEX statement is deprecated; Do not use it in new applications.
Index build operations use minimial logging but may be delayed when there is a concurrently executing backup. This delay is caused by the synchronization requirements of minimally logged buffer pool pages when using the simple or bulk-logged recovery model.
DROP INDEX new heap rebuild (if applicable). Index page deallocation during a DROP INDEX operation is always fully logged.
Каждая база данных SQL Server имеет журнал транзакций, в котором фиксируются все транзакции и производимые ими в базе изменения.
Журнал транзакций — это важная составляющая базы данных. Если система даст сбой, этот журнал поможет вам вернуть базу данных в согласованное состояние.
Сведения об архитектуре и внутренних компонентах журнала транзакций см. в разделе Руководство по архитектуре журнала транзакций SQL Server и управлению им.
Удаляя или перемещая этот журнал, вы должны понимать все последствия этого действия.
Известные рабочие точки, от которых следует начинать применение журналов транзакций при восстановлении базы данных, создаются контрольными точками. Дополнительные сведения см. в статье Контрольные точки базы данных (SQL Server).
Supporting high availability and disaster recovery solutions
The standby-server solutions, Always On availability groups, database mirroring, and log shipping, rely heavily on the transaction log.
In an Always On availability groups scenario, every update to a database, the primary replica, is immediately reproduced in separate, full copies of the database, the secondary replicas. The primary replica sends each log record immediately to the secondary replicas, that applies the incoming log records to availability group databases, continually rolling it forward. For more information, see Always On Failover Cluster Instances
In a log shipping scenario, the primary server sends the active transaction log of the primary database to one or more destinations. Each secondary server restores the log to its local secondary database. For more information, see About Log Shipping.
In a database mirroring scenario, every update to a database, the principal database, is immediately reproduced in a separate, full copy of the database, the mirror database. The principal server instance sends each log record immediately to the mirror server instance, which applies the incoming log records to the mirror database, continually rolling it forward. For more information, see Database Mirroring.
Заключение
Журнал транзакций, безусловно, важный компонент базы данных SQL Server. Он влияет на все, что требует восстановления или зависит от него - бэкапы, восстановление, аварийное восстановление и т.д.
В данной статье мы обсудили природу журнала транзакций, аспекты его надлежащего обслуживания и продемонстрировали поведение DML в базах данных при полной или простой моделях восстановления. Однако это далеко не все, что можно узнать о журнале транзакций. Начните с изучения документации.
Операции, допускающие минимальное протоколирование
Минимальное протоколирование — это протоколирование только информации, необходимой для восстановления транзакции без поддержки восстановления на момент времени. В этом разделе определяются операции, которые подлежат минимальному протоколированию в модели восстановления с неполным протоколированием (как и в простой модели восстановления, кроме случаев, когда выполняется резервное копирование).
Минимальное протоколирование не поддерживается для оптимизированных для памяти таблиц.
В модели полного восстановлениявсе массовые операции полностью протоколируются. Однако для набора массовых операций можно использовать минимальное протоколирование, временно переключив базу данных на модель восстановления с неполным протоколированием во время массовых операций. Минимальное протоколирование более эффективно, чем полное, и снижает вероятность того, что во время массовой операции большого объема будет заполнено все доступное пространство журнала транзакций. Однако, если при включенном минимальном протоколировании база данных будет повреждена или потеряна, ее нельзя будет восстановить до точки сбоя.
Следующие операции, выполняемые с полным протоколированием в модели полного восстановления, осуществляются с минимальным протоколированием в простой модели восстановления и модели восстановления с неполным протоколированием:
- Операции массового импорта (bcp, BULK INSERT и INSERT. SELECT). Дополнительные сведения о том, когда массовый импорт в таблицу подлежит минимальному протоколированию, см. в разделе Prerequisites for Minimal Logging in Bulk Import.
Если включена репликация транзакций, операции BULK INSERT протоколируются полностью даже в модели восстановления с неполным протоколированием.
Если включена репликация транзакций, операции SELECT INTO протоколируются полностью даже в модели восстановления с неполным протоколированием.
Частичные изменения типов данных с большими значениями с помощью предложения .WRITE инструкции UPDATE при вставке или добавлении новых данных. Обратите внимание, что минимальное протоколирование не используется при обновлении существующих значений. Дополнительные сведения о больших типах-значениях см. в статье Типы данных (Transact-SQL).
Инструкции WRITETEXT и UPDATETEXT при вставке или добавлении новых данных в столбцы с типом данных text , ntext , и image . Обратите внимание, что минимальное протоколирование не используется при обновлении существующих значений.
Инструкции WRITETEXT и UPDATETEXT являются устаревшими , поэтому старайтесь не использовать их в новых приложениях.
Если в базе данных используется простая модель восстановления или модель восстановления с неполным протоколированием, некоторые DDL-операции с индексом протоколируются в минимальном объеме при их выполнении как режиме «вне сети», так и в режиме «в сети». Минимально протоколируются следующие операции с индексами.
ОперацииCREATE INDEX (включая индексированные представления).
ОперацииALTER INDEX REBUILD или DBCC DBREINDEX.
Инструкция DBCC DBREINDEX является устаревшей. Не используйте ее в новых приложениях.
Операции построения индекса используют минимальное ведение журнала, но могут быть отложены при одновременном выполнении резервного копирования. Эта задержка вызвана требованиями к синхронизации страниц буферного пула с минимальным протоколированием при использовании простой модели восстановления или модели восстановления с неполным протоколированием.
Перестроение новой кучи DROP INDEX (если применимо). Освобождение страниц индексов при выполнении операции DROP INDEX всегда протоколируется полностью.
Операции, поддерживаемые журналом транзакций
Журнал транзакций поддерживает следующие операции:
- восстановление отдельных транзакций;
- восстановление всех незавершенных транзакций при запуске SQL Server ;
- накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя;
- поддержка репликации транзакций;
- Поддержка решений высокой уровня доступности и аварийного восстановления: Группы доступности AlwaysOn, зеркальное отображение базы данных и доставка журналов.
Накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя
После потери оборудования или сбоя диска, затрагивающего файлы базы данных, можно восстановить базу данных на момент, предшествующий сбою. Сначала восстановите последнюю полную резервную копию и последнюю дифференциальную резервную копию базы данных, затем восстановите последующую серию резервных копий журнала транзакций до момента возникновения сбоя.
При восстановлении каждой резервной копии журнала, Компонент Database Engine повторно применяет все изменения, записанные в журнале, для наката всех транзакций. После восстановления последней резервной копии журнала Компонент Database Engine затем использует данные журнала для отката всех транзакций, которые не были завершены на момент сбоя. Дополнительные сведения см. в статье Обзор процессов восстановления (SQL Server).
Поддержка репликации транзакций
Агент чтения журнала следит за журналами транзакций всех баз данных, которые настроены для репликации транзакций, и копирует отмеченные для репликации транзакции из журнала транзакций в базу данных распространителя. Дополнительные сведения о репликации транзакций см. в разделе Как работает репликация транзакций.
Усечение журнала транзакций
Процесс усечения журнала освобождает место в файле журнала для повторного использования журналом транзакций. Необходимо регулярно усекать журнал транзакций, чтобы предотвратить переполнение выделенного пространства. По ряду причин его усечение может быть отложено, поэтому очень важно следить за размером журнала. Некоторые операции можно выполнять с минимальным протоколированием, чтобы сократить их вклад в размер журнала транзакций.
Усечение журнала удаляет неактивные виртуальные файлы журнала (VLF) из логического журнала транзакций базы данных SQL Server, освобождая в нем место для повторного использования физическим журналом транзакций. Если усечение журнала транзакций не выполняется, со временем он заполняет все доступное место на диске, отведенное для файлов физического журнала.
В целях предотвращения этой проблемы усечение журнала выполняется автоматически после следующих событий, за исключением тех случаев, когда оно по каким-то причинам задерживается:
- В простой модели восстановления — после достижения контрольной точки.
- Для моделей полного восстановления и моделей восстановления с неполным протоколированием, если контрольная точка была создана после предыдущего резервного копирования, усечение происходит после резервного копирования журнала (если только это не резервная копия журнала только для копирования).
Дополнительные сведения см. в разделе Факторы, которые могут вызвать задержку усечения журнала далее в этой статье.
Усечение журнала не приводит к уменьшению размера физического файла журнала. Для уменьшения реального размера физического файла журнала необходимо выполнить его сжатие. Сведения о сжатии физического файла журнала см. в разделе Управление размером файла журнала транзакций.
Следует учитывать факторы, которые могут повлиять на задержку усечения журнала. Если после сжатия журнала снова потребуется дисковое пространство, размер журнала транзакций снова будет увеличиваться, что повлияет на производительность во время операций увеличения.
Восстановление отдельных транзакций
Если приложение выдает инструкцию ROLLBACK или Компонент Database Engine обнаруживает ошибку, такую как потеря связи с клиентом, записи журнала используются для отката изменений, выполненных в результате незавершенной транзакции.
Характеристики журнала транзакций
Ниже приведены характеристики журнала транзакций Компонент SQL Server Database Engine.
Журнал транзакций выполнен как отдельный файл или набор файлов в базе данных. Кэш журнала управляется отдельно от буферного кэша для страниц данных, что приводит к простому, быстрому и устойчивому коду в пределах компонента Компонент SQL Server Database Engine. Дополнительные сведения см. в разделе Физическая архитектура журнала транзакций.
Формат записей журнала и страниц не обязан следовать формату страниц данных.
Журнал транзакций может располагаться в нескольких файлах. Вы можете задать для этих файлов автоматическое расширение, установив для журнала значение FILEGROWTH . Это снижает вероятность исчерпания пространства журнала транзакций, в то же самое время уменьшая административные издержки. Дополнительные сведения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп.
Механизм многократного использования пространства в файлах журналов действует быстро и оказывает минимальное влияние на пропускную способность транзакций.
Сведения об архитектуре и внутренних компонентах журнала транзакций см. в разделе Руководство по архитектуре журнала транзакций SQL Server и управлению им.
Усечение журнала транзакций
Процесс усечения журнала освобождает место в файле журнала для повторного использования журналом транзакций. Необходимо регулярно усекать журнал транзакций, чтобы предотвратить переполнение выделенного пространства. По ряду причин его усечение может быть отложено, поэтому очень важно следить за размером журнала. Некоторые операции можно выполнять с минимальным протоколированием, чтобы сократить их вклад в размер журнала транзакций.
Усечение журнала удаляет неактивные виртуальные файлы журнала (VLF) из логического журнала транзакций базы данных SQL Server, освобождая в нем место для повторного использования физическим журналом транзакций. Если усечение журнала транзакций не выполняется, со временем он заполняет все доступное место на диске, отведенное для файлов физического журнала.
В целях предотвращения этой проблемы усечение журнала выполняется автоматически после следующих событий, за исключением тех случаев, когда оно по каким-то причинам задерживается:
- В простой модели восстановления — после достижения контрольной точки.
- Для моделей полного восстановления и моделей восстановления с неполным протоколированием, если контрольная точка была создана после предыдущего резервного копирования, усечение происходит после резервного копирования журнала (если только это не резервная копия журнала только для копирования).
Дополнительные сведения см. в разделе Факторы, которые могут вызвать задержку усечения журнала далее в этой статье.
Усечение журнала не приводит к уменьшению размера физического файла журнала. Для уменьшения реального размера физического файла журнала необходимо выполнить его сжатие. Сведения о сжатии физического файла журнала см. в разделе Управление размером файла журнала транзакций.
Следует учитывать факторы, которые могут повлиять на задержку усечения журнала. Если после сжатия журнала снова потребуется дисковое пространство, размер журнала транзакций снова будет увеличиваться, что повлияет на производительность во время операций увеличения.
Характеристики журнала транзакций
Ниже приведены характеристики журнала транзакций Компонент SQL Server Database Engine.
Журнал транзакций выполнен как отдельный файл или набор файлов в базе данных. Кэш журнала управляется отдельно от буферного кэша для страниц данных, что приводит к простому, быстрому и устойчивому коду в пределах компонента Компонент SQL Server Database Engine. Дополнительные сведения см. в разделе Физическая архитектура журнала транзакций.
Формат записей журнала и страниц не обязан следовать формату страниц данных.
Журнал транзакций может располагаться в нескольких файлах. Вы можете задать для этих файлов автоматическое расширение, установив для журнала значение FILEGROWTH . Это снижает вероятность исчерпания пространства журнала транзакций, в то же самое время уменьшая административные издержки. Дополнительные сведения см. в разделе Параметры инструкции ALTER DATABASE (Transact-SQL) для файлов и файловых групп.
Механизм многократного использования пространства в файлах журналов действует быстро и оказывает минимальное влияние на пропускную способность транзакций.
Сведения об архитектуре и внутренних компонентах журнала транзакций см. в разделе Руководство по архитектуре журнала транзакций SQL Server и управлению им.
Восстановление отдельных транзакций
Если приложение выдает инструкцию ROLLBACK или Компонент Database Engine обнаруживает ошибку, такую как потеря связи с клиентом, записи журнала используются для отката изменений, выполненных в результате незавершенной транзакции.
Supporting transactional replication
The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database. For more information, see How Transactional Replication Works.
Operations supported by the transaction log
The transaction log supports the following operations:
- Individual transaction recovery.
- Recovery of all incomplete transactions when SQL Server is started.
- Rolling a restored database, file, filegroup, or page forward to the point of failure.
- Supporting transactional replication.
- Supporting high availability and disaster recovery solutions: Always On availability groups, database mirroring, and log shipping.
Как обслуживать журнал транзакций?
Администраторы баз данных, которые обслуживают экземпляры SQL Server установок IaaS, должны регулярно делать резервные копии журналов транзакций. Полезно иметь конфигурации аварийного восстановления, такие как Log Shipping или AlwaysOn AG. Подобные конфигурации выполняют резервирование автоматически.
При режиме полного восстановления, создание резервной копии журнала усекает те части журнала транзакций, которые больше не требуются для восстановления. Усечение журнала удаляет неактивные виртуальные файлы журнала. Тем самым освобождается место в журналах транзакций для последующего использования.
Код в листинге 6 показывает размер журнала транзакций и сколько в нем свободного пространства.
Рис. 3: Вывод кода в листинге 6
Мы можем также сжать физический журнал транзакций с помощью кода, приведенного в листинге 7. Перед сжатием проверьте, что у вас имеется резервная копия журнала транзакций. В условиях производства лучше сделать расписание создания бэкапов журнала, чтобы избежать неконтролируемого роста файла журнала транзакций и гарантировать сохранение данных. При сконфигурированной опции аварийного восстановления типа Log Shipping или AlwaysOn AG это уже гарантируется.
Вы можете обратиться к столбцу log_reuse_wait_desc представления каталога sys.databases, чтобы определить любые условия, которые препятствуют сжатию журнала транзакций. Обратите внимание на запрос этого столбца в листинге 3.
Такими условиями могут быть ожидание контрольной точки, ожидание резервирования журнала, идущие резервирование или восстановление, активная длительная транзакция, и подобные процессы в базе данных.
Рис.4: Используемое пространство после выполнения кода в листинге 7
Мы используем код в листинге 8 для создания резервной копии базы данных. В нашем конкретном случае мы должны сначала сделать полный бэкап, поскольку бэкапы журнала всегда ссылаются на полный бэкап. "Последний" полный бэкап начинает цепочку, когда происходит восстановление к заданному моменту времени.
При запуске базы данных в простом режиме восстановления журнал транзакций усекается при каждой контрольной точке. В этом режиме бэкапы журнала невозможны.
Местоположение файла журнала транзакций должно иметь надлежащий размер, чтобы удовлетворять длительным транзакциям, которые происходят время от времени. В противном случае журнал транзакций может заполнить все дисковое пространство. На рис.4 показано, что происходит с журналом транзакций, когда делается бэкап. Обратите внимание, что физический файл по-прежнему имеет размер 40Мб, но теперь у нас есть около 37Мб свободного пространства.
Рис.5: Журнал транзакций после создания резервной копии
Recovery of all incomplete transactions when SQL Server is started
If a server fails, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. When an instance of SQL Server is started, it runs a recovery of each database. Every modification recorded in the log that may not have been written to the data files is rolled forward. Every incomplete transaction found in the transaction log is then rolled back to make sure the integrity of the database is preserved. For more information, see Restore and Recovery Overview (SQL Server).
Transaction log truncation
Log truncation frees space in the log file for reuse by the transaction log. You must regularly truncate your transaction log to keep it from filling the allotted space. Several factors can delay log truncation, so monitoring log size matters. Some operations can be minimally logged to reduce their impact on transaction log size.
Log truncation deletes inactive virtual log files (VLFs) from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space allocated to physical log files.
To avoid running out of space, unless log truncation is delayed for some reason, truncation occurs automatically after the following events:
- Under the simple recovery model, after a checkpoint.
- Under the full recovery model or bulk-logged recovery model, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).
For more information, see Factors that can delay log truncation, later in this topic.
Log truncation does not reduce the size of the physical log file. To reduce the physical size of a physical log file, you must shrink the log file. For information about shrinking the size of the physical log file, see Manage the Size of the Transaction Log File.
However, keep in mind Factors that can delay log truncation. If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log grow operations.
Related tasks
Управление журналом транзакций
Резервное копирование журнала транзакций (модель полного восстановления)
Восстановление журнала транзакций (модель полного восстановления)
Требованием реляционных систем баз данных является надежность (durable) транзакций. Эта "D" присутствует в свойствах транзакций ACID. Система должна гарантировать, что при внезапном сбое транзакция может быть повторена. SQL Server выполняет это требование записью всех транзакций в физический файл, который называется файлом журнала транзакций.
В сущности, всякий раз, когда фиксируется транзакция, SQL Server записывает изменения, произведенные этой транзакцией в журнал транзакций. Даже если результаты выполнения транзакции отсутствуют в файле данных, они доступны в журнале транзакций и могут быть воспроизведены в случае внезапного сбоя.
Операции, поддерживаемые журналом транзакций
Журнал транзакций поддерживает следующие операции:
- восстановление отдельных транзакций;
- восстановление всех незавершенных транзакций при запуске SQL Server ;
- накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя;
- поддержка репликации транзакций;
- Поддержка решений высокой уровня доступности и аварийного восстановления: Группы доступности AlwaysOn, зеркальное отображение базы данных и доставка журналов.
Структура журнала транзакций
Журнал транзакций - это физический файл, видимый в операционной системе, где находится база данных SQL Server. Каждая база данных имеет один журнал транзакций, но возможно сконфигурировать и больше. Дело в том, что наличие нескольких журналов транзакций не дает каких-либо преимуществ с точки зрения производительности. SQL Server выполняет запись в журнал транзакций последовательно - один файл должен быть заполнен, прежде чем использовать следующий. Однако несколько файлов, размещаемых на разных дисках, могут спасти положение, если первый файл заполнится.
Изнутри файл журнала транзакций представляет собой набор виртуальных файлов журнала. Размер и число таких файлов влияет на время, требуемое на резервирование базы данных или вывод её в рабочий режим. Полезно правильно устанавливать размер журнала транзакций и быть уверенным, что установка автоматического прироста соответствует ожидаемому уровню активности. Т.е. рост файла не должен происходить очень часто.
Поддержка решений высокого уровня доступности и аварийного восстановления
Решения резервного сервера, Группы доступности AlwaysOn, зеркальное отображение базы данных и доставка журналов в значительной степени опираются на журнал транзакций.
В сценарии "Группы доступности AlwaysOn" каждое изменение в базе данных (первичной реплике) немедленно воспроизводится в ее полных автономных копиях (вторичных репликах). Первичная реплика немедленно отсылает каждую запись журнала во вторичные реплики, которые применяют входящие записи к базам данных групп доступности, производя непрерывный накат. Дополнительные сведения см. в разделе Экземпляры отказоустойчивого кластера AlwaysOn.
В сценарии доставки журналов основной сервер отправляет активный журнал транзакций основной базы данных в определенное назначение или множество назначений. Каждый сервер-получатель восстанавливает журнал в свою локальную базу данных-получатель. Дополнительные сведения см. в разделе Сведения о доставке журналов.
В сценарии зеркального отражения базы данных каждое изменение в базе данных (основной базе данных) немедленно воспроизводится в ее полной автономной копии (зеркальной базе данных). Экземпляр основного сервера немедленно отсылает каждую запись журнала в экземпляр зеркального сервера, который применяет входящие записи к зеркальной базе данных, путем ее непрерывного наката. Дополнительные сведения см. в разделе Зеркальное отображение базы данных.
Factors that can delay log truncation
When log records remain active for a long time, transaction log truncation is delayed, and the transaction log can fill up, as we mentioned earlier in this long topic.
For information about how to respond to a full transaction log, see Troubleshoot a Full Transaction Log (SQL Server Error 9002).
Really, Log truncation can be delayed by a variety of reasons. Learn what, if anything, is preventing your log truncation by querying the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view. The following table describes the values of these columns.
A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. Note that long-running transactions prevent log truncation under all recovery models, including the simple recovery model, under which the transaction log is generally truncated on each automatic checkpoint.
A transaction is deferred. A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions (SQL Server).
Related tasks
Управление журналом транзакций
Резервное копирование журнала транзакций (модель полного восстановления)
Восстановление журнала транзакций (модель полного восстановления)
Каждая база данных SQL Server имеет журнал транзакций, в котором фиксируются все транзакции и производимые ими в базе изменения.
Журнал транзакций — это важная составляющая базы данных. Если система даст сбой, этот журнал поможет вам вернуть базу данных в согласованное состояние.
Сведения об архитектуре и внутренних компонентах журнала транзакций см. в разделе Руководство по архитектуре журнала транзакций SQL Server и управлению им.
Удаляя или перемещая этот журнал, вы должны понимать все последствия этого действия.
Известные рабочие точки, от которых следует начинать применение журналов транзакций при восстановлении базы данных, создаются контрольными точками. Дополнительные сведения см. в статье Контрольные точки базы данных (SQL Server).
Что вызывает рост журнала?
Давайте создадим небольшую базу данных, используя код в листинге 1. Файл данных вначале имеет размер 4Мб, файл журнала - 2Мб. Ваши промышленные базы данных никогда не будут иметь такой размер, особенно при популярной практике pre-allocation (предварительное выделение). Мы выбрали такой размер просто в демонстрационных целях.
В этой базе данных мы создаем единственную таблицу (листинг 2) для последующего выполнения операторов языка манипуляции данными (DML).
Выполнив код в листинге 3, проверим, что мы сделали.
Рис.1: Результаты выполнения кода в листинге 3, но до DML
Обратите внимание на столбец File size. Приступаем к наблюдению за ростом журнала транзакций при выполнении операторов INSERT и DELETE 100000 раз (листинг 4).
В листинге 4 выполняется вставка одной строки в таблицу txn_log с последующим её удалением; это действие повторяется 100000 раз.
В целом таблица не увеличивается в результате этих действий, однако журнал транзакций растет существенно. Если повторить запрос в листинге 3 после выполнения операторов DML из листинга 4, то увидим, насколько вырос журнал транзакций:
Рис.2: Результаты выполнения кода из листинга 3 после операторов DML
Журнал транзакций вырос с 4Мб до 40Мб в результате этих манипуляций, хотя файл данных не изменился в размерах. Это ясно показывает, что размер журнала транзакций имеет мало общего с размером данных. На размер журнала оказывает влияние интенсивность, с которой происходит изменение (DML) базы данных.
Модели восстановления и журналы транзакций
SQL Server поддерживает три модели восстановления - полную (Full), простую (Simple) и с неполным протоколированием (Bulk Logged).
При полной модели восстановления ВСЕ транзакции записываются в журнал. Таким образом, база данных может быть полностью восстановлена после сбоя. Это также означает, что резервная копия базы данных может быть восстановлена к заданному моменту времени, если доступен журнал транзакций или соответствующий бэкап. При моделях восстановления Full и Bulk Logged журналы транзакций усекаются всякий раз, когда выполняется бэкап журнала.
При простой модели восстановления также ВСЕ транзакции записываются. Однако журнал транзакций усекается всякий раз, когда база данных выполняет контрольную точку.
Контрольная точка устанавливается, когда SQL Server сбрасывает "грязные" буферы в файл данных. Грязные буферы это дисковые страницы, хранящиеся в памяти, которые были изменены транзакциями, в результате чего состояние памяти не соответствует состоянию на диске. Но мы не будем здесь это обсуждать. В простой модели восстановления SQL Server сохраняет все эти изменения в журнале транзакций до тех пор, пока они не будут сброшены на диск.
Восстановление всех незавершенных транзакций при запуске SQL Server
Если на сервере происходит сбой, базы данных могут остаться в состоянии, когда часть изменений не переписана из буферного кэша в файлы данных, но в них имеются изменения, совершенные незаконченными транзакциями. Когда экземпляр SQL Server будет запущен, он выполнит восстановление каждой базы данных. Каждое изменение, записанное в журнале, которое, возможно, не было записано в файлы данных, накатывается. Чтобы сохранить целостность базы данных, будет также произведен откат каждой незавершенной транзакции, найденной в журнале транзакций. Дополнительные сведения см. в статье Обзор процессов восстановления (SQL Server).
Поддержка решений высокого уровня доступности и аварийного восстановления
Решения резервного сервера, Группы доступности AlwaysOn, зеркальное отображение базы данных и доставка журналов в значительной степени опираются на журнал транзакций.
В сценарии "Группы доступности AlwaysOn" каждое изменение в базе данных (первичной реплике) немедленно воспроизводится в ее полных автономных копиях (вторичных репликах). Первичная реплика немедленно отсылает каждую запись журнала во вторичные реплики, которые применяют входящие записи к базам данных групп доступности, производя непрерывный накат. Дополнительные сведения см. в разделе Экземпляры отказоустойчивого кластера AlwaysOn.
В сценарии доставки журналов основной сервер отправляет активный журнал транзакций основной базы данных в определенное назначение или множество назначений. Каждый сервер-получатель восстанавливает журнал в свою локальную базу данных-получатель. Дополнительные сведения см. в разделе Сведения о доставке журналов.
В сценарии зеркального отражения базы данных каждое изменение в базе данных (основной базе данных) немедленно воспроизводится в ее полной автономной копии (зеркальной базе данных). Экземпляр основного сервера немедленно отсылает каждую запись журнала в экземпляр зеркального сервера, который применяет входящие записи к зеркальной базе данных, путем ее непрерывного наката. Дополнительные сведения см. в разделе Зеркальное отображение базы данных.
Transaction log characteristics
Characteristics of the SQL Server Database Engine transaction log:
The transaction log is implemented as a separate file or set of files in the database. The log cache is managed separately from the buffer cache for data pages, which results in simple, fast, and robust code within the SQL Server Database Engine. For more information, see Transaction Log Physical Architecture.
The format of log records and pages is not constrained to follow the format of data pages.
The transaction log can be implemented in several files. The files can be defined to expand automatically by setting the FILEGROWTH value for the log. This reduces the potential of running out of space in the transaction log, while at the same time reducing administrative overhead. For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup Options.
The mechanism to reuse the space within the log files is quick and has minimal effect on transaction throughput.
For information about the transaction log architecture and internals, see the SQL Server Transaction Log Architecture and Management Guide.
Накат восстановленной базы данных, файла, файловой группы или страницы до момента сбоя
После потери оборудования или сбоя диска, затрагивающего файлы базы данных, можно восстановить базу данных на момент, предшествующий сбою. Сначала восстановите последнюю полную резервную копию и последнюю дифференциальную резервную копию базы данных, затем восстановите последующую серию резервных копий журнала транзакций до момента возникновения сбоя.
При восстановлении каждой резервной копии журнала, Компонент Database Engine повторно применяет все изменения, записанные в журнале, для наката всех транзакций. После восстановления последней резервной копии журнала Компонент Database Engine затем использует данные журнала для отката всех транзакций, которые не были завершены на момент сбоя. Дополнительные сведения см. в статье Обзор процессов восстановления (SQL Server).
Факторы, которые могут вызвать задержку усечения журнала
Когда записи журнала остаются активными длительное время, усечение журнала транзакций откладывается и возникает вероятность переполнения журнала транзакций, как описано ранее.
Дополнительные сведения о том, что нужно делать при переполнении журнала транзакций, см. в разделе Troubleshoot a Full Transaction Log (SQL Server Error 9002).
На самом деле усечение журнала может быть задержано из-за множества причин. Чтобы узнать причину, препятствующую усечению журнала транзакций в конкретном случае, выполните запрос по столбцам log_reuse_wait и log_reuse_wait_desc представления каталога sys.database. В следующей таблице описаны значения этих столбцов.
Во время начала создания резервной копии журнала может существовать длительная транзакция. В этом случае, чтобы освободить пространство, может потребоваться создание другой резервной копии журнала. Помните, что длительные транзакции препятствуют усечению журнала во всех моделях восстановления, включая простую модель восстановления, в которой журнал транзакций обычно усекается на каждой автоматической контрольной точке.
Транзакция отложена. Отложенная транзакция — это активная транзакция, откат которой был заблокирован по причине недоступности какого-либо ресурса. Дополнительные сведения о причинах, вызывающих появление отложенных транзакций, и о том, как их можно вывести из такого состояния, см. в статье Отложенные транзакции (SQL Server).
Восстановление всех незавершенных транзакций при запуске SQL Server
Если на сервере происходит сбой, базы данных могут остаться в состоянии, когда часть изменений не переписана из буферного кэша в файлы данных, но в них имеются изменения, совершенные незаконченными транзакциями. Когда экземпляр SQL Server будет запущен, он выполнит восстановление каждой базы данных. Каждое изменение, записанное в журнале, которое, возможно, не было записано в файлы данных, накатывается. Чтобы сохранить целостность базы данных, будет также произведен откат каждой незавершенной транзакции, найденной в журнале транзакций. Дополнительные сведения см. в статье Обзор процессов восстановления (SQL Server).
Individual transaction recovery
If an application issues a ROLLBACK statement, or if the Database Engine detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction.
Rolling a restored database, file, filegroup, or page forward to the point of failure
After a hardware loss or disk failure affecting the database files, you can restore the database to the point of failure. You first restore the last full database backup and the last differential database backup, and then restore the subsequent sequence of the transaction log backups to the point of failure.
As you restore each log backup, the Database Engine reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, the Database Engine then uses the log information to roll back all transactions that were not complete at that point. For more information, see Restore and Recovery Overview (SQL Server).
Операции, допускающие минимальное протоколирование
Минимальное протоколирование — это протоколирование только информации, необходимой для восстановления транзакции без поддержки восстановления на момент времени. В этом разделе определяются операции, которые подлежат минимальному протоколированию в модели восстановления с неполным протоколированием (как и в простой модели восстановления, кроме случаев, когда выполняется резервное копирование).
Минимальное протоколирование не поддерживается для оптимизированных для памяти таблиц.
В модели полного восстановлениявсе массовые операции полностью протоколируются. Однако для набора массовых операций можно использовать минимальное протоколирование, временно переключив базу данных на модель восстановления с неполным протоколированием во время массовых операций. Минимальное протоколирование более эффективно, чем полное, и снижает вероятность того, что во время массовой операции большого объема будет заполнено все доступное пространство журнала транзакций. Однако, если при включенном минимальном протоколировании база данных будет повреждена или потеряна, ее нельзя будет восстановить до точки сбоя.
Следующие операции, выполняемые с полным протоколированием в модели полного восстановления, осуществляются с минимальным протоколированием в простой модели восстановления и модели восстановления с неполным протоколированием:
- Операции массового импорта (bcp, BULK INSERT и INSERT. SELECT). Дополнительные сведения о том, когда массовый импорт в таблицу подлежит минимальному протоколированию, см. в разделе Prerequisites for Minimal Logging in Bulk Import.
Если включена репликация транзакций, операции BULK INSERT протоколируются полностью даже в модели восстановления с неполным протоколированием.
Если включена репликация транзакций, операции SELECT INTO протоколируются полностью даже в модели восстановления с неполным протоколированием.
Частичные изменения типов данных с большими значениями с помощью предложения .WRITE инструкции UPDATE при вставке или добавлении новых данных. Обратите внимание, что минимальное протоколирование не используется при обновлении существующих значений. Дополнительные сведения о больших типах-значениях см. в статье Типы данных (Transact-SQL).
Инструкции WRITETEXT и UPDATETEXT при вставке или добавлении новых данных в столбцы с типом данных text , ntext , и image . Обратите внимание, что минимальное протоколирование не используется при обновлении существующих значений.
Инструкции WRITETEXT и UPDATETEXT являются устаревшими , поэтому старайтесь не использовать их в новых приложениях.
Если в базе данных используется простая модель восстановления или модель восстановления с неполным протоколированием, некоторые DDL-операции с индексом протоколируются в минимальном объеме при их выполнении как режиме «вне сети», так и в режиме «в сети». Минимально протоколируются следующие операции с индексами.
ОперацииCREATE INDEX (включая индексированные представления).
ОперацииALTER INDEX REBUILD или DBCC DBREINDEX.
Инструкция DBCC DBREINDEX является устаревшей. Не используйте ее в новых приложениях.
Операции построения индекса используют минимальное ведение журнала, но могут быть отложены при одновременном выполнении резервного копирования. Эта задержка вызвана требованиями к синхронизации страниц буферного пула с минимальным протоколированием при использовании простой модели восстановления или модели восстановления с неполным протоколированием.
Перестроение новой кучи DROP INDEX (если применимо). Освобождение страниц индексов при выполнении операции DROP INDEX всегда протоколируется полностью.
Поддержка репликации транзакций
Агент чтения журнала следит за журналами транзакций всех баз данных, которые настроены для репликации транзакций, и копирует отмеченные для репликации транзакции из журнала транзакций в базу данных распространителя. Дополнительные сведения о репликации транзакций см. в разделе Как работает репликация транзакций.
Факторы, которые могут вызвать задержку усечения журнала
Когда записи журнала остаются активными длительное время, усечение журнала транзакций откладывается и возникает вероятность переполнения журнала транзакций, как описано ранее.
Дополнительные сведения о том, что нужно делать при переполнении журнала транзакций, см. в разделе Troubleshoot a Full Transaction Log (SQL Server Error 9002).
На самом деле усечение журнала может быть задержано из-за множества причин. Чтобы узнать причину, препятствующую усечению журнала транзакций в конкретном случае, выполните запрос по столбцам log_reuse_wait и log_reuse_wait_desc представления каталога sys.database. В следующей таблице описаны значения этих столбцов.
Во время начала создания резервной копии журнала может существовать длительная транзакция. В этом случае, чтобы освободить пространство, может потребоваться создание другой резервной копии журнала. Помните, что длительные транзакции препятствуют усечению журнала во всех моделях восстановления, включая простую модель восстановления, в которой журнал транзакций обычно усекается на каждой автоматической контрольной точке.
Транзакция отложена. Отложенная транзакция — это активная транзакция, откат которой был заблокирован по причине недоступности какого-либо ресурса. Дополнительные сведения о причинах, вызывающих появление отложенных транзакций, и о том, как их можно вывести из такого состояния, см. в статье Отложенные транзакции (SQL Server).
Читайте также: