Журнал транзакций sql: Как просмотреть журналы транзакций в SQL Server 2008

Я использую репликацию журнала транзакций в своей производственной БД sql server 2008. У меня возникли проблемы с ростом журнала транзакций, хотя я поддерживаю журнал транзакций каждые 15 минут. как…

Это может показаться странным вопросом,но я просто не могу найти журналы установки моей Экспресс-установки Microsoft SQL Server 2008. Согласно нескольким статьям службы поддержки Microsoft , они…

В SQL Server 2008 есть ли способ отключить журнал транзакций или очистить файл журнала? Когда я выполняю один запрос в своем проекте (очень большой с точки зрения транзакции), размер этого файла…

У нас есть база данных SQL Server 2008 R2, которая время от времени создает резервные копии журналов транзакций. Сегодня около 12 часов ночи в базе данных произошла большая ошибка… У меня есть…

Можно ли отфильтровать содержимое данных столбцов, опубликованных в стандартной транзакционной репликации базы данных SQL Server 2008 R2? Если я фильтрую сами столбцы, они полностью исчезают в. ..

Поддерживает ли Amazon RDS (sql-server) журналы транзакций sql? если так Как я могу включить его через интерфейс RDS? Как я могу получить к нему доступ?

Если у меня есть SQL Server 2008R2 compatibility 90 production database и SQL Server 2005 dev database. Могу ли я восстановить журналы транзакций из production в dev? Журналы транзакций…

Мы запускаем базу данных AlwaysOn SQL Server с 2 серверами. Все работает отлично, но у нас действительно есть огромные изменения данных каждый день. Наш DB составляет около 60 ГБ, но мы очень часто…

Как ограничить рост 1с журнала транзакций 1С+MS-SQL?   

Очень часто в форумах слышатся жалобы, что журнал транзакций в базе данных достиг размера, в разы, превышающего размер самой базы данных. Как же его можно ограничить?

Очень просто. Для этого:

• Открываем Enterprise Manager, находим там наш сервер и нашу базу данных, включаем свойства базы данных.
• Переходим на закладку «Options».
• Выставляем опцию Recovery model в режим «Simple».
• Ставим отметку в поле «Auto shrink».

Режим «Simple» — служит для того, чтобы полностью отказаться от идеи хранения лога транзакций. Как только транзакция завершена со статусом «Успешно», лог по ней удаляется, но размер файла при этом не меняется. А вот, собственно, «

На особо нагруженных базах не рекомендуется устанавливать опцию «Auto shrink», так как урезка лог файла создает дополнительную нагрузку на сервер. В любом случае, урезать лог файл можно и вручную. Для этого открываем Enterprise Manager, находим там наш сервер и нашу базу данных, по ней щелкаем правой кнопкой мыши и из выпадающего контекстного меню выбираем «Shrink Database...».

Далее, нам откроется диалоговое выбора режима сжатия.

Красным отмечен очень интересный пункт. Эта опция позволяет не только обрезать лог и сжать файл данных, но и переупорядочить даные на страницах, путем более плотного их размещения. Размер базы данных может значительно уменьшиться для тех баз, где давно не выполнялось это действие.

Перепечатка, воспроизведение в любой форме, распространение, в том числе в переводе, любых материалов с сайта www.softpoint.ru возможны только с письменного разрешения компании «СофтПоинт». Это правило действует для всех без исключения случаев, кроме тех, когда в материале прямо указано разрешение на копирование (основание: Закон Российской Федерации «Об авторском праве и смежных правах»).

Резервное копирование журналов транзакций SQL

Резервное копирование журналов транзакций SQL

Программа Backup Exec предлагает два способа резервного копирования журнала транзакций: Журнал и Журнал без усечения.

При создании резервной копии журнала рекомендуется использовать только программу Backup Exec для усечения журнала. Если журнал будет усечен в ходе выполнения другой операции, то перед созданием следующей резервной копии журнала вам потребуется выполнить полное или дифференциальное резервное копирование.

Способ Журнал без усечения следует выбирать только в том случае, если база данных повреждена, либо утеряны файлы базы данных. При этом способе выполняется резервное копирование транзакций, доступ к которым при таком состоянии базы данных в ином случае может отсутствовать. Эту резервную копию журнала транзакций можно затем использовать вместе с последней резервной копией базы данных и предыдущими резервными копиями журнала транзакций для восстановления базы данных на момент аварии; однако все не зафиксированные транзакции будут отброшены. При использовании способа Журнал без усечения зафиксированные транзакции не удаляются после резервного копирования журнала.

Для применения резервной копии, созданной способом Журнал без усечения для восстановления базы данных следует иметь также резервную копию базы данных, созданную до резервной копии Log No Truncate. Журнал транзакций содержит только файлы журнала, которые применяются в процессе восстановления, что недостаточно для восстановления базы данных в целом. Для восстановления базы данных необходимо иметь по крайней мере одну резервную копию базы данных либо полный набор резервных копий групп файлов и резервную копию журнала базы данных.

Для просмотра опций базы данных в Enterprise Manager на сервере SQL щелкните правой кнопкой мыши на базе данных, выберите Свойства, затем вкладку Опции и просмотрите параметры конфигурации.

При работе с SQL 7.0 не выполняйте резервное копирование журнала этими способами, если выбрана опция базы данных SQL «выбрать в/массовое копирование» и с момента создания последней резервной копии базы данных в ней выполнялись операции, не внесенные в журнал. Не внесенные в журнал операции нарушают последовательность резервных копий журнала транзакций. Восстановление базы данных с помощью резервных копий базы данных и журнала транзакций будет успешным только в том случае, если последовательность резервных копий журнала транзакций не была нарушена после последнего резервного копирования базы данных или дифференциального резервного копирования. Если же эта последовательность была нарушена, следует выполнить резервное копирование базы данных или дифференциальное резервное копирование, а затем снова выполнить резервное копирование журнала для сохранения всех изменений, необходимых для восстановления базы данных.

Как создать резервную копию журналов транзакций SQL

1. В панели навигации щелкните на стрелке рядом со значком Резервное копирование.

2. Выберите .

3. В панели Свойства в разделе Источник нажмите и выберите данные, резервное копирование которых необходимо выполнить.

4. Для того чтобы выбрать данные SQL, нажмите значок с именем домена или значки, содержащие экземпляры SQL, а затем нажмите значок системы Windows, содержащей экземпляр SQL. При работе с кластерным сервером выберите ресурсы резервного копирования на виртуальном сервере.

   Появится список общих сетевых каталогов, а также значок, представляющий экземпляр SQL.

5. Для выбора свойств задания резервного копирования SQL в панели Свойства найдите раздел Параметры и выберите .

6. После выбора опций запустите задание резервного копирования либо выберите другие опции резервного копирования в панели Свойства, а затем запустите задание резервного копирования.

НОУ ИНТУИТ | Лекция | Полная модель восстановления

Аннотация: Данная лекция посвящена полной модели восстановления. Рассматривается модель восстановления с неполным протоколированием, восстановление базы из резервных копий и восстановление базы данных при простой стратегии резервного копирования с использованием T-SQL

Как уже упоминалось ранее, необходимо заранее указать SQL Server, какую стратегию резервного копирования вы планируете реализовать. Если используются только полные и разностные резервные копии базы данных, то следует выбрать простую модель восстановления. Если нужно использовать также резервные копии журналов транзакций, то следует выбрать полную модель восстановления ( FULL ) или модель с неполным протоколированием BULK_LOGGED. Выбрав полную модель восстановления, мы сообщаем SQL Server, что необходимо создавать резервные копии журнала транзакций. Чтобы сделать это возможным, SQL Server сохраняет все транзакции в журнале транзакций до тех пор, пока не будет создана резервная копия журнала. При выполнении резервного копирования журнала транзакций, SQL Server выполняет усечение журнала транзакций после того, как резервная копия записана на устройство резервного копирования. В простом режиме журнал транзакций усекается после каждой контрольной точки; это означает, что зафиксированные транзакции (которые уже записаны в файлы данных) удаляются из журнала транзакций. Таким образом, в простом режиме резервные копии журнала транзакций не могут быть созданы.

Чтобы выбрать модель восстановления FULL, можно использовать инструкцию ALTER DATABASE. Следующий код устанавливает режим восстановления базы данных AdventurеWorks в значение FULL.

USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

Модель восстановления с неполным протоколированием

В модели полного восстановления высокопроизводительные операции массового копирования (операции, которые изменяют сразу большие количества данных) полностью протоколируются, чтобы сделать возможным резервное копирование журнала транзакций. В некоторых базах данных эта модель восстановления не может использоваться регулярно из-за ограничений на размер журнала транзакций и проблемы с производительностью, которые появляются из-за полного протоколирования высокопроизводительных операций массового копирования. Логично, что, существует модель восстановления с неполным протоколированием. Она позволяет создавать резервные копии журнала транзакций для фиксации и журнала, и результатов любых высокопроизводительных операций массового копирования, но при этом не имеет указанных недостатков. В рамках модели восстановления с неполным протоколированием невозможно восстановить базу данных на определенный момент времени. Кроме того, невозможно выполнять резервное копирование журнала транзакций, когда файл данных повр ежден, а высокопроизводительная операция массового копирования может произойти после того, как резервная копия файла журнала транзакций уже была создана. Это один из основных недостатков резервного копирования журнала транзакций. Следовательно, модель восстановления с неполным протоколированием следует включать только на период выполнения высокопроизводительных операций массового копирования и, по возможности, на непродолжительное время. В остальное время следует использовать полную модель восстановления. Не используйте модель восстановления с неполным протоколированием, если у вас не возникает проблем при использовании только полностью протоколируемых операций. Дополнительную информацию можно найти в Электронной документации по SQL Server 2005, тема «Резервное копирование в модели восстановления с неполным протоколированием».

Создание резервных копий журнала транзакций

Чтобы выполнить резервное копирование журнала транзакций, следует выбрать полную модель восстановления, причем после изменения модели восстановления должна быть создана хотя бы одна полная резервная копия базы данных. Резервное копирование журнала транзакций выполняется с помощью инструкции BACKUP LOG. Как всегда, следует указать имя базы данных и устройство резервного копирования. Типы устройств резервного копирования в этом случае не отличаются от тех, которые используются для создания полных и разностных резервных копий.

Чтобы создать резервную копию журнала транзакций базы данных AdventurеWorks на физическом устройстве, выполните следующие действия.

Создаем резервную копию файла журнала транзакций

1. Установите полную ( FULL ) модель восстановления.
2. intuit.ru/2010/edi»>Создайте хотя бы одну полную резервную копию базы данных.
3. Создайте резервную копию журнала транзакций базы данных AdventurеWorks на физическом устройстве при помощи следующей инструкции SQL:

   USE master;
   GO
   BACKUP LOG AdventureWorks
   TO DISK='t:\adv_log.bak'

Как и в других инструкциях резервного копирования, процесс дописывает резервную копию на устройство резервного копирования, если в инструкции BACKUP не указан другой вариант. Чтобы перезаписать резервные копии на устройстве, используется инструкция WITH INIT.

USE master;
GO
BACKUP LOG AdventureWorks
TO DISK='t:\adv_log.bak'
WITH INIT

Восстановление базы данных из резервных копий

В предыдущем разделе мы выполняли различные виды резервного копирования, но процесс восстановления при этом подразумевался только в теории. Теперь мы узнаем, как восстановить базу данных различными способами.

Извлечение информации резервного копирования

Прежде, чем приступить к восстановлению базы данных, необходимо знать, какую резервную копию выбрать для восстановления. SQL Server хранит историю резервного копирования, в которой есть информация о каждой отдельной резервной копии, созданной для базы данных, в базе данных msdb. Чтобы найти нужную для восстановления резервную копию, можно выполнить запрос к базе данных msdb.

Создаем информацию простого резервного копирования

1. В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, SQL Server Management Studio (Все программы, Microsoft SQL Server 2005, Среда SQL Server Management Studio).
2. В диалоговом окне Connect To Server (Соединение с сервером) нажмите кнопку Connect (Соединить).
3. В панели инструментов Standard (Стандартная) нажмите кнопку New Query (Новый запрос), чтобы открыть окно New Query (Новый запрос).
4. Введите и выполните следующие инструкции BACKUP, чтобы создать полную и разностную резервные копии базы данных AdventurеWorks.

   ALTER DATABASE AdventureWorks
   SET RECOVERY SIMPLE;
   —Создаем полную резервную копию базы данных
   BACKUP DATABASE AdventureWorks
   TO DISK = "T:\BACKUPS\ADVFULL.BAK"
   WITH INIT;
   —Создаем разностную резервную копию 
   BACKUP DATABASE AdventureWorks   
   TO DISK = "T:\BACKUPS\ADVDIFF.BAK" 
   WITH INIT,Differential;

Получаем общую информацию резервного копирования

1. Чтобы получить информацию о том, какие резервные копии были созданы в базе данных AdventurеWorks, выполните следующую инструкцию SELECT:

   USE msdb
   GO
   SELECT backup_start_date,type, physical_device_name,backup_set_id
   FROM backupset bs inner join backupmediafamily bm
   ON bs.media_set_id = bm.media_set_id
   WHERE database_name ='AdventureWorks'
   ORDER BY backup_start_date desc

   Панель результатов, показанная на рисунке, сообщает, что тип самой последней резервной копии — I, что соответствует разностной копии. Как вам известно, чтобы восстановить данные из резервной копии, сначала необходимо восстановить данные из самой последней полной резервной копии. Эту резервную копию мы видим во второй строке, ее тип D указывает на то, что это полная резервная копия.

2. Каждая резервная копия получает уникальный идентификатор, который называется идентификатором набора резервных копий; этот идентификатор можно увидеть в окне результатов, которое показано на следующем рисунке.

   Используя приведенную выше информацию, можно узнать, какие файлы данных и журналов были включены в резервные копии и в каких папках они первоначально хранились. Следующий запрос получает эту информацию для идентификатора набора 62. Выполняя этот запрос, обязательно измените backup_set_id с 62 на идентификатор полной резервной копии, найденной на предыдущем этапе.

   SELECT filegroup_name,logical_name,physical_name
   FROM msdb..backupfile
   WHERE backup_set_id = 62
   —change to your backup_set_id

3. intuit.ru/2010/edi»>В некоторых ситуациях в базе данных msdb может не оказаться необходимой информации. Это может произойти, если база данных msdb была повреждена при аварийной ситуации или если резервное копирование выполнялось на другой системе. В этих случаях можно получить необходимую информацию только непосредственно с устройства резервного копирования. Чтобы получить информацию о резервных копиях, которые находятся на T:\BACKUPS\ADVFULL.BAK’, введите и выполните следующую инструкцию.

   RESTORE HEADERONLY FROM DISK='T:\BACKUPS\ADVFULL.BAK'

4. Чтобы получить информацию о файлах данных и журналов, которые были включены в резервные копии, хранящиеся на данном устройстве, выполните инструкцию RESTORE FILELISTONLY.

   RESTORE FILELISTONLY 
   FROM DISK ='T:\BACKUPS\ADVFULL.BAK'

Настройка резервного копирования 1C и сжатие файла лога транзакций MS SQL при помощи Transact-SQL

Форматы дат Transact-SQL
Формат	Значение	Пример
1	select convert(varchar, getdate(), 1)	12/30/06
2	select convert(varchar, getdate(), 2)	06. 12.30
3	select convert(varchar, getdate(), 3)	30/12/06
4	select convert(varchar, getdate(), 4)	30.12.06
5	select convert(varchar, getdate(), 5)	30-12-06
6	select convert(varchar, getdate(), 6)	30 Dec 06
7	select convert(varchar, getdate(), 7)	Dec 30, 06
10	select convert(varchar, getdate(), 10)	12-30-06
11	select convert(varchar, getdate(), 11)	06/12/30
12	select convert(varchar, getdate(), 12)	061230
23	select convert(varchar, getdate(), 23)	2006-12-30
101	select convert(varchar, getdate(), 101)	12/30/2006
102	select convert(varchar, getdate(), 102)	2006. 12.30
103	select convert(varchar, getdate(), 103)	30/12/2006
104	select convert(varchar, getdate(), 104)	30.12.2006
105	select convert(varchar, getdate(), 105)	30-12-2006
106	select convert(varchar, getdate(), 106)	30 Dec 2006
107	select convert(varchar, getdate(), 107)	Dec 30, 2006
110	select convert(varchar, getdate(), 110)	12-30-2006
111	select convert(varchar, getdate(), 111)	2006/12/30
112	select convert(varchar, getdate(), 112)	20061230
Только время
8	select convert(varchar, getdate(), 8)	00:38:54
14	select convert(varchar, getdate(), 14)	00:38:54:840
24	select convert(varchar, getdate(), 24)	00:38:54
108	select convert(varchar, getdate(), 108)	00:38:54
114	select convert(varchar, getdate(), 114)	00:38:54:840
Дата и время
0	select convert(varchar, getdate(), 0)	Dec 12 2006 12:38AM
9	select convert(varchar, getdate(), 9)	Dec 30 2006 12:38:54:840AM
13	select convert(varchar, getdate(), 13)	30 Dec 2006 00:38:54:840AM
20	select convert(varchar, getdate(), 20)	2006-12-30 00:38:54
21	select convert(varchar, getdate(), 21)	2006-12-30 00:38:54. 840
22	select convert(varchar, getdate(), 22)	12/30/06 12:38:54 AM
25	select convert(varchar, getdate(), 25)	2006-12-30 00:38:54.840
100	select convert(varchar, getdate(), 100)	Dec 30 2006 12:38AM
109	select convert(varchar, getdate(), 109)	Dec 30 2006 12:38:54:840AM
113	select convert(varchar, getdate(), 113)	30 Dec 2006 00:38:54:840
120	select convert(varchar, getdate(), 120)	2006-12-30 00:38:54
121	select convert(varchar, getdate(), 121)	2006-12-30 00:38:54.840
126	select convert(varchar, getdate(), 126)	2006-12-30T00:38:54.840
127	select convert(varchar, getdate(), 127)	2006-12-30T00:38:54. 840

Восстановление удаленных данных из таблицы SQL с помощью журнала транзакций и номера LSN

Использование операции UPDATE или DELETE без условия WHERE часто приводит к потере данных из таблиц. Удаленные строки могут быть восстановлены, если известно время их удаления. Это можно сделать с помощью порядковых номеров регистрации (LSN). Это уникальный идентификатор, который присваивается каждой записи в журнале транзакций SQL Server. В следующем разделе статьи мы рассмотрим восстановления удаленных данных с помощью журнала транзакций и номеров LSN.

Чтобы восстанавливать строки, удаленные из таблицы, база данных SQL Server должна иметь BULK-LOGGED или FULL модель восстановления в момент первого удаления. Чтобы журналы были доступны для восстановления данных, необходимо выполнить следующие действия (применимо в SQL Server 2005, 2008, 2012, 2014 и 2016):

Проверьте количество строк в таблице базы данных, из которой были случайно удалены данные, используя следующий запрос:

Получите резервную копию журнала транзакций базы данных, используя приведенный ниже запрос:

USE Databasename
GO
BACKUP LOG [Databasename]
TO DISK = N'D:DatabasenameRDDTrLog. trn'
WITH NOFORMAT, NOINIT,
NAME = N'Databasename-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Чтобы восстановить потерянную информацию, необходимо собрать сведения об удаленных строках. Для этого запустите приведенный ниже запрос:

USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation  = 'LOP_DELETE_ROWS'

В результате работы запроса мы получим идентификатор транзакции (например, 000: 000001f3) для удаленных строк.

Определим конкретное время, когда строки были удалены. Для этого мы используем идентификатор транзакции 000: 000001f3. Выполните следующий запрос:

USE Databasename
GO
SELECT
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name],
[Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f3'
AND
[Operation] = 'LOP_BEGIN_XACT'

В результате выполнения этого запроса мы получим значение текущего порядкового номера журнала (LSN). Например, 00000020: 000001d0: 0001.

Теперь запустим процесс восстановления строк таблицы SQL Server. Для этого используем приведенный ниже запрос:

USE Databasename
GO
RESTORE DATABASE Databasename_COPY FROM
DISK = 'D:DatabasenameRDDFull.bak'
WITH
MOVE 'Databasename' TO 'D:RecoverDBDatabasename.mdf',
MOVE 'Databasename_log' TO 'D:RecoverDBDatabasename_log.ldf',
REPLACE, NORECOVERY;
GO

Теперь применим журнал транзакций для восстановления удаленных строк, используя LSN 00000020: 000001d0: 0001:

USE  Databasename
GO
RESTORE LOG Databasename_COPY FROM DISK = N'D:DatabasenameRDOTrLog.trn'
WITH STOPBEFOREMARK = ‘lsn:0x00000020:000001d0:0001'  
Note: Since LSN values are in Hexadecimal form and for restoring tables
using this LSN, we need to convert it into decimal form. For this purpose, 
we add 0x just before the LSN as shown above.

Процесс восстановления записей успешно завершен. Проверьте, восстановлены ли удаленные записи в базе данных с именем Databasename_Copy.

USE Databasename_Copy GO Select * from Table_name

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

Журнала транзакций поможет восстановить записи, удаленные из таблиц SQL. Но он сложен в реализации. Поэтому лучше использовать автоматизированные решения.

Данная публикация является переводом статьи «Recover Deleted Data From SQL Table Using Transaction Log and LSNs» , подготовленная редакцией проекта.

Журнал транзакций для базы данных заполнен MS SQL Server

У меня есть длительный процесс, который открывает транзакцию на полную длительность.

У меня нет контроля над тем, как это выполняется.

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

Таким образом, процесс выходит из строя с ошибкой "The transaction log for database 'xxx' is full" .

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

Не уверен, что я должен попробовать дальше. Процесс работает в течение нескольких часов, поэтому играть в пробную версию и ошибки непросто.

Есть идеи?

Если кому-то интересно, процесс представляет собой импорт организации в Microsoft Dynamics CRM 4.0.

Существует много дискового пространства, у нас есть журнал в режиме простого ведения журнала и резервное копирование журнала до начала процесса.

– = – = – = – = – UPDATE – = – = – = – = –

Спасибо всем за комментарии до сих пор. Следующее – это то, что привело меня к мысли, что журнал не будет расти из-за открытой транзакции:

Я получаю следующую ошибку …

 Import Organization (Name=xxx, Id=560d04e7-98ed-e211-9759-0050569d6d39) failed with Exception: System. Data.SqlClient.SqlException: The transaction log for database 'xxx' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases 

Поэтому после этого совета я пошел в log_reuse_wait_desc column in sys.databases « log_reuse_wait_desc column in sys.databases » и в нем было указано значение « ACTIVE_TRANSACTION ».

Согласно Microsoft: http://msdn.microsoft.com/en-us/library/ms345414(v=sql.105).aspx

Это означает следующее:

Транзакция активна (все модели восстановления). • Долгосрочная транзакция может существовать в начале резервного копирования журнала. В этом случае для освобождения пространства может потребоваться другая резервная копия журнала. Дополнительные сведения см. В разделе «Длительные активные транзакции» далее в этом разделе.

• Отложенная транзакция (только для SQL Server 2005 Enterprise Edition и более поздних версий). Отложенная транзакция – это фактически активная транзакция, откат которой заблокирован из-за некоторого недоступного ресурса. Для получения информации о причинах отложенных транзакций и способах их перемещения из состояния отложенного платежа см. Раздел Отложенные транзакции.

Я что-то неправильно понял?

– = – = – = – ОБНОВЛЕНИЕ 2 – = – = – = –

Просто начался процесс с первоначальным размером файла журнала, установленным в 30 ГБ. Это займет пару часов.

– = – = – = – Окончательное ОБНОВЛЕНИЕ – = – = – = –

Проблема была вызвана тем, что файл журнала потребляет все доступное дисковое пространство. В последней попытке я освободил 120 ГБ, и он все еще использовал все это и в конечном итоге потерпел неудачу.

Я не понимал, что это происходит раньше, потому что, когда процесс запускался всю ночь, он возвращался к отказу. На этот раз мне удалось проверить размер файла журнала перед откатом.

Спасибо всем за ваш вклад.

Это одноразовый скрипт или регулярная работа?

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

Чтобы устранить эту проблему, измените модель восстановления на « Simple», затем « Shrink Files Log»

1. Свойства базы данных> Параметры> Модель восстановления> Простой

2. Задачи базы данных> Усадка> Файлы> Журнал

Готово.

Затем проверьте размер файла журнала db в свойствах базы данных> Файлы> Файлы базы данных> Путь

Чтобы проверить полный журнал сервера sql: открыть средство просмотра файлов журнала в SSMS> База данных> Управление> Журналы SQL Server> Текущие

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

У вас есть Включить автозагрузку и неограниченный рост файлов, которые включены для файла журнала? Вы можете редактировать их через SSMS в разделе «Свойства базы данных> Файлы»

Это старый подход к школе, но если вы выполняете итеративное обновление или вставляете операцию в SQL, то, что работает в течение длительного времени, рекомендуется периодически (программно) называть «контрольную точку». Вызов «контрольной точки» заставляет SQL записывать на диск все эти изменения только для памяти (грязные страницы, они называются) и элементы, хранящиеся в журнале транзакций. Это приводит к периодической очистке журнала транзакций, что предотвращает проблемы, подобные описанным.

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

Это предотвратит любые действия в этой базе данных (например, сжимается), а SQL Server Database Engine вызовет ошибку 9002.

Чтобы преодолеть это поведение, я советую вам проверить это. Журнал транзакций для базы данных «SharePoint_Config» заполнен из-за LOG_BACKUP, который показывает подробные шаги для решения проблемы.

Следующее будет обрезать журнал.

 USE [yourdbname] GO -- TRUNCATE TRANSACTION LOG -- DBCC SHRINKFILE(yourdbname_log, 1) BACKUP LOG yourdbname WITH TRUNCATE_ONLY DBCC SHRINKFILE(yourdbname_log, 1) GO -- CHECK DATABASE HEALTH -- ALTER FUNCTION [dbo]. [checker]() RETURNS int AS BEGIN RETURN 0 END GO 

Журнал транзакций (SQL Server) — SQL Server

• 23.10.2019
• 11 минут на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии)

Каждая база данных SQL Server имеет журнал транзакций, в котором записываются все транзакции и модификации базы данных, сделанные каждой транзакцией.

Журнал транзакций — важный компонент базы данных. Если произойдет сбой системы, вам понадобится этот журнал, чтобы вернуть базу данных в согласованное состояние.

Для получения информации об архитектуре и внутреннем устройстве журнала транзакций см. Руководство по архитектуре и управлению журналом транзакций SQL Server.

Предупреждение

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

Подсказка

Известные хорошие точки, с которых можно начать применение журналов транзакций во время восстановления базы данных, создаются контрольными точками.Дополнительные сведения см. В разделе Контрольные точки базы данных (SQL Server).

Операции, поддерживаемые журналом транзакций

Журнал транзакций поддерживает следующие операции:

• Восстановление отдельной транзакции.
• Восстановление всех незавершенных транзакций при запуске SQL Server.
• Прокат восстановленной базы данных, файла, файловой группы или страницы до точки отказа.
• Поддержка репликации транзакций.
• Поддержка решений для обеспечения высокой доступности и аварийного восстановления: группы доступности AlwaysOn, зеркальное отображение базы данных и доставка журналов.

Восстановление отдельной транзакции

Если приложение выдает инструкцию ROLLBACK или компонент Database Engine обнаруживает ошибку, например потерю связи с клиентом, записи журнала используются для отката изменений, внесенных незавершенной транзакцией.

Восстановление всех незавершенных транзакций при запуске SQL Server

Если сервер выходит из строя, базы данных могут оставаться в состоянии, при котором некоторые изменения никогда не записывались из буферного кеша в файлы данных, и могут быть некоторые изменения из-за незавершенных транзакций в файлах данных.Когда экземпляр SQL Server запускается, он выполняет восстановление каждой базы данных. Каждое изменение, записанное в журнале, которое могло не быть записано в файлы данных, подвергается повтору транзакций. Каждая незавершенная транзакция, обнаруженная в журнале транзакций, затем откатывается, чтобы убедиться в сохранении целостности базы данных. Дополнительные сведения см. В разделе Восстановление и восстановление: Обзор (SQL Server).

Прокат восстановленной базы данных, файла, файловой группы или страницы до точки отказа

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

При восстановлении каждой резервной копии журнала компонент Database Engine повторно применяет все изменения, записанные в журнале, для наката всех транзакций. При восстановлении последней резервной копии журнала компонент Database Engine затем использует информацию журнала для отката всех транзакций, которые не были завершены на тот момент. Дополнительные сведения см. В разделе Восстановление и восстановление: Обзор (SQL Server).

Поддержка репликации транзакций

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

Поддержка решений высокой доступности и аварийного восстановления

Решения на основе резервного сервера, группы доступности AlwaysOn, зеркальное отображение базы данных и доставка журналов в значительной степени зависят от журнала транзакций.

В сценарии группы доступности AlwaysOn каждое обновление базы данных, первичной реплики, немедленно воспроизводится в отдельных полных копиях базы данных, вторичных репликах. Первичная реплика немедленно отправляет каждую запись журнала вторичным репликам, которые применяют входящие записи журнала к базам данных группы доступности, непрерывно повторяя транзакции. Для получения дополнительной информации см. Always On Failover Cluster Instances

В сценарии доставки журналов первичный сервер отправляет активный журнал транзакций первичной базы данных в одно или несколько мест назначения.Каждый вторичный сервер восстанавливает журнал в своей локальной вторичной базе данных. Для получения дополнительной информации см. О доставке журналов.

В сценарии зеркального отображения базы данных каждое обновление базы данных, основной базы данных, немедленно воспроизводится в отдельной полной копии базы данных, зеркальной базы данных. Экземпляр основного сервера немедленно отправляет каждую запись журнала экземпляру зеркального сервера, который применяет входящие записи журнала к зеркальной базе данных, непрерывно повторяя транзакцию.Дополнительные сведения см. В разделе «Зеркальное отображение базы данных».

Характеристики журнала транзакций

Характеристики журнала транзакций ядра СУБД SQL Server:

• Журнал транзакций реализован в виде отдельного файла или набора файлов в базе данных. Кэш журналов управляется отдельно от буферного кеша для страниц данных, что приводит к созданию простого, быстрого и надежного кода в ядре СУБД SQL Server. Для получения дополнительной информации см. Физическая архитектура журнала транзакций.

• Формат записей и страниц журнала не ограничивается форматом страниц данных.

• Журнал транзакций может быть реализован в нескольких файлах. Файлы можно настроить на автоматическое расширение, установив для журнала значение FILEGROWTH . Это снижает вероятность нехватки места в журнале транзакций и в то же время снижает административные издержки. Дополнительные сведения см. В разделе Параметры файла и файловой группы ALTER DATABASE (Transact-SQL).

• Механизм повторного использования пространства в файлах журнала работает быстро и минимально влияет на пропускную способность транзакций.

Для получения информации об архитектуре и внутреннем устройстве журнала транзакций см. Руководство по архитектуре и управлению журналом транзакций SQL Server.

Усечение журнала транзакций

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

Усечение журнала удаляет неактивные файлы виртуального журнала (VLF) из журнала логических транзакций базы данных SQL Server, освобождая место в логическом журнале для повторного использования физическим журналом транзакций. Если журнал транзакций никогда не усекается, он в конечном итоге заполнит все дисковое пространство, выделенное для физических файлов журнала.

Чтобы избежать нехватки места, если усечение журнала не задерживается по какой-либо причине, усечение происходит автоматически после следующих событий:

• По простой модели восстановления, после КПП.
• В модели полного восстановления или модели восстановления с неполным протоколированием, если контрольная точка возникла с момента предыдущего резервного копирования, усечение происходит после резервного копирования журнала (если это не резервная копия журнала только для копирования).

Дополнительные сведения см. В разделе «Факторы, которые могут задерживать усечение журнала» далее в этом разделе.

Примечание

Усечение журнала не уменьшает размер физического файла журнала. Чтобы уменьшить физический размер физического файла журнала, необходимо сжать файл журнала. Для получения информации об уменьшении размера физического файла журнала см. Управление размером файла журнала транзакций.
Однако следует помнить о факторах, которые могут задерживать усечение журнала. Если после сжатия журнала снова потребуется пространство для хранения, журнал транзакций снова вырастет, что приведет к снижению производительности во время операций увеличения журнала.

Факторы, которые могут задержать усечение журнала

Когда записи журнала остаются активными в течение длительного времени, усечение журнала транзакций задерживается, и журнал транзакций может заполняться, как мы упоминали ранее в этом длинном разделе.

На самом деле, усечение журнала может быть отложено по разным причинам. Узнайте, что мешает усечению журнала, запросив столбцы log_reuse_wait и log_reuse_wait_desc представления каталога sys.databases. В следующей таблице описаны значения этих столбцов.

значение log_reuse_wait	значение log_reuse_wait_desc	Описание
0	НИЧЕГО	В настоящее время существует один или несколько повторно используемых файлов виртуального журнала (VLF).
1	КОНТРОЛЬНАЯ ТОЧКА	С момента последнего усечения журнала контрольная точка не возникла, или заголовок журнала еще не вышел за пределы виртуального файла журнала (VLF). (Все модели восстановления) Это обычная причина задержки усечения журнала. Дополнительные сведения см. В разделе Контрольные точки базы данных (SQL Server).
2	LOG_BACKUP	Перед усечением журнала транзакций требуется резервная копия журнала. (Только для моделей восстановления с полным или неполным протоколированием) После завершения следующего резервного копирования журнала часть пространства журнала может стать повторно используемым.
3	ACTIVE_BACKUP_OR_RESTORE	Выполняется резервное копирование или восстановление данных (все модели восстановления). Если резервное копирование данных предотвращает усечение журнала, отмена операции резервного копирования может помочь решить немедленную проблему.
4	АКТИВНЫЙ_ПЕРЕВОД	Транзакция активна (все модели восстановления): Длительная транзакция могла существовать в начале резервного копирования журнала. В этом случае для освобождения места может потребоваться еще одна резервная копия журнала.Обратите внимание, что длительные транзакции предотвращают усечение журнала во всех моделях восстановления, включая простую модель восстановления, при которой журнал транзакций обычно усекается на каждой автоматической контрольной точке. Транзакция отложена. Отложенная транзакция фактически является активной транзакцией, откат которой заблокирован из-за недоступности ресурса. Для получения информации о причинах отложенных транзакций и о том, как вывести их из отложенного состояния, см. Отложенные транзакции (SQL Server). Длительные транзакции также могут заполнять журнал транзакций tempdb. Tempdb неявно используется пользовательскими транзакциями для внутренних объектов, таких как рабочие таблицы для сортировки, рабочие файлы для хеширования, рабочие таблицы курсора и управление версиями строк. Даже если пользовательская транзакция включает в себя только чтение данных ( SELECT запросов), внутренние объекты могут быть созданы и использованы в пользовательских транзакциях. Затем можно заполнить журнал транзакций tempdb.
5	БАЗА ДАННЫХ_ЗЕРКАЛЬНОЕ ЗЕРКАЛО	Зеркальное отображение базы данных приостановлено или в режиме высокой производительности зеркальная база данных значительно отстает от основной базы данных.(Только для модели полного восстановления) Дополнительные сведения см. В разделе «Зеркальное отображение базы данных (SQL Server)».
6	РЕПЛИКАЦИЯ	Во время репликации транзакций транзакции, относящиеся к публикациям, все еще не доставляются в базу данных распространителя. (Только для модели полного восстановления) Для получения информации о репликации транзакций см. Репликация SQL Server.
7	DATABASE_SNAPSHOT_CREATION	Создается моментальный снимок базы данных.(Все модели восстановления) Это обычная и обычно кратковременная причина отложенного усечения журнала.
8	LOG_SCAN	Происходит сканирование журнала. (Все модели восстановления) Это обычная и обычно кратковременная причина отложенного усечения журнала.
9	AVAILABILITY_REPLICA	Вторичная реплика группы доступности применяет записи журнала транзакций этой базы данных к соответствующей вторичной базе данных.(Модель полного восстановления) Дополнительные сведения см. В разделе Обзор групп доступности AlwaysOn (SQL Server).
10	–	Только для внутреннего использования
11	–	Только для внутреннего использования
12	–	Только для внутреннего использования
13	OLDEST_PAGE	Если база данных настроена на использование косвенных контрольных точек, самая старая страница в базе данных может быть старше, чем порядковый номер журнала контрольной точки (LSN).В этом случае самая старая страница может задерживать усечение журнала. (Все модели восстановления) Для получения информации о косвенных контрольных точках см. Контрольные точки базы данных (SQL Server).
14	OTHER_TRANSIENT	Это значение в настоящее время не используется.
16	XTP_CHECKPOINT	Необходимо выполнить контрольную точку OLTP в памяти. Для таблиц, оптимизированных для памяти, автоматическая контрольная точка берется, когда файл журнала транзакций становится больше 1.5 ГБ с момента последней контрольной точки (включает как дисковые, так и оптимизированные для памяти таблицы) Дополнительные сведения см. В разделах «Операция контрольной точки для таблиц, оптимизированных для памяти» и [Процесс ведения журнала и контрольной точки для таблиц, оптимизированных в памяти] (https: // blogs. msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

Операции, которые можно минимально протоколировать

Минимальное ведение журнала включает в себя регистрацию только той информации, которая требуется для восстановления транзакции без поддержки восстановления на определенный момент времени.В этом разделе описываются операции, которые минимально регистрируются в модели восстановления с неполным протоколированием (а также в простой модели восстановления, за исключением случаев, когда выполняется резервное копирование).

Примечание

Минимальное ведение журнала не поддерживается для таблиц, оптимизированных для памяти.

Примечание

В модели полного восстановления все массовые операции полностью регистрируются. Однако вы можете минимизировать ведение журнала для набора массовых операций, временно переключив базу данных на модель восстановления с неполным протоколированием для массовых операций.Минимальное ведение журнала более эффективно, чем полное ведение журнала, и оно снижает вероятность крупномасштабной массовой операции, заполняющей доступное пространство журнала транзакций во время массовой транзакции. Однако, если база данных повреждена или утеряна при минимальном ведении журнала, вы не сможете восстановить базу данных до точки отказа.

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

Когда включена репликация транзакций, операции 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
Контроль надежности транзакций
Предварительные требования для минимального ведения журнала при массовом импорте
Резервное копирование и восстановление баз данных SQL Server
Обзор восстановления и восстановления (SQL Server)
Контрольные точки базы данных (SQL Server)
Просмотр или изменение Свойства базы данных
Recovery Models (SQL Server)
Transaction Log Backups (SQL Server)
sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL)

Управление журналами транзакций в SQL Server

Каждая база данных в экземпляре SQL Server имеет журнал, в котором записываются все изменения базы данных. Поскольку этот журнал записывается независимо, до внесения изменений журнал транзакций позволяет базе данных откатывать или восстанавливать транзакции в случае отказа оборудования или ошибки приложения. Из-за важности своей роли журнал транзакций хранится в одном или нескольких файлах журнала, которые отделены от файлов данных; записи журнала записываются в журнал транзакций до того, как измененное содержимое буферного кэша записывается в файлы данных.

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

• Откат отдельных транзакций, если выдается инструкция ROLLBACK или ядро ​​базы данных обнаруживает ошибку.
• Откат незавершенных транзакций, возникших в результате сбоя сервера. Откат транзакций выполняется при перезапуске SQL Server.
• Восстановление незавершенных транзакций, записанных в журналы, но не в файлы данных в результате сбоя сервера.Транзакции записываются в файлы данных при перезапуске SQL Server.
• Прокат восстановленной базы данных, файловой группы, файла или страницы до точки отказа в случае отказа оборудования. Повтор транзакций выполняется после применения последних полных и дифференциальных резервных копий.
• Поддержка репликации транзакций, зеркального отображения базы данных и доставки журналов.

Файл (или файлы, если используется более одного файла), составляющий журнал транзакций, делится на файлы виртуального журнала, размер которых вместе с их количеством в физическом журнале определяется ядром базы данных.Механизм базы данных также решает, когда и какие виртуальные файлы будут усечены. Однако вы можете указать минимальный и максимальный размеры физического журнала, а также настроить приращение роста, используемое при расширении этого файла. Кроме того, вы можете добавлять в журнал физические файлы, удалять файлы, увеличивать размер журнала и уменьшать его.

В этой статье я объясню, как выполнять эти задачи, чтобы вы могли начать управлять своими журналами транзакций, и я привожу примеры, демонстрирующие, как работает каждая задача.В этих примерах я использовал следующий код для создания базы данных EmployeeDB на локальном экземпляре SQL Server 2008:

Обратите внимание, что я создал файлы базы данных не в том месте, которое используется по умолчанию в SQL Server.Если вы запустите этот код, вы сможете найти файлы базы данных в любом удобном для вас месте. После создания базы данных я использовал следующий оператор SELECT… INTO для извлечения данных из базы данных AdventureWorks2008 и создания таблицы «Сотрудники»:

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

Каждая база данных SQL Server включает свойство модели восстановления, которое определяет способ регистрации транзакций, возможность резервного копирования журнала транзакций и тип разрешенных операций восстановления. По умолчанию новая база данных наследует модель восстановления из базы данных model . Однако вы можете изменить настройку по умолчанию, назначив другую модель восстановления.

Вы можете настроить базу данных SQL Server с любой из следующих трех моделей восстановления.

• Простой: В этой модели резервное копирование журнала транзакций не разрешено, что означает, что у вас нет административных издержек, связанных с поддержанием резервных копий журнала транзакций. Модель также автоматически освобождает пространство журнала, поэтому почти не нужно управлять пространством журнала транзакций. Однако это также самая рискованная из моделей — базу данных можно восстановить только до момента ее последней резервной копии.Транзакции, выполненные с момента последнего резервного копирования, теряются. Эта модель обычно используется для системных баз данных, а также для тестирования и разработки, хотя иногда она подходит для базы данных только для чтения, такой как хранилище данных. В этой модели некоторые операции регистрируются минимально.
• Полный: Для файлов журналов можно и нужно создавать резервные копии, поскольку они обеспечивают полное восстановление до определенного момента времени. Однако эта модель менее рискованна, чем простая модель.В модели полного восстановления все операции полностью регистрируются, включая операции массового импорта. Модель полного восстановления обычно используется для производственных сред.
• Bulk Logged: Эта модель предназначена в качестве дополнения к модели полного восстановления, поскольку такие операции, как массовый импорт, регистрируются только минимально. Например, вы можете захотеть выполнить массовую загрузку данных и не беспокоиться о регистрации этих транзакций, потому что при необходимости вы можете перезагрузить данные. В таких случаях вы можете установить модель восстановления на Bulk Logged при импорте данных, а затем снова изменить настройку на Full, когда вы закончите.(Обратите внимание, что вам следует выполнить полное резервное копирование после того, как вы снова измените настройку на «Полное».)

Вы можете переключить модель восстановления в базе данных, запустив инструкцию ALTER DATABASE и указав предложение SET RECOVERY, как показано в следующем примере:

Как видите, я изменяю базу данных EmployeeDB и устанавливаю модель восстановления FULL.Однако обратите внимание, что по умолчанию для базы данных модели настроена модель полного восстановления, что означает, что для EmployeeDB была автоматически настроена модель полного восстановления, поскольку она унаследовала параметр из базы данных модели . В результате, если модель восстановления по умолчанию не была изменена в базе данных model вашего экземпляра SQL Server, приведенный выше пример ALTER DATABASE не изменил настройку. Однако следует также отметить, что при переключении базы данных с простой модели на полную модель иногда необходимо предпринимать другие шаги, например, создание полной резервной копии базы данных.В разделе «Рекомендации по переходу от простой модели восстановления» в электронной документации по SQL Server описываются шаги, которые могут потребоваться при переключении с простой модели на полную или с групповым протоколированием.

Вы также можете установить модель восстановления в SQL Server Management Studio. В обозревателе объектов щелкните правой кнопкой мыши имя базы данных и выберите Свойства . В диалоговом окне Свойства базы данных щелкните страницу Параметры и затем установите свойство Модель восстановления .

При ведении журнала транзакций базы данных вам часто требуется получить информацию о журнале, чтобы вы могли проверить его настройки или отслеживать, сколько места используется для журнала. Один из способов найти информацию о журнале — использовать представление каталога sys.database_files. Представление возвращает сведения о файлах базы данных, включая тип файла, текущий размер файла и допустимый размер файла для увеличения.

В следующем примере я использую представление каталога sys.database_files для получения данных о файле журнала, связанном с базой данных EmployeeDB:

Оператор возвращает текущий размер файла (в страницах по 8 КБ), максимальный размер, разрешенный для роста файла (также в страницах по 8 КБ), скорость роста и флаг is_percent_growth, который определяет, как скорость роста следует интерпретировать. Если флаг установлен в 0, скорость роста равна количеству страниц размером 8 КБ. Если флаг установлен на 1, скорость роста выражается в процентах.

Вышеупомянутый оператор SELECT возвращает следующие результаты:

Как показывают результаты, оператор возвращает только одну строку.Это потому, что с базой данных EmployeeDB связан только один файл журнала. Результаты также показывают, что текущий размер файла EmployeeDB_log составляет 128 страниц по 8 КБ. Однако файл может вырасти до 268 435 456 страниц размером 8 КБ с приращением 10%.

Вы также можете использовать инструкцию DBCC SQLPERF для возврата информации о журналах транзакций для каждой базы данных в экземпляре SQL Server. Чтобы получить данные журнала, вы должны указать ключевое слово LOGSPACE в круглых скобках, как показано в следующем примере:

Оператор возвращает размер журнала в МБ, процент используемого пространства журнала и состояние журнала для каждой базы данных в экземпляре SQL Server.Следующие результаты показывают информацию, возвращаемую инструкцией DBCC SQLPERF для базы данных EmployeeDB:

В этом случае размер журнала EmployeeDB составляет около 1 МБ, и используется около 40% пространства журнала.

В SQL Server Management Studio можно также создать отчет, который графически отображает данные, аналогичные результатам инструкции DBCC SQLPERF.Чтобы создать отчет, щелкните правой кнопкой мыши имя базы данных в обозревателе объектов, затем укажите на отчеты , затем укажите на Стандартные отчеты и, наконец, щелкните Использование диска .

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

Прежде чем можно будет создать резервную копию файла журнала, необходимо выполнить полное резервное копирование базы данных. Например, прежде чем я создам резервную копию файла журнала, который я использую для примеров в этой статье, я выполню следующий оператор BACKUP DATABASE в базе данных EmployeeDB:

Обратите внимание: если вы запустите этот код, убедитесь, что расположение НА ДИСКЕ существует, или укажите другое расположение.

После того, как я создал резервную копию базы данных, я выполнил следующие операторы изменения данных, чтобы журнал содержал транзакции, не включенные в резервную копию:

Затем я повторно выполняю инструкцию DBCC SQLPERF, чтобы просмотреть объем используемого пространства журнала.Заявление вернуло следующие результаты:

Как видите, процент используемого пространства журнала увеличился примерно с 40% до почти 65%.

После резервного копирования базы данных вы можете создать резервную копию журнала транзакций.Чтобы выполнить резервное копирование журнала транзакций, используйте оператор BACKUP LOG и укажите целевое назначение для файлов резервных копий, как показано в следующем примере:

Обратите внимание: если вы запустите этот код, убедитесь, что расположение НА ДИСКЕ существует, или укажите другое расположение.

Обратите внимание, что я включил предложение TO DISK, чтобы указать место назначения файла. Однако оператор BACKUP поддерживает другие варианты резервного копирования данных. Дополнительную информацию смотрите в разделе «РЕЗЕРВНОЕ КОПИРОВАНИЕ (Transact-SQL)» в электронной документации по SQL Server.

После резервного копирования журнала транзакций ядро ​​базы данных SQL Server автоматически усекает неактивное пространство журнала. (Усечение файла журнала удаляет неактивные виртуальные файлы журнала, но не уменьшает размер файла. Кроме того, вы не можете специально усечь журнал.Однако вы можете сжать файл, что уменьшит его размер. Я объясню, как уменьшить файл журнала позже в статье). Чтобы проверить, усечен ли журнал, снова запустите инструкцию DBCC SQLPERF. На этот раз результаты должны быть примерно такими:

Теперь процент используемого пространства журналов снова снизился примерно на 45%.

Вы можете использовать оператор ALTER DATABASE для изменения файла журнала. Вы должны указать предложение MODIFY FILE вместе с соответствующими параметрами. Помимо указания логического имени файла журнала, вы можете определить следующие три аргумента:

• РАЗМЕР: Новый размер файла журнала. Вы можете указать размер как КБ, МБ, ГБ или ТБ, например 10 МБ или 1 ГБ. Если вы не укажете размер при добавлении файла, ядро ​​базы данных будет использовать размер по умолчанию 1 МБ.Новый размер должен быть больше текущего, иначе вы получите сообщение об ошибке при выполнении инструкции.
• MAXSIZE: Максимальный размер, до которого может вырасти файл. Вы можете указать размер как КБ, МБ, ГБ или ТБ. Если вы не укажете максимальный размер, файл будет расти до тех пор, пока не заполнит диск (при условии, что пространство необходимо).
• FILEGROWTH: Приращение роста, используемое при расширении файла. Вы можете указать размер как КБ, МБ, ГБ или ТБ, или вы можете указать размер в процентах, например 10%.Если номер указан без суффикса, используется МБ. Если число не указано, используется 10%. Значение 0 указывает, что автоматический рост не разрешен.

Следующий оператор ALTER DATABASE изменяет файл EmployeeDB_log в базе данных EmployeeDB:

Как показано в инструкции, после того, как я укажу логическое имя файла журнала, я устанавливаю новый размер для файла (2 МБ), максимальный размер (200 МБ) и приращение роста (10 МБ).

После выполнения оператора ALTER DATABASE вы можете запросить представление каталога sys.database_files, чтобы проверить изменения. Ваши результаты должны быть примерно такими:

Размер файла теперь составляет 256 страниц по 8 КБ, максимальный размер — 25 600 страниц по 8 КБ, а шаг увеличения — 1 280 страниц по 8 КБ.

Как вы помните, для усечения журнала транзакций необходимо сначала создать резервную копию журнала. Затем ядро ​​базы данных автоматически обрезает неактивные записи. Однако усечение журнала не уменьшает его размер. Вместо этого вы должны сжать файл журнала, что приведет к удалению одного или нескольких неактивных виртуальных файлов журнала.

Чтобы сжать файл журнала, можно запустить инструкцию DBCC SHRINKFILE, которая указывает имя файла журнала и целевой размер в МБ. Например, следующий оператор DBCC SHRINKFILE сжимает файл EmployeeDB_log:

Целевой размер в этом заявлении — 1 МБ (128 страниц по 8 КБ).Когда вы запускаете оператор, ядро ​​базы данных сжимает файл до этого размера, но только если имеется достаточно неактивных файлов виртуального журнала.

После выполнения оператора вы можете проверить степень уменьшения файла, запросив представление каталога sys.database_files, которое должно вернуть результаты, подобные следующим:

Как видите, размер уменьшен с 256 страниц по 8 КБ до 128.Если ядро ​​базы данных не может освободить место, оно выдает сообщение, предлагающее шаги, которые вы можете предпринять, чтобы освободить место в журнале. Выполните предложенные шаги, а затем повторно запустите инструкцию DBCC SHRINKFILE.

Если вам нужно увеличить журнал транзакций, вы можете использовать один из способов — добавить файл в журнал.

Это можно сделать с помощью предложения ADD LOG FILE оператора ALTER DATABASE. Помимо указания логических и физических имен нового файла журнала, вы можете определить следующие три аргумента:

• РАЗМЕР: Начальный размер файла журнала.Вы можете указать размер как КБ, МБ, ГБ или ТБ, например 10 МБ или 1 ГБ. Если вы не укажете размер при добавлении файла, ядро ​​базы данных будет использовать размер по умолчанию 1 МБ.
• MAXSIZE: Максимальный размер, до которого может вырасти файл. Вы можете указать размер как КБ, МБ, ГБ или ТБ. Если вы не укажете максимальный размер, файл будет расти до тех пор, пока не заполнит диск (при условии, что пространство необходимо).
• FILEGROWTH: Приращение роста, используемое при расширении файла.Вы можете указать размер как КБ, МБ, ГБ или ТБ, или вы можете указать размер в процентах, например 10%. Если номер указан без суффикса, используется МБ. Если число не указано, используется 10%. Значение 0 указывает, что автоматический рост не разрешен.

В следующем примере файл EmployeeDB_log2 добавляется в журнал транзакций EmployeeDB:

Обратите внимание, что я сначала указываю логические и физические имена файлов, а затем определяю начальный размер, максимальный размер и приращение роста. После запуска этого оператора я могу подтвердить, что файл был добавлен в журнал, запросив представление каталога sys.database_files (используя тот же запрос, который я использовал ранее), который возвращает следующие результаты:

Как показывают результаты, файл EmployeeDB_log2 был добавлен в базу данных с начальным размером 256 страниц по 8 КБ, максимальным размером 6 400 страниц по 8 КБ и с шагом роста 10%.

Вы также можете использовать оператор ALTER DATABASE для удаления файла журнала, указав предложение REMOVE FILE, как показано в следующем примере:

Чтобы определить, был ли удален файл, вы можете еще раз запросить представление каталога sys.database_files, которое вернет следующие результаты:

Обратите внимание, что файл EmployeeDB_log2 все еще присутствует в списке, но его размер установлен на 1 страницу размером 8 КБ.Физический файл был удален, но логический файл все еще связан с базой данных. Перед удалением логического файла необходимо создать резервную копию журнала транзакций. После резервного копирования журнала можно снова запросить представление каталога sys.database_files. На этот раз ваши результаты должны выглядеть примерно так:

Как видите, логический файл удален.

Очевидно, что журналы транзакций играют важную роль в базах данных SQL Server, и приведенная выше информация должна дать вам представление о том, как с ними работать. Однако я не рассмотрел способы использования журнала транзакций для поддержки репликации транзакций, зеркального отображения базы данных и доставки журналов. Я также не рассмотрел, как использовать журнал транзакций и его резервные копии для восстановления базы данных. Каждая из этих тем заслуживает отдельной статьи. Но у вас, по крайней мере, теперь должно быть базовое основание для журналов транзакций и у вас должна быть возможность начать с ними работать.Однако я настоятельно рекомендую вам ознакомиться с различными темами в электронной документации по SQL Server, посвященными журналам транзакций, а также с другими источниками по этой теме, чтобы иметь полное представление о том, как работают журналы и как ими лучше всего управлять.

Резервное копирование журналов транзакций каждую минуту. Да, действительно.

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

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

Вероятно, вам придется повторить этот вопрос пару раз, потому что они не поверят, что эти слова срываются с ваших уст. После того, как они в конце концов выздоровеют, они ляпнут такие слова, как «NONE», «ZERO» и «ZOMG». Кратко расскажите им, сколько стоит система без потери данных — обычно это миллионы долларов, — а затем попросите другой ответ.

Они собираются попросить список вариантов и их стоимость.Забудьте пока о мелких деталях — давайте просто предположим, что одно из ваших решений включает резервное копирование журналов транзакций.

Большая рука находится на сетевом кабеле, а маленькая — на кабеле питания.

Резервное копирование журналов дороже:

• A — Каждый час
• B — Каждые 15 минут
• C — Каждые 5 минут
• D — Каждую минуту

Вопрос с подвохом — все они стоят одинаково.

«НО БОЛЬШЕ РЕЗЕРВНЫХ КОПИЙ ЖУРНАЛА ЗНАЧИТ БОЛЬШЕ ЗАМЕДЛЕНИЙ!»

Нет, это означает меньше накладных расходов.Для простоты вычислений предположим, что вы генерируете 60 ГБ данных журнала транзакций в час. Вы бы предпочли выполнять резервное копирование 1 ГБ в минуту небольшими порциями или выгружать сервер в мусорную корзину каждый час, пытаясь создать резервную копию всех 60 ГБ за раз? Пользователи обязательно заметят последнее — сервер будет медленно сканировать, пока он обрабатывает этот шланг данных журнала в течение длительного периода.

«НО БОЛЬШЕ РЕЗЕРВНЫХ КОПИЙ ЖУРНАЛА ЗНАЧИТ БОЛЬШЕ ФАЙЛОВ!»

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

«НО ЗАДАНИЯ ДЛЯ РЕЗЕРВНОГО КОПИРОВАНИЯ ЖУРНАЛА НЕ МОГУТ ЗАВЕРШИТЬСЯ ЗА МИНУТУ!»

Если у вас слишком много баз данных и ваши задания не успевают, самое время начать разбивать резервные копии журналов на несколько заданий. Некоторые из моих клиентов, например, используют два задания — одно для резервного копирования всех нечетных баз данных по идентификатору базы данных в sys.базы данных и еще один для резервного копирования всех событий. Резервные копии журналов никоим образом не нагружают их серверы, и пользователи не замечают влияния двух одновременных резервных копий журналов в двух разных базах данных.

«НО ВЫ НЕ МОЖЕТЕ БЫТЬ СЕРЬЕЗНЫМ!»

Я так же серьезно, как и вы, когда вы случайно выбрали 15 минут или 1 час в качестве расписания резервного копирования журнала. Дело в том, что решать не компьютерные фанаты, а заказчик. Спросите своего бизнес-пользователя о бизнес-потребностях их данных, а затем предложите им меню вариантов для достижения этой цели.

Вы думаете, что все они будут настаивать на том, что все данные критически важны, но вы удивитесь. Как только они увидят затраты, связанные с доставкой журналов, группами доступности AlwaysOn, репликацией SAN и т. Д., Они смогут принимать более обоснованные решения о том, какие данные действительно бесценны, а какие данные мы могли бы повторно ввести с минимальными затратами.

Но пока вы не зададите им этот вопрос, они предполагают, что вы никогда не потеряете данные.

Как очистить журнал транзакций SQL Server с помощью DBCC SHRINKFILE

Журнал транзакций — это файл, содержащий все записи транзакций и изменения базы данных, сделанные каждой транзакцией в базе данных SQL Server.Файл журнала играет очень важную роль в базе данных SQL Server, когда дело доходит до аварийного восстановления, и он не должен находиться в поврежденном состоянии. В случае аварии мы можем восстановить базу данных с помощью информации, содержащейся в журнале транзакций SQL Server. Журнал транзакций следует регулярно обрезать или очищать, чтобы размер файла журнала не увеличивался. На этой странице будет обсуждаться, как очистить журнал транзакций SQL Server.

Зачем очищать журнал транзакций SQL Server?

Во время работы SQL Server журнал транзакций увеличивается, если происходят какие-либо изменения в базе данных.Регулярное управление размером журнала транзакций необходимо для предотвращения переполнения журнала транзакций. Для предотвращения заполнения журнала требуется усечение журнала или очистка журнала транзакций SQL Server. Процесс усечения удаляет неактивные файлы виртуального журнала из логического журнала транзакций, освобождая пространство для повторного использования физическим журналом транзакций. Журнал транзакций в конечном итоге заполнит все дисковое пространство, выделенное для его физических файлов журнала, если оно никогда не будет усечено.

В SQL Server есть три модели восстановления.В зависимости от того, какой из них используется, процесс усечения различается:

Модель простого восстановления Резервные копии журнала транзакций не поддерживаются, процесс усечения выполняется автоматически, и пространство доступно для использования.

Модель восстановления с неполным протоколированием Нет автоматического усечения журнала, необходимо регулярно создавать резервные копии, чтобы пометить неиспользуемое пространство и сделать его доступным для перезаписи. Размер журнала можно уменьшить, используя минимальное ведение журнала для массовых операций.

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

Пространство журнала транзакций можно контролировать с помощью команды:

Как очистить журнал транзакций SQL Server?

Усечение журнала освобождает место в файле журнала для повторного использования. Поэтому это также называется очисткой журнала. Файл журнала транзакций логически разделен на небольшие части, называемые файлами виртуального журнала (VLF). Каждый файл VLF — это модуль, который можно пометить как доступный для повторного использования (бесплатно) или как недоступный для повторного использования (используемый).VLF помечается как «активный», если он используется, и «неактивный», если он свободен. Очистить журнал транзакций SQL Server означает поиск и создание VLF как бесплатных.

Примечание: Следует иметь в виду, что усечение журнала не предназначено для уменьшения размера физического файла журнала. Сжатие журнала необходимо для уменьшения физического файла журнала.

Очистка журнала транзакций SQL Server состоит из двух шагов. Во-первых, нам нужно выполнить резервное копирование журнала с параметром TRUNCATE_ONLY, а следующим шагом будет использование функции DBCC SHRINKFILE для сжатия файла до необходимого размера.

РЕЗЕРВНОЕ КОПИРОВАНИЕ ЖУРНАЛА TRUNCATE_ONLY — не лучший вариант, поскольку он очищает все содержимое нашего журнала транзакций без его резервного копирования. Многие люди используют эту команду перед сжатием файла журнала с помощью DBCC SHRINKFILE, освобождающего место на диске. TRUNCATE_ONLY недоступен в более поздних версиях SQL Server.

Вместо усечения журналов транзакций мы можем использовать простой режим восстановления, в котором мы не создаем журналы, которые мы бы не использовали.

У нас есть два варианта сжатия журнала.Их:

Сжать файл журнала SQL Server с помощью SQL Server Management Studio

Щелкните правой кнопкой мыши базу данных и выберите: Задачи, затем Сжать, а затем выберите Файлы:

Измените тип файла на журнал.

Сжать файл журнала транзакций SQL с использованием T-SQL

Для простого восстановления будет использоваться следующая команда:

DBCC SHRINKFILE (имя файла журнала, желаемый размер в МБ)

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

 ИЗМЕНИТЬ имя базы данныхDB
УСТАНОВИТЕ ВОССТАНОВЛЕНИЕ ПРОСТО
ИДТИ
DBCC SHRINKFILE (имя файла журнала, желаемый размер в МБ)
ИДТИ
ИЗМЕНИТЬ имя базы данныхDB
УСТАНОВИТЬ ВОССТАНОВЛЕНИЕ ПОЛНОЕ
 

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

 имя журнала резервного копирования DB TO BackupDevice 

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

Усечение журнала транзакций SQL Server

Как обрезать журналы SQL?

С одной стороны, вам необходимо вести журналы транзакций, чтобы вы могли восстановить данные SQL Server в случае какого-либо удаления, нежелательного изменения или повреждения данных.С другой стороны, вам необходимо удалить журналы транзакций для экономии места, но без каких-либо записей транзакций вы не сможете успешно восстановить их в случае возникновения непредсказуемой ситуации.

Лучше всего сначала создать резервную копию всей виртуальной машины VMware или Hyper-V, на которой работает Microsoft SQL Server, и всех файлов журналов, хранящихся в ней, а затем удалить (или усечь) эти файлы на исходной виртуальной машине, освободив место для хранения.

Упрощение усечения журнала транзакций до 1-2-3

NAKIVO Backup & Replication поддерживает усечение журнала транзакций для Microsoft SQL Server 2008 и более поздних версий.Продукт следует передовой практике выполнения процесса усечения журнала, обеспечивая при этом простоту использования и простоту. NAKIVO Backup & Replication может автоматически обрезать файлы журнала транзакций после успешного резервного копирования и репликации виртуальной машины. Все, что вам нужно сделать, это просто установить его и забыть.

Чтобы освободить место для хранения виртуальной машины, NAKIVO Backup & Replication выполняет следующие операции:

• Резервное копирование / репликация всей виртуальной машины VMware или Hyper-V под управлением Microsoft SQL Server
• После успешного завершения резервного копирования / репликации определяет файлы журнала транзакций SQL Server, которые уже были зафиксированы в базе данных.
• Обрезает (удаляет) файлы журнала зафиксированных транзакций на исходной виртуальной машине, освобождая таким образом место для хранения

Что вы получаете

Следовательно, вы получаете резервную копию / реплику ВМ со всеми файлами журнала транзакций.Несмотря на то, что файлы журналов резервного копирования могут быть довольно большими, NAKIVO Backup & Replication легко уменьшает размер резервной копии виртуальной машины с помощью функций дедупликации и сжатия резервных копий. В свою очередь, исходная виртуальная машина остается без журналов и может быть восстановлена ​​в определенной точке восстановления с использованием вышеупомянутого файла / реплики резервной копии виртуальной машины, если что-то пойдет не так.

Экономия в реальном времени

NAKIVO Backup & Replication — это первоклассное решение, которое снимает с вас бремя управления журналами транзакций.NAKIVO Backup & Replication может автоматически обрезать файлы журналов, освобождая место на вашей виртуальной машине без вашего участия и экономя много времени и усилий.

Загрузите полнофункциональную бесплатную пробную версию и посмотрите, как она вам подходит!

Восстановить потерянные или удаленные файлы журнала транзакций

Администрирование SQL Server: Время от времени кто-то звонит с ужасной проблемой, потому что диск был потерян или кто-то по ошибке удалил файлы журнала транзакций.В этом случае затронутые базы данных становятся непригодными для использования и обычно помечаются как ПОДОЗРЕВАЕМЫЕ или В ВОССТАНОВЛЕНИИ в Management Studio. Обычно лучшим вариантом является восстановление базы данных из резервной копии, однако иногда файлы резервных копий могут быть потеряны или просто не настроены и, следовательно, не существуют. Если файлы журнала транзакций не могут быть восстановлены и резервная копия базы данных недоступна, последний вариант — заставить SQL Server восстановить потерянные файлы журнала транзакций. Этот вариант должен рассматриваться в последнюю очередь. , но есть случаи, когда это оправдано.Поскольку файл журнала транзакций создается как совершенно новый файл, любые транзакции, которые были в старом файле журнала транзакций, будут потеряны, поэтому возможно повреждение данных. Очевидно, вам потребуется создать папки для журналов транзакций на диске с достаточным пространством для хранения новых файлов журналов.

Принудительное создание нового журнала транзакций

Используйте этот оператор, чтобы получить имена для замены в соответствующих местах в операторах ALTER DATABASE, которые будут следовать:

выберите d.имя как dbname, m.name как логическое имя, m.physical_name

из sys.master_files m

присоединиться к sys.databases d на (d.database_id = m.database_id)

, где m.type = 1 — Лог

заказать по 1, 2

Вы можете использовать фактическое имя файла из столбца Physical_name в значении FILENAME ниже. Используйте новый путь к папке в качестве пути в значении FILENAME. Выполняйте каждый оператор один за другим и особенно обратите внимание на то, что возвращает вывод CHECKDB, если есть ошибки.Если он говорит, что вам нужно запустить REPAIR_ALLOW_DATA_LOSS, раскомментируйте команду и запустите ее. Когда вы не получаете ошибок из DBCC CHECKDB, вы можете поместить базу данных в MULTI_USER и затем использовать базу данных.

Мастер использования

GO

ALTER DATABASE [dbname] SET EMERGENCY

GO

ALTER DATABASE [dbname] SET SINGLE_USER

GO

ALTER DATABASE [dbname] REBUILD LOG ON

(NAME = логическое имя, FILENAME = ’Путь к новому расположению файла \ Имя файла журнала.ldf ») — Например,« E: \ MSSQL \ LOGS \ DB_Log.ldf »

GO

DBCC CHECKDB ([имя_бд])

GO

— DBCC CHECKDB ([dbname], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

— GO

ALTER DATABASE [dbname] SET MULTI_USER

GO

для SQL Server

SQL являются сердцем и душой ваших систем ERP и CRM, а также многих других критически важных бизнес-приложений.Их простои и потерянные данные означают финансовые потери в часах работы сотрудников, упущенных сделках и прибылях. Чем больше данных вы хотите сохранить в случае простоя, тем больше у вас причин для создания резервных копий журнала транзакций SQL. Они максимально увеличивают доступность SQL Server, позволяя восстанавливать базу данных в любой момент времени.

Вы можете создавать резервные копии журналов транзакций SQL с помощью собственных средств Microsoft или сторонних решений. Собственные инструменты включают SQL Server Management Studio, Transact-SQL (или T-SQL), задания агента SQL Server, планы обслуживания SQL Server и сценарии PowerShell.Для получения дополнительных сведений об использовании этих параметров я предлагаю вам ознакомиться с Microsoft
TechNet и MSDN.

На этот раз остановимся на Veeam. Veeam Backup & Replication (входит в состав Veeam Availability Suite) может позаботиться о ваших журналах транзакций SQL, а также обо всем вашем сервере SQL. Он обеспечивает обработку изображений с учетом требований приложений для согласованного резервного копирования SQL Server и поддерживает резервное копирование и усечение журналов транзакций. Для виртуализированных SQL Server 2012 и 2014 Veeam также поддерживает группы доступности AlwaysOn.

Свойства вашей базы данных должны поддерживать ведение журнала транзакций. SQL Server контролирует ведение журнала транзакций с помощью моделей восстановления. Модель простого восстановления не требует журналов транзакций и не позволяет выполнять восстановление на определенный момент времени. Veeam Backup & Replication по умолчанию исключает базы данных простого режима из заданий обработки журналов. Только базы данных в моделях восстановления с полным или неполным протоколированием имеют свои изменения, записанные в файл журнала транзакций SQL, что делает возможным восстановление до определенного момента времени.Убедитесь, что ваша база данных находится в режиме восстановления с полным или неполным протоколированием.

В Veeam Backup & Replication задания резервного копирования журнала транзакций являются подзадачей задания резервного копирования виртуальной машины SQL Server. Итак, сначала создайте и настройте задание резервного копирования для вашей виртуальной машины SQL Server. Чтобы создать резервную копию SQL Server с учетом транзакций, включите обработку изображений с учетом требований приложений.

Щелкните Приложения , чтобы настроить параметры обработки SQL.

В настройках Редактировать выберите Обрабатывать журналы транзакций с этим заданием .

Файлы журналов увеличиваются в размере вместе с загрузкой базы данных, поэтому для управления размером файла журнала (.LDF) требуется регулярное резервное копирование журнала транзакций SQL. В зависимости от количества и частоты изменений файл .LDF может увеличиваться более чем в два раза, чем сама база данных. Если выделенное пространство для хранения закончится, новые транзакции не начнутся. Когда вы создаете резервную копию журнала транзакций SQL, он усекается и повторно использует пространство хранения. Резервное копирование и усечение журналов предотвращает переполнение дискового пространства.

На вкладке SQL выберите, как вы хотите управлять журналами транзакций SQL. Набор доступных опций удовлетворяет практически любые требования SQL. Для высоконагруженных баз данных SQL Server администраторы баз данных выполняют резервное копирование журналов транзакций каждые 15 минут или меньше. Низкого показателя RPO легко достичь, поскольку файлы журнала содержат только изменения в базе данных. Его резервное копирование выполняется намного быстрее, чем инкрементное резервное копирование SQL Server, и оно не влияет на производственную среду.

В Veeam Backup & Replication задания резервного копирования журнала транзакций SQL — это интервальный фоновый процесс.Он будет автоматически запускаться каждый раз в соответствии с заданным вами расписанием.

Резервные копии журналов хранятся в виде файлов .VLB в вашем репозитории резервных копий вместе с соответствующими резервными копиями виртуальных машин SQL Server.

Veeam Backup & Replication предоставляет полное резервное копирование виртуальных машин SQL Server на основе образов с цепочкой приращений и цепочкой резервных копий журналов SQL. Для восстановления базы данных вы можете использовать Veeam Explorer для Microsoft SQL. Он поддерживает сценарии восстановления базы данных из последней резервной точки восстановления, от воспроизведения журнала до определенного момента времени и от воспроизведения журнала до конкретной транзакции.