Журнал транзакций SQL увеличивается при использовании отслеживания измененных данных для Oracle от Attunity
Эта статья поможет устранить проблему, из-за которой вы заметили непрерывный рост журнала транзакций для базы данных с поддержкой CDC.
Исходная версия продукта: SQL Server 2012 и более поздних версиях
Исходный номер базы знаний: 2871474
Симптомы
Рассмотрим следующий сценарий.
- Вы используете Microsoft SQL Server 2017 в Windows, SQL Server 2016, 2014 или 2012 для Oracle by Attunity.
- Вы создаете экземпляр CDC для записи изменений из таблиц базы данных Oracle.
- Значения отслеживания изменений хранятся в SQL Server базах данных отслеживания изменений.
- Журнал транзакций в базе данных SQL Server увеличивается, и транзакции не помечаются для усечения при записи изменений данных.
В этом сценарии увеличение SQL Server файла журнала транзакций базы данных накапливается и потребляет слишком много места на диске с течением времени.
Причина
При настройке отслеживания измененных данных для экземпляров Oracle база данных SQL, получающая данные об изменениях, будет иметь зеркальные таблицы с транзакциями, помеченными для репликации. Это происходит потому, что CDC для Oracle использует базовые системные хранимые процедуры, похожие на те, которые используются в CDC для SQL Server. Тем не менее, так как репликация CDC SQL не требуется, когда CDC для Oracle используется отдельно, средство чтения журналов для очистки транзакций, помеченных для репликации, отсутствует. Так как транзакцию не нужно реплицировать в SQL Server, можно вручную пометить транзакцию как распределенную, используя обходной путь, описанный далее в этой статье.
Чтобы проверить эту точную причину DBCC OPENTRAN , выполните команду при подключении к базе данных CDC SQL Server. Вы увидите нераспределённый номер LSN, как показано в следующем примере:
Replicated Transaction Information: Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (38:272:1) DBCC execution completed. If DBCC printed error messages, contact your system administrator. Возможно, у вас есть нераспределённое имя LSN, так как CDC для Oracle использует CDC для хранимых процедур SQL и, в свою очередь, использует средство чтения журналов репликации. Это нераспределенное имя LSN соответствует записям журнала для добавления зеркальной таблицы в базу данных CDC Attunity.
При выполнении log_reuse_wait_desc этого запроса параметр возвращает значение REPLICATION , указывающее причину. Выберите имя из log_reuse_wait_desc sys.databases , где имя :
REPLICATION
Решение
- Выполните следующую команду в окне запроса, подключенном к базе данных с поддержкой CDC в SQL Server:
EXEC sp_repltrans Вы должны получить следующие выходные данные:
xdesid xact_seqno xact_seqno 0x000000260000012C0001 0x0000002A000001B50001 sp_repldone @xactid = 0x000000260000012C0001, @xact_segno = 0x0000002A000001B50001 DBCC OPENTRAN Это возвращает выходные данные, которые выглядят следующим образом:
No active open transactions. DBCC execution completed. If DBCC printed error messages, contact your system administrator. SELECT log_reuse_wait_desc, NAME FROM sys.databases WHERE NAME = 'your_cdc_database' Это возвращает выходные данные, которые выглядят следующим образом:
log_reuse_wait_desc name NOTHING your_cdc_database BACKUP LOG your_cdc_database TO DISK='c:\folder\logbackup.trn' DBCC SHRINKFILE (yourcdcdatabase_log, 1024) Дополнительная информация
В этой статье упомянуты программные продукты независимых производителей. Корпорация Майкрософт не дает никаких гарантий, подразумеваемых и прочих, относительно производительности и надежности этих продуктов.
Обратная связь
Были ли сведения на этой странице полезными?
SQL-Ex blog
Реляционные базы данных спроектированы для отслеживания изменений, вносимых в базу данных командами языка модификации данных (DML). Фундаментальной причиной такой структуры служит гарантия долговечности изменений и их надежного отката. Типичными командами DML, используемыми в SQL, являются INSERT, UPDATE и DELETE. Когда INSERT вносит новые строки в таблицу базы данных, ядро базы данных должно быть физически активно и работать в эффективном режиме.
Это означает, что изменения должны быстро записываться в файл журнала (сначала в буфер журнала), в то время как блоки фактических данных еще находятся в памяти пока не возникнет событие контрольной точки. То же самое происходит с операциями UPDATE и DELETE. Попытка сохранять изменения, сделанные в памяти, непосредственно в файлах данных будет неэффективной. Таким образом, этот файл журнала или файл журнала транзакций (Transaction Log File) очень важен для функционирования систем реляционных баз данных, подобных SQL Server.
В этой статье мы показываем, насколько больше места занимает журнал транзакций по сравнению с фактическими данным, содержащимися в базе данных. Мы также демонстрируем, что это происходит по той причине, что журнал транзакций захватывает и хранит историю изменений базы данных, в то время как файлы данных хранят конечное состояние после выполненных изменений. Также даются некоторые рекомендации по управлению этим ростом журнала транзакций.
Эксперимент: рост журнала по сравнению с ростом данных
На первом шаге демонстрируется пример роста журнала по сравнению с ростом файла данных при выполнении простых операций DML. База данных, используемая в эксперименте, была спроектирована таким образом, чтобы сделать очевидным воздействие шагов. Код в листинге 1 создает базу данных и включает режим полного восстановления (FULL RECOVERY)
-- Листинг 1: Создание базы с небольшим ростом файла для иллюстрации
USE [master]
GO
/* Object: Database [DB01] Script Date: 14/05/2022 9:38:51 am */CREATE DATABASE [DB01]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DB01', FILENAME = N'C:MSSQLDataDB01.mdf' , SIZE = 4MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1KB )
LOG ON
( NAME = N'DB01_log', FILENAME = N'E:MSSQLLogDB01_log.ldf' , SIZE = 512KB , MAXSIZE = 2048GB , FILEGROWTH = 1KB )
GO
ALTER DATABASE DB01 SET RECOVERY FULL;
GO
Чтобы убедиться в наличии базового уровня, мы используем листинг 2 для проверки физического размера файла данных и файла журнала, а также используемое пространство. На рисунке 1 показан результирующие наборы, полученные при выполнении кода листинга 2.
-- Листинг 2: Проверка роста физического журнала для базы данных
USE DB01
GO
SELECT name, physical_name, size*8 , max_size
FROM sys.master_files
WHERE name like 'DB01';
GO
-- Проверка пространства файла данных базы данных
EXEC sp_spaceused;
GO
-- Проверка пространства файла журнала для базы данных
SELECT
DB_NAME(database_id) [Database Name]
,total_log_size_in_bytes/1024 [Total Log Size (KB)]
,used_log_space_in_bytes/1024 [Used Log Space (KB)]
,used_log_space_in_percent [Used Log Space (]
FROM sys.dm_db_log_space_usage ;
GO
Рис.1: Размер файла данных и файла журнала, использовано (начальное состояние)
Обратите внимание на физические размеры файла данных и журнала, а также на использованное пространство, что показано на рис.1. Файлы данных и журнала имеют размеры 4096Кб и 512Кб соответственно. Напомню, что это размеры, указанные в скрипте создания базы данных. Также указано использованное на данный момент пространство.
Затем мы создаем новую таблицу в базе данных и вставляем в нее 5000 строк. После этого действия INSERT снова проверим рост файла. Результаты выполнения скрипта из листинга 2 показаны на рис.2.
-- Листинг 3: Оператор создания и наполнения таблицы
USE DB01
GO
CREATE TABLE TAB01 (
ID INT IDENTITY (1,1)
,Name CHAR(50));
GO
INSERT INTO TAB01 VALUES ('Kenneth Igiri');
GO 5000
Рис.2: Размер файла данных и журнала, использовано (после создания таблицы и вставки)
Снова посмотрите внимательно на области, указанные красными стрелками на результатах. Физические размеры файла данных не изменились (4096Кб), но журнал транзакций вырос до 3840Кб. Используемое пространство увеличилось в обоих случаях, но журнал транзакций в большей мере. Это ниже иллюстрируется на диаграммах рисунков 4 и 5.
Теперь мы выполним DML из листинга 4, который представляет собой набор операторов INSERT и DELETE, которые оставляют базу данных в неизменном состоянии, что касается числа строк. Однако, как мы наблюдаем на рис.3, что имеются изменения в использовании пространства файлами данных и журнала.
-- Листинг 4: Проверка роста журнала в базе данных
USE DB01
GO
INSERT INTO TAB01 VALUES ('Kenneth Igiri');
GO 5000
DELETE FROM TAB01 WHERE ID>5000;
GO
SELECT COUNT(*) as Computed FROM TAB01;
GO
Рис.3: Размер файла данных и журнала, использовано (после вставки и удаления)
Теперь значительную часть от общего размера базы данных составляет размер файла журнала транзакций.
Замечания относительно роста журнала транзакций
Как видно, интересный факт состоит в том, что журнал транзакций в SQL Server может возрасти значительно больше, чем файлы базы данных, в зависимости от активности базы данных. В журнал транзакций записывается история изменений состояния базы данных, в то время как файл базы данных сохраняет результаты каждого изменения без истории этих изменений.
Графический анализ
Для большей ясности возвращаемые в предыдущем разделе числа мы поместили в электронную таблицу и представили в виде гистограммы. Мы указали конкретные числа, используемые на рисунках 1, 2 и 3.
На рисунке 4 показано изменение пространства, используемого файлами данных и журнала, на одной гистограмме. Разрыв в росте используемого пространства между файлом журнала и файлом данных подтверждает более раннее утверждение. На рисунке 5 также показано, что физический журнал вносит теперь больший вклад в общий размер базы данных, чем сам файл данных.
Рис.4: Гистограмма, показывающая используемое пространство файлами данных и журнала
Число строк в таблице TAB001 покажет, что общее число полезных данных осталось тем же, поскольку мы просто встали дополнительно 5000 строк, а затем удалили их. В идеальном мире мы бы даже не увидели никакого изменения в используемом пространстве файла данных, но такие факторы, как фрагментация, приводят к некоторому наблюдаемому росту.
Рис.5: Гистограмма, показывающая физический размер файлов данных и журнала
Последствия роста журнала транзакций по сравнению с ростом данных
В зависимости от природы операций в среде вашей базы данных, весьма вероятно, что файл журнала транзакций будет расти в значительно большей мере, чем ваши файлы данных. Если вы работаете администратором локальной базы данных, то должны предусматривать такой рост. Если вы переносите нагрузку в облако, то можете понести расходы, связанные с объемами хранилища.
Некоторые администраторы пытаются срезать объемы, установив для промышленной базы данных простой режим восстановления (SIMPLE) для обслуживания роста журнала. Это чревато, потому что восстановление данных станет невозможным в случае сбоя в середине дня. Кроме того, вы по-прежнему сталкиваетесь с ростом в сценариях, когда долгоиграющие транзакции приводят к росту журнала транзакций и приводят вас к необходимости сжимать файл транзакций. Хорошей практикой является использование FULL REСOVERY MODE для промышленных баз данных.
Рекомендации
- Проанализировать активность базы данных и определить наибольший размер журнала транзакций, до которого он может вырасти в течение самого загруженного часа дня. На этом основании предусмотрите выделенный диск, чтобы журнал транзакций никогда не превысил пространство в операционной системе (Error 9002).
- Составьте расписание выполнения процедуры создания резервной копии журнала транзакций в общей стратегии резервирования. Периодичность этой операции должна быть час или менее в зависимости от почасовой нагрузки базы данных.
- Если вы используете Transaction Log Shipping для аварийного восстановления, вам может не понадобиться резервирование журнала транзакций. Однако вы должны убедиться, что ваша настройка аварийного восстановления работает стабильно.
- При несчастном случае, когда вы превышаете дисковое пространство или выделенное по бюджету, вам потребуется сжать журнал или создать дополнительный файл журнала на другом носителе. Что бы вы ни делали, ручное резервирование журнала транзакций всегда рекомендуется как первый шаг, если это возможно.
Анализ активности базы данных
Один быстрый способ отслеживать использование файла журнала транзакций на вашем экземпляре — использовать счетчик производительности Log Files Used Size (KB), что показано на рисунке 6. Этот счетчик имеется в мониторе производительности (Performance Monitor) под группой SQLServer:Databases (смотри рис.7).
Рис.6: Счетчик производительности используемых файлов журнала
Обратите внимание, что этот счетчик был включен только для интересующей нас базы данных DB01. Это помогает изолировать потребности каждой базы данных, а не тот случай, когда используется Total_ для всех экземпляров.
Рис.7: Счетчик производительности используемых файлов журнала
Составьте расписание выполнения процедуры создания резервной копии журнала транзакций
Листинг 5 дает пример скрипта, который создает задание SQL Agent для создания резервной копии журнала транзакций для указанных баз данных с почасовым интервалом. Задание также посылает уведомление по завершению работы с тем, чтобы вы могли отслеживать успешное создание бэкапов журнала. Ожидается, что у вас будут другие задания для выполнения полной и дифференциальной резервных копий, если вы выберите этот метод.
Возможно такое выполнение при использовании инструментов создания резервных копий третьих фирм, таких как Veritas Netbackup, Devart’s SQL Backup Tool и других. В этой статье описан очень конкретный случай использования для конфигурирования резервного копирования с помощью Veritas Netbackup.
-- Листинг 5: Создание задания для бэкапа журнала транзакций
-- Скрипт задания SQL Agent
-- Проверьте, что вы указали путь бэкапа. Это может быть также UNC-путь
-- Замените DB1, DB2, DB3 и т.д. списком баз данных экземпляра
-- Создайте оператор "DatabaseAdmin" или используйте имя по вашему выбору
USE [msdb]
GO
/* Object: Job [Custom_Log_Backups] Script Date: 12/11/2016 10:07:21 */BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/* Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/11/2016 10:07:21 */IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Custom_Log_Backups',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@notify_email_operator_name=N'DatabaseAdmin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/* Object: Step [Backup Log] Script Date: 12/11/2016 10:07:22 */EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Log',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec sp_MSforeachdb @command1=''
DECLARE @backup sysname
set @backup=N''''L:BACKUP?_'''' + convert(nvarchar,getdate(),112)+N''''.trn''''
if ''''?'''' in ("DB1","DB2","DB3")
backup log [?] to disk = @backup with compression''',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sch_Backup_Log',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20161211,
@active_end_date=99991231,
@active_start_time=180000,
@active_end_time=235959,
@schedule_uid=N'575f95a5-b353-42b8-9b62-e09e0653c5b6'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Конфигурация аварийного восстановления
Конфигурирование аварийного восстановления — это вообще совершенно другая тема. Связь с настоящей статьей состоит в том, что во всех методах аварийного восстановления в SQL Server фирма Microsoft использует резервные копии журнала транзакций и усечение как часть решения. Помимо прочего, одним из фундаментальных назначений журнала транзакций является восстановление. Transaction Log Shipping реализует в фоновом режиме резервирование журналов, таким образом, нет действительной необходимости отдельно конфигурировать резервирование журналов. При использовании AlwaysOn Availability Groups резервирование журналов требуется, по крайней мере, для одной реплики.
В этой статье пошагово описывается конфигурирование Transaction Log Shipping с особым вниманием к отложенному восстановлению. Посмотрите раздел Setting Up the Environment, в котором показано, что конфигурация Log Shipping состоит из трех заданий SQL Agent Jobs — задания резервирования на первичной базе данных, задания копирования и задания восстановления (обоих на вторичных базах данных).
Резервирование журнала и сжатие
В листинге 6 показан скрипт, используемый для резервирования журнала транзакций и сжатия его до 2Мб. Обратите внимание, что требуется выполнить полный бэкап до того, как может быть выполнено резервирование журнала. Также отметим, что размер, до которого может быть сжат журнал транзакций, зависит от того, сколько доступно свободного места.
-- Листинг 6: Усечение жарнала транзакций
-- Резервирование базы данных (полный бэкап)
USE master
GO
BACKUP DATABASE DB01
TO DISK = N'E:BackupDB01.bak';
-- Резервирование журнала транзакций
BACKUP LOG DB01
TO DISK = N'E:BackupDB01_Log.trn';
-- Подтверждение свободного места в журнале транзакций
USE DB01
GO
SELECT
DB_NAME(database_id) [Database Name]
,total_log_size_in_bytes/1024 [Total Log Size (KB)]
,used_log_space_in_bytes/1024 [Used Log Space (KB)]
,used_log_space_in_percent [Used Log Space (%)]
FROM sys.dm_db_log_space_usage ;
GO
-- Сжатие журнала транзакций до желаемого размера на основе свободного места
USE DB01
GO
DBCC SHRINKFILE ('DB01_log',2)
GO
Монторинг журнала транзакций
Microsoft предоставляет несколько инструментов для мониторинга журнала транзакций, некоторые из которых перечислены в ссылках. Инструменты третьих фирм, подобные Redgate’s SQL Monitor, dbForge Transaction Log, SQL Monitor (часть Devart’s DevOps tools for database) и SQL Transaction Log Reader помогают администраторам баз данных монторить и обслуживать журнал транзакций. Эти инструменты обычно имеют визуальные панели, которые облегчают работу с ними.
Заключение
Управление ростом журнала транзакций является ключевым аспектом администрирования базы данных SQL Server. Это особенно справедливо, когда вы все еще размещаете экземпляры локально или используете виртуальные машины для выполнения рабочих нагрузок в облаке. У каждого достойного администратора баз данных должны быть проактивные, автоматизированные способы делать это.
Важно отметить, что помимо транзакций в пользовательской базе данных, обслуживание таких действий, как перестройка индексов, также может вызвать значительный рост. Это должно быть учтено в планах обслуживания.
Ссылки
- Журнал транзакций
- Управление размером журнала транзакций
- dbForge Transaction Log
- Монитор проиводительности SQL Server
- Инструменты Database DevOps
Почему растет log в ms sql
Вырос log файл базы до 420 гигов. Сама база весит 7 гигов. База РИК. Причем растет скачкообразно может неделю не изменятся размер а потом за раз вырасти сразу на 40 гигов. Все планы обслуживания испробованы результат нулевой. В чем проблема кто нибудь сталкивался.
(0)
что такое РИК ?
(1) РИБ опечатка )))
«может неделю не изменятся размер а потом за раз» запустили невменяемую обработку по закрытию какой-нить ереси на двое суток и получайте.
Как уменьшить размер log файл а то скоро места не хватит?
(4) Ставишь у базы «Модель восстановления» Simple(Простая). ПКМ на базу, «задачи-сжать-файлы», выбираешь файл лога, ставишь новый размер.
Интересно, а можно файл log перенести на другой физический диск? А то, по моему, нахождение его на том же физическом устройстве, довольно прилично тормозит работу, при которой меняются большие объемы данных. Например, закрытие периода.
(6) Не поверишь — нужно! Это официальная рекомендация MS, так скуль будет работать быстрее.
И в (5) тебе правильно сказали, только перед этим тот же MS рекомендует бэкап сделать.
(7) Я уж это не стал писать:).
Вообще оптимизация быстродействия — отдельная песня, но общие рекомендации таковы — базу — на отдельный физический носитель либо на отдельный массив дисков. То же самое — с логом, так как 1с активно его использует. Лог-файл бьется на отдельные файлы, которые соответствуют количеству ядер в системе. Система — на отдельном диске. Навскидку — так.
(5) Модель восстановление не хочется ставить Простую стоит полная. Задача сжать файлы не помогла. А поставить при рабочей базе новый размер лога как то боязно, а примерно сколько ставить размер лога и чем это может грозить.
(9) сжатие лога без транкейта, как новый год без ёлочки
(9) На работающей базе это делать не нужно. Выгони пользователей. Сделай бэкап. Затем сделай как я написал. Потом верни обратно модель восстановления.
А бэкапы журнала делаешь? Поидеи должен уменьшаться после резервной копии.
ldf растет наиболее сильно, когда происходит перестроение индексов. Нет ли у тебя такого в планах обслуживания? Если так, то нужно шринковать после обслуживания. Только не базу, а сам ldf. Еще вариант — массовая загрузка данных. Но индексы наиболее вероятно.
(8) бить нужно не лог, а tempdb. «биение» лога бессмысленно.
А простая выгрузка и загрузка средствами 1С решает проблему лога и фрагментации базы?
(0) Постоянно читаю вот эти статьи. Жду просветления.
Пока понял что если полная модель восстановления, то БД стремиться обеспечить восстановление данных на любой момент времени, точнее на любую транзакцию. Поэтому урезать журнал так просто не получится, т.к. нарушим целостность восстановления. Перед урезанием нужно сделать резервню копию не базы данных а именноо логфайла. Тем самым система зафиксирует что копия есть, можно обрезать базу, целостность не нарушена. Далее возникает следующая проблема. Транзации в логфайле могут быть раскиданы как полпало, например текущая траназация в конце лога, соотв. урезание ни к чему не приведёт. Щас не помню какие команды нужда выполнить шоб сдвинуть актуальные транзации в начало. Далее шринкуем, т.е. освобождаем не используемое простанство логфайла.
Изучаю дальше эту тему. Еще много непонятного.
(16) опять же временно )
сделай бэкап ЛОГА и шринкани, это проще чем загружать выгружать dt
(19)Ну, хотя бы перед большими обработками.
(20)Дело в том, что админ в конторе совершенно не представляет себе всех нюансов работы с какой-либо SQL. Такое ощущение, что он оканчивал философский факультет политеха. Вот и думаю, что проще ему на 1С стандарте делать все операции.
(22) Чё за мода пошла на админов которые мышки меняют и винду переустанвливают вешать задачи по обслужаванию баз данных. Кто скорее специались по БД? Одинэсник или этот переустановщик винды?
(23) Что за мода пошла. на тех кто обслуживать бушек. вешать сервера.
(23)Нормальная такая мода. Он позиционирует себя, как крутой спец. Но, хотя бы элементарные вещи по SQL должен знать. К 1С это, совершенно, не относится.
(22) тогда урезать и переводить базу в режим simple, хотя переодически проблема с логом все равно будет возникать, но не в таких масштабах
(24)(25) Ну ладно останемся при своих мнениях. Я считаю что администрирование сети, винды совсем близко не радом с обслуживание БД типа SQL или Oracle. Это блин вообще отдельная специальность, которая согласитесь ближе к работе одинэсника.
(27) Да ладно. одинесник должен бушек обслуживать и облизывать..а серьезные вещи типа администрирование SQL и Оракл. как правильно сказал другие люди. и админ ближе к этому так как технический специалист по образованию.
база была 60 гб, лог файл 180 гб. Покурил покурил и так и не понял, на кой черт мне нужен этот лог файл. Сделал модель simply и живу дальше припеваючи. Всё ок
(28) Так стоп. Я думал одинэсники это технические специалисты с очень обширными позниниями в теории БД в первую очередь, Операционных система, сетях, БУ, НУ, МСФО. Я ошибался?
(29) Такая модель тоже имеет место быть. Это нормально. Это зависит от уровня пох. ма админа. Расскажи как часто делаются резервные копии базы в 60 гигов?
(30)Ошибаешься. Если спросить админа че нить про проводки, он со стула рухнет.
(32) Я правильно понял что ты хорошо знаешь теорию БУ, НУ а про индексы БД не слышал и принципиально не хочешь слышать?
(30) Просто есть еще универсалы вот и все. В глубинках России приходится так вот работать))))
(31) был куплен внешний винт на 1,5 ТБ. Делаются архивы каждый день. В итоге за месяц остаются архивы только на первое число. Остальное удаляется.
(34) Я сам из глубинки. Мне самому приносили утюги и телевизоры ремонтировать, и лампочки в туалете почему то тоже «компьютерщик» должен менять. Ну я про тоже что админ админит мышки и клавы, а чел по работе с БД вроде как занимается БД. Нет?
(33)Пожалуй, тебе на стольких БД, на которых я работал, не приходилось работать. Думаю, такая же ситуация и в области знаний по БУ, НУ. Я просто хочу чтобы каждый занимался своим делом. А БД, насколько позволяет мне мой опыт, ближе к администрированию.
(36) Ну т.е. раз в сутки, если вдруг в обед полетит винд с рабочей БД, то данные по 1000 отгрузок за полдня вручную перебивать с первички? Если тебя за это не подвесят то это приемлемый вариант.
(11) Попробую как посоветовал. А так архивы делаются раз в сутки и на SQL и DT выгружается а вот архивирования журнала выключили так как подумали что вроде и не нужен, хотя после прочтения все ответов понял что лучше его включить и потихоньку log уменьшится.
(38) там зеркало )
(40) Видал я как эти зеркала сыплются, стоимость восстановления дороже чем всю базу перебить вручную.
(41) ага, я тоже видал как серверную из канализации заливало )
(36) С этим согласен но думаю что еще многое зависит от аппаратной части т.е. железа если сервер нормальна собран под задачи с минимальными рисками. У нас Raid 10. Причем архивы кидаются на другой сервер.
Включу архивацию лога раз в час посмотрим завтра как это повлияет на размер лога.
Мда дела пока болтали вырос еще на 22 гига. Т.е. буквально за 2 часа.
(15) Разумеется, я ошибся, прошу прощения. Файлы данных TempDB разбиваются, лог TempDB остается в остается одним файлом.
(45) Ну а что с базой-то происходит? Должны быть массовые операции со строками или операции с индексами. Что в профайлере видно?
(47) Да вроде ни чего нет в данный момент нет пользователей. План обслуживания такой архивирования базы-перестроение индекса-реорганизация индекса-обновление статистики. В данный момент включил архивирование журнала транзакций раз в час.
(48) «перестроение индекса» — ну вот и лови 22 гига.
(48) «перестроение индекса-реорганизация индекса-обновление статистики»
Да. действительно ничё особенного. Как вы там ещё работаете.
(49) Я так понял что если я включил архивирования журнала транзакции то лог уменьшится. Все нормальна работало все это произошло буквально за пару недель. Но было включено архивирования лога транзакции. И плотформа была 15. В данный момент 16. И пошел рост лога.
(51) Каждый раз перестраивать все индексы не нужно, это очень затратная и в общем бесполезная операция(если каждый раз). После этой операции реорганизация индексов и обновление статистики уже не нужны.
(52) т.е архивирование базы одиним планом обслуживания каждый день а остальное можно и раз в недельку другим планом обслуживания?
(53) Да. Правда, смотря какая база. Если много данных меняется — вставка, удаление — тогда можно и чаще. Опять же — не обязательно все подряд. Вот такая процедура крутится у меня:
DECLARE @SQL varchar(256), @DB_ID int;
SET @DB_ID = (SELECT DB_ID());
DECLARE reindex CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
SELECT ‘ALTER INDEX ALL ON [‘ + OBJECT_NAME(afp.OBJECT_ID) + ‘] REBUILD WITH (SORT_IN_TEMPDB = ON);’ AS [Инструкция T-SQL]
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, ‘SAMPLED’) AS afp
WHERE afp.database_id = @DB_ID
AND afp.index_type_desc IN (‘CLUSTERED INDEX’)
AND (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent AND afp.page_count > 12
UNION ALL
SELECT [Инструкция T-SQL] =
CASE
WHEN afp.avg_fragmentation_in_percent >= 15
OR afp.avg_page_space_used_in_percent THEN ‘ALTER INDEX [‘ + i.name + ‘] ON [‘ + OBJECT_NAME(afp.OBJECT_ID) + ‘] REBUILD WITH (SORT_IN_TEMPDB = ON);’
WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
THEN ‘ALTER INDEX [‘ + i.name + ‘] ON [‘ + OBJECT_NAME(afp.OBJECT_ID) + ‘] REORGANIZE;’
END
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, ‘SAMPLED’) AS afp
JOIN sys.indexes AS i
ON (afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)
AND afp.database_id = @DB_ID
AND afp.index_type_desc IN (‘NONCLUSTERED INDEX’)
AND (
(afp.avg_fragmentation_in_percent >= 10 AND afp.avg_fragmentation_in_percent < 15)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
)
AND afp.page_count > 12
AND afp.OBJECT_ID NOT IN (
SELECT OBJECT_ID
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, ‘SAMPLED’)
WHERE database_id = @DB_ID
AND index_type_desc IN (‘CLUSTERED INDEX’)
AND (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
AND page_count > 1
)
ORDER BY [Инструкция T-SQL]
OPEN GLOBAL reindex
WHILE 1 = 1
BEGIN
FETCH reindex INTO @SQL
IF @@fetch_status <> 0 BREAK
—select @SQL
EXEC(@SQL)
PRINT @SQL
END
CLOSE GLOBAL reindex
DEALLOCATE reindex
Почему растет log в ms sql
В некоторых случаях при использовании клиент-серверного варианта работы «Управление дистрибуцией» может наблюдаться быстрый рост базы данных на MS SQL сервере.
Проблема с быстрым увеличением размера базы MS SQL сервера связана с особенностями настройки самого сервера.
Согласно описанию на сайте 1С в разделе » Настройки Microsoft SQL Server для работы с 1С:Предприятием «, данное поведение регулируется настройкой «Recovery Model«.
Если для настройки установлено значение «Full«, то журнал транзакций очень быстро заполняется записями, но при этом настройка позволяет восстанавливать состояние базы на любой момент времени.
Если установлено значение «Simple«, то базу данных невозможно восстановить на любой момент, но файл журнал транзакций будет расти гораздо медленнее.
Также рекомендуем настроить автоматическое создание резервных копий и автоматическое обрезание (truncate) файла лога.