Проблемы с базой, решения.
отсюда
отсюда
Всё о сокращении Transaction Log для MS SQL 2008/2012
Рассмотрены пожалуй все варианты проблем из за которых
журнал транзакций может не сокращаться и все варианты решений. От
простейших, до "не рекомендуемых".
Для MS SQL 2008/2012
рекомендации ИТС уже устарели, кроме того и раньше они не всегда
помогали. В статье попытался собрать наиболее полный комплект информации
по данному вопросу.
В своё время в одном месте всего этого не нашел, поэтому думаю будет полезно.
Популярная статья ИТС http://its.1c.ru/db/metod81#content:2373:1 устарела - теперь уменьшение размера журнала транзакций стало не самой простой операцией.
Собственно там рекомендуется следующий скрипт:
BACKUP LOG Имя_Базы_Данных WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE(Имя_Файла_Журнала_Транзакций)
go
Если выполнить его в MS SQL 2008/2012 получите ошибку:
'truncate_only' is not a recognized BACKUP option
Подробно:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9997e75e-f126-4901-9379-de540a708ec9/backup-log-with-truncateonly
Что теперь делать?
Решения, собственно два:
1)
USE [Database] ALTER DATABASE [Database] SET RECOVERY SIMPLE
go
DBCC SHRINKFILE ([Database]_log, 1);
ALTER DATABASE [Database] SET RECOVERY FULL
go
2)
USE [Database]
BACKUP LOG [Database] TO DISK='NUL:'
go
DBCC SHRINKFILE ([Database]_log, 1)
go
Если "Урезанием лога" не злоупотреблять (т.е. сокрашать лог вместе с полной копией) то по большому счету не принципиально каким методом пользоваться.
Второй вроде как правильнее, зато первый "надежнее".
На этом казалось бы можно и остановиться, но зачем тогда отдельную статью писать. Нет, конечно это ещё не всё. Обычно вопросы про урезание лога возникают когда это сделать не получается.
Притом способы, описанные выше, как правило, описаны не раз, все их освоили и проблем не вызывают.
Итак, если все действия, описанные выше не помогли - лог файл по-прежнему занимает N гигабайт. Переходим к плану B:
select log_reuse_wait_desc from sys.databasesВ результате можете получить 3 варианта:
а. Пусто - Обычно это означает что у БД лог можно хоть сейчас полностью сократить, могу предложить только попробовать ещё раз Shrink, а если не поможет - переходить к плану C
b. Log_Backup - Нормальный варинат. В данном случае говорит о том, что Backup Log не выполнено, или выполнено некорректно
b. Replication - значит что ваш лог не обрезается из за репликации - скорее всего ошибки.
с. Active transactions - Самая частая ситуация - в базе есть подвисшие транзакции, с ними нужно разобраться.
Replication - Репликация для систем на платформе 1С, пожалуй, бессмысленное дело. Потому как Read only баз MS SQL не бывает, средства создания распределенных систем в 1С есть собственне (да, я про РИБ). Для обеспечения отказоустойчивости гораздо лучше подходят кластерные технологии. Собственно рекоммендация простая:
sp_removedbreplication '[Database]'Собственно после этого бэкап и Shrink помогут. Если же вопреки здравому смыслу вы всё-таки хотите сохранить репликацию БД то конечно выполнять эту команду нельзя, а нужно разбираться с ошибками репликации. Но это уже тема отдельной статьи.
Active transactions - наиболее популярная история. В базе есть транзакции, которые не завершены, и чего то ожидают. Чащи всего такие транзакции получаются при потере сетевого соединения или "вылете" клиента 1С в момент записи в БД. В этом случае нужно собственно узнать какая транзакция "повисла":
DBCC OPENTRAN
После выполнения этой команды вы получите примерно следующий результат:
Transaction information for database 'master'.
Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : -1
Name : user_transaction
LSN : (518:1576:1)
Start time : May 5 2014 3:30:07:197PM
SID : 0x010500000000000515000000a065cf7e784b9b5fe77c87709e611500
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Из этого обилия информации ключевым является Start Time и SPID. Если транзакция в базе 1С выполняется боле нескольких секунд это уже означает что что-то не так. А если start Time будет минут 10 или более от текущего времени - такие транзакции (сеансы) нужно завершать. Но предварительно я бы рекоммендовал узнать что эта транзакция делала.Для завершения процесса можно ввести команду
KILL [Process ID]
Где Process ID - это тот самый SPID полученный на предыдущем шаге. При этом незавершенные транзакции откатятся средствами MS SQL Server. Возможно при "убийстве" процесса будут завершены и несколько сеансов 1С, но вряд ли много. Сервер 1С поддерживает собственный пул соединений с MS SQL, соответственно соединения из этого пула используются только тогда, когда серверу что-то нужно от СУБД. При этом если соединение занято (а оно как видим занято) вряд ли оно будет использоваться для других процессов.
Но предварительно (!) если хотите всё-таки разобраться в проблеме рекомендую выполнить скрипт вроде:
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = [Process ID]
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GOВ результате вы получите текст команды SQL Server, на которой, собственно, всё и "зависло". Из неё вам нужна будет таблица в которую производилась запись, далее используя функцию "ПолучитьСтруктуруХраненияБазыДанных()" вы определите таблицу в терминах объектов метаданных в которую производилась запись и смотрите код. Как правило такие неприятные последствия происходят:
1) Ошибки в сетевых подключениях (для толстого клиента в т.ч. в сетевых подключениях клиентов, для тонкого - только в проблемах сети между сервером 1С и MS SQL).
2) Каких то неправильных действиях (отправка почты, запись в файл, запуск внешних обработок, чтения из файла) производимых в транзакциях (при записи, при проведении)
Собственно от них надо избавляться.
Если ничего не помогло (или план B)
ВНИМАНИЕ! Перед выполнением процедур, описанных ниже, сделать полную резервную копию файлов БД MS SQL нужно обязательно!!!!
Есть ещё один - более радикальный способ решения вопроса роста журнала транзакций MS SQL. Но я лично его бы не рекомендовал к использованию. Тем не менее, специалисты Microsoft тоже могут ошибаться,
и SQL Server может содержать ошибки, о которых мы регулярно читаем в BugFix, или же наблюдаем сами, поэтому приведу и этот способ.
Суть его заключается в том что журнал транзакций просто удаляется и создается новый. При этом вы конечно теряете информацию из него и БД можно будет восстановить только из полной копии (которую вы конечно перед этим сделали).
Конечно при этом, особенно если в базе были всё-таки не зафиксированные может быть нарушена логическая целостность, но для этого запускается CheckDB которая в общем и целом приводит базу в порядок. Для аналогии это то же самое что в 1С проврять ссылочную целостность с опцией "Удалять если не найден". Если транзакция полностью не зафиксирована, но от неё остались частично данные, что противоречит принципу атомарности транзакций - эти данные будут удалены.
Итак приступим:
1) Detach БД из списка
2) Фал *.ldf удаляем (вы же его сохранили уж, да?)
3) Файл *.mdf переименовываем (в любое имя какое нравится)
4) В MS SQL создаём новую (!!!) БД с тем же именем, с каким была "больная" БД
5) Останавливаем MS SQL Server
6) Новый *.mdf файл удаляем, а старый переименовываем под "старое имя", подменяя тем самым файл новой БД
7) Запускаем MS SQL Server. При этом будет "битая БД", далее мы её исправляем
8) ALTER DATABASE [Database] SET EMERGENCY
9) exec sp_dboption [Database], 'single user', 'TRUE'Монопольный режим работы с БД
10) DBCC CHECKDB ([Database], REPAIRALLOWDATA_LOSS)Ключевая операция - "возвращает БД к жизни". Может выполняться достаточно долго - до получаса на больших БД. Ни в коем случае не прерывайте эту операцию. Результат, где будут собраны исправленные ошибки
на всякий случай сохраните
11) exec sp_dboption [Database], 'single user', 'FALSE'Сбрасываем монопольный режим
12) ALTER DATABASE [Database] SET ONLINEДелаем базу доступной.
После чего получаем БД с чистым новеньким логом. На самом деле операция достаточно проста и в большинстве случае не несёт никаких критических последствий. Но всё-таки рекомендую прибегать к ней только в крайнем случае. Описана она не раз и в разных вариациях. Привожу свой вариант, который показался наиболее простым и понятным.
Внимание !
При выполнении ALTER DATABASE база SET EMERGENCY, к базе не должно быть соединений (подключения пользователей), если подвисло и на следующие команды выдает сообщение Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database 'name'. Try again later. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. надо убить все коннекты к данной базе, можно через монитор .
SELECT
DB_NAME(dbid) as 'DBName'
, loginame as 'Login'
, COUNT(dbid) as 'Connections'
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid
, loginame
--------------------
SELECT name , dbid FROM master.dbo.sysdatabases order by name
---------------------------------------------------------
select DATABASEPROPERTYEX('<your database>', 'UserAccess')
ALTER DATABASE база SET EMERGENCY - ????
ALTER DATABASE база SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB ('база');
DBCC CHECKALLOC ('база');
DBCC CHECKDB ('база',repair_allow_data_loss);
ALTER DATABASE база SET MULTI_USER;
-----------------------------------------------------------
взято с
Если необходимо восстановить просто базу из полной копии, то достаточно выполнить следующий скрипт:
В случае, если необходимо восстановить последовательно сначала полную копию, разностные копии и журналы транзакций, тогда необходимо написать следующий SQL скрипт.
Для восстановления БД можно использовать так же и SSMS.
В своё время в одном месте всего этого не нашел, поэтому думаю будет полезно.
Популярная статья ИТС http://its.1c.ru/db/metod81#content:2373:1 устарела - теперь уменьшение размера журнала транзакций стало не самой простой операцией.
Собственно там рекомендуется следующий скрипт:
BACKUP LOG Имя_Базы_Данных WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE(Имя_Файла_Журнала_Транзакций)
go
Если выполнить его в MS SQL 2008/2012 получите ошибку:
'truncate_only' is not a recognized BACKUP option
Подробно:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9997e75e-f126-4901-9379-de540a708ec9/backup-log-with-truncateonly
Что теперь делать?
Решения, собственно два:
1)
USE [Database] ALTER DATABASE [Database] SET RECOVERY SIMPLE
go
DBCC SHRINKFILE ([Database]_log, 1);
ALTER DATABASE [Database] SET RECOVERY FULL
go
2)
USE [Database]
BACKUP LOG [Database] TO DISK='NUL:'
go
DBCC SHRINKFILE ([Database]_log, 1)
go
Если "Урезанием лога" не злоупотреблять (т.е. сокрашать лог вместе с полной копией) то по большому счету не принципиально каким методом пользоваться.
Второй вроде как правильнее, зато первый "надежнее".
На этом казалось бы можно и остановиться, но зачем тогда отдельную статью писать. Нет, конечно это ещё не всё. Обычно вопросы про урезание лога возникают когда это сделать не получается.
Притом способы, описанные выше, как правило, описаны не раз, все их освоили и проблем не вызывают.
Итак, если все действия, описанные выше не помогли - лог файл по-прежнему занимает N гигабайт. Переходим к плану B:
select log_reuse_wait_desc from sys.databasesВ результате можете получить 3 варианта:
а. Пусто - Обычно это означает что у БД лог можно хоть сейчас полностью сократить, могу предложить только попробовать ещё раз Shrink, а если не поможет - переходить к плану C
b. Log_Backup - Нормальный варинат. В данном случае говорит о том, что Backup Log не выполнено, или выполнено некорректно
b. Replication - значит что ваш лог не обрезается из за репликации - скорее всего ошибки.
с. Active transactions - Самая частая ситуация - в базе есть подвисшие транзакции, с ними нужно разобраться.
Replication - Репликация для систем на платформе 1С, пожалуй, бессмысленное дело. Потому как Read only баз MS SQL не бывает, средства создания распределенных систем в 1С есть собственне (да, я про РИБ). Для обеспечения отказоустойчивости гораздо лучше подходят кластерные технологии. Собственно рекоммендация простая:
sp_removedbreplication '[Database]'Собственно после этого бэкап и Shrink помогут. Если же вопреки здравому смыслу вы всё-таки хотите сохранить репликацию БД то конечно выполнять эту команду нельзя, а нужно разбираться с ошибками репликации. Но это уже тема отдельной статьи.
Active transactions - наиболее популярная история. В базе есть транзакции, которые не завершены, и чего то ожидают. Чащи всего такие транзакции получаются при потере сетевого соединения или "вылете" клиента 1С в момент записи в БД. В этом случае нужно собственно узнать какая транзакция "повисла":
DBCC OPENTRAN
После выполнения этой команды вы получите примерно следующий результат:
Transaction information for database 'master'.
Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : -1
Name : user_transaction
LSN : (518:1576:1)
Start time : May 5 2014 3:30:07:197PM
SID : 0x010500000000000515000000a065cf7e784b9b5fe77c87709e611500
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Из этого обилия информации ключевым является Start Time и SPID. Если транзакция в базе 1С выполняется боле нескольких секунд это уже означает что что-то не так. А если start Time будет минут 10 или более от текущего времени - такие транзакции (сеансы) нужно завершать. Но предварительно я бы рекоммендовал узнать что эта транзакция делала.Для завершения процесса можно ввести команду
KILL [Process ID]
Где Process ID - это тот самый SPID полученный на предыдущем шаге. При этом незавершенные транзакции откатятся средствами MS SQL Server. Возможно при "убийстве" процесса будут завершены и несколько сеансов 1С, но вряд ли много. Сервер 1С поддерживает собственный пул соединений с MS SQL, соответственно соединения из этого пула используются только тогда, когда серверу что-то нужно от СУБД. При этом если соединение занято (а оно как видим занято) вряд ли оно будет использоваться для других процессов.
Но предварительно (!) если хотите всё-таки разобраться в проблеме рекомендую выполнить скрипт вроде:
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = [Process ID]
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GOВ результате вы получите текст команды SQL Server, на которой, собственно, всё и "зависло". Из неё вам нужна будет таблица в которую производилась запись, далее используя функцию "ПолучитьСтруктуруХраненияБазыДанных()" вы определите таблицу в терминах объектов метаданных в которую производилась запись и смотрите код. Как правило такие неприятные последствия происходят:
1) Ошибки в сетевых подключениях (для толстого клиента в т.ч. в сетевых подключениях клиентов, для тонкого - только в проблемах сети между сервером 1С и MS SQL).
2) Каких то неправильных действиях (отправка почты, запись в файл, запуск внешних обработок, чтения из файла) производимых в транзакциях (при записи, при проведении)
Собственно от них надо избавляться.
Если ничего не помогло (или план B)
ВНИМАНИЕ! Перед выполнением процедур, описанных ниже, сделать полную резервную копию файлов БД MS SQL нужно обязательно!!!!
Есть ещё один - более радикальный способ решения вопроса роста журнала транзакций MS SQL. Но я лично его бы не рекомендовал к использованию. Тем не менее, специалисты Microsoft тоже могут ошибаться,
и SQL Server может содержать ошибки, о которых мы регулярно читаем в BugFix, или же наблюдаем сами, поэтому приведу и этот способ.
Суть его заключается в том что журнал транзакций просто удаляется и создается новый. При этом вы конечно теряете информацию из него и БД можно будет восстановить только из полной копии (которую вы конечно перед этим сделали).
Конечно при этом, особенно если в базе были всё-таки не зафиксированные может быть нарушена логическая целостность, но для этого запускается CheckDB которая в общем и целом приводит базу в порядок. Для аналогии это то же самое что в 1С проврять ссылочную целостность с опцией "Удалять если не найден". Если транзакция полностью не зафиксирована, но от неё остались частично данные, что противоречит принципу атомарности транзакций - эти данные будут удалены.
Итак приступим:
1) Detach БД из списка
2) Фал *.ldf удаляем (вы же его сохранили уж, да?)
3) Файл *.mdf переименовываем (в любое имя какое нравится)
4) В MS SQL создаём новую (!!!) БД с тем же именем, с каким была "больная" БД
5) Останавливаем MS SQL Server
6) Новый *.mdf файл удаляем, а старый переименовываем под "старое имя", подменяя тем самым файл новой БД
7) Запускаем MS SQL Server. При этом будет "битая БД", далее мы её исправляем
8) ALTER DATABASE [Database] SET EMERGENCY
9) exec sp_dboption [Database], 'single user', 'TRUE'Монопольный режим работы с БД
10) DBCC CHECKDB ([Database], REPAIRALLOWDATA_LOSS)Ключевая операция - "возвращает БД к жизни". Может выполняться достаточно долго - до получаса на больших БД. Ни в коем случае не прерывайте эту операцию. Результат, где будут собраны исправленные ошибки
на всякий случай сохраните
11) exec sp_dboption [Database], 'single user', 'FALSE'Сбрасываем монопольный режим
12) ALTER DATABASE [Database] SET ONLINEДелаем базу доступной.
После чего получаем БД с чистым новеньким логом. На самом деле операция достаточно проста и в большинстве случае не несёт никаких критических последствий. Но всё-таки рекомендую прибегать к ней только в крайнем случае. Описана она не раз и в разных вариациях. Привожу свой вариант, который показался наиболее простым и понятным.
Внимание !
При выполнении ALTER DATABASE база SET EMERGENCY, к базе не должно быть соединений (подключения пользователей), если подвисло и на следующие команды выдает сообщение Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database 'name'. Try again later. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. надо убить все коннекты к данной базе, можно через монитор .
SELECT
DB_NAME(dbid) as 'DBName'
, loginame as 'Login'
, COUNT(dbid) as 'Connections'
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid
, loginame
--------------------
SELECT name , dbid FROM master.dbo.sysdatabases order by name
---------------------------------------------------------
select DATABASEPROPERTYEX('<your database>', 'UserAccess')
ALTER DATABASE база SET EMERGENCY - ????
ALTER DATABASE база SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB ('база');
DBCC CHECKALLOC ('база');
DBCC CHECKDB ('база',repair_allow_data_loss);
ALTER DATABASE база SET MULTI_USER;
-----------------------------------------------------------
взято с
Если необходимо восстановить просто базу из полной копии, то достаточно выполнить следующий скрипт:
RESTORE DATABASE [Имя базы данных] FROM DISK = 'Z:\SQLServerBackups\back.bak' WITH REPLACE
В случае, если необходимо восстановить последовательно сначала полную копию, разностные копии и журналы транзакций, тогда необходимо написать следующий SQL скрипт.
RESTORE DATABASE TEST_DB –восстанавливаем полную копию
FROM test_db_full
WITH NORECOVERY;
GO
RESTORE DATABASE TEST_DB –восстанавливаем разностную копию
FROM test_db_diff
WITH FILE = 1,
NORECOVERY;
GO
RESTORE LOG TEST_DB –восстанавливаем журнал транзакций №1
FROM test_db_tran_1
WITH FILE = 1,
WITH NORECOVERY;
GO
RESTORE LOG TEST_DB –восстанавливаем журнал транзакций №2
FROM test_db_tran_2
WITH FILE = 1,
WITH NORECOVERY;
GO
RESTORE DATABASE TEST_DB
WITH RECOVERY;
GO
Для восстановления БД можно использовать так же и SSMS.
