Регламентные работы по БД на SQL
Реакция на эту публикацию оказалась очень неоднозначной, в практике встречал многое, но того что высказано в критике не видел. Что конечно не говорит что этого не может быть. Как говорил оин из моих учителей наставников - "Видишь суслика? Нет? А он есть! Так и с ошибками в коде любой программы." Впрочем это все лирика.
Базы данных нынче очень тяжелые, раньше делал регламент по бэкапам средствами SQL каждые 4 часа на базе размером более 20 Gb. Сейчас на более сильном железе это дает ошибки транзакции, что бесит пользователей. Поэтому теперь все делаем ночью. Встречается, что бэкап средствами SQL при разворачивании оказывается нерабочим, поэтому предпочитаю бэкапы средствами 1С (ВНИМАНИЕ: 1С рекомендует бэкапы средствами скуля делать). Скрипт по запуску на сервере прилагаю ниже.
В приложенном файле собрано много чего: работа с файлами (скрипты cmd) и их можно в регламент SQL смело вставлять при необходимости, перезапуск ragent (сервера), собранные материалы из статьи ниже (на всякий случай все проверил на живом\рабочем сервере), решение некоторых ошибок связанных с обновление (БД разрушена, недоступна или БД не выгружается через конфигуратор), кратко памятка основных и часто используемых команд по входу в БД (блокировка, лог, отладка серверных процедур и т.д), регламентные работы на сервере и возврат доступа к БД на сервере. Приложены ссылки на похожие публикации.
Самая упрощенная схема реламента эта бэкап средствами 1С раз в неделю. А обслуживание БД состоит из 4 заданий: Шринкование (вручную или раз в неделю автоматом), реиндексация, обновление статистики, очистка процедураного кэша.
В качестве примера приведена БД "ara2014"
Обслуживание SQL (пример большой статьи по индексам и т.д на SQL):
//soft.crimea.com/public/308762/
+++++++++++++++++++++++++++++++++++++++++++++++++++++
Запуск конфигуратора для создания бэкапа БД 1С (вход в заблокированную БД, файл с пропиской тек. даты, запись лога работы сеанса):
Пользователь Б -"exchange", пароль БД - "exc878787", имя файла с датой - "1Cv8_ARA_%date:~6,4%%date:~3,2%%date:~0,2%.dt", код блокировки (как и пароль как вы указали) - "55513", и имя лога работы сеанса 1С.
"C:\Program Files (x86)\1cv82\common\1cestart.exe" CONFIG /S"winsrv02\ara2014" /N"exchange" /P"exc878787" /DumpIB"C:\1C\Arxiv\1Cv8_ARA_%date:~6,4%%date:~3,2%%date:~0,2%.dt" /UC55513 /Out"C:\1C\Arxiv\LOGBackupARA.txt" -NoTruncate
+++++++++++++++++++++++++++++++++++++++++++++++++++++
Шринкование БД (вручную, если ставите в атоматическое выполнение, то раза в неделю остаточно):
USE [ara2014]
GO
DBCC SHRINKDATABASE(N'ara2014' )
GO
+++++++++++++++++++++++++++++++++++++++++++++++++++++
Реиндексация базы данных (раз в сутки, в не рабочее время) (рекомендовано от 1С):
DECLARE reindex_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN reindex_cursor
DECLARE @tablename sysname
FETCH NEXT FROM reindex_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXECUTE ('DBCC DBREINDEX (''' + @tablename + ''')')
FETCH NEXT FROM reindex_cursor INTO @tablename
END
CLOSE reindex_cursor
DEALLOCATE reindex_cursor
+++++++++++++++++++++++++++++++++++++++++++++++++++++
Обновление статистики базы данных (один или несколько раз в день) (рекомендовано от 1С):
exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'
DBCC UPDATEUSAGE (ara2014)
+++++++++++++++++++++++++++++++++++++++++++++++++++++
Очистка процедураного кэша СУБД (после обновления статистики) (рекомендовано от 1С):
DBCC FREEPROCCACHE
ДОПОЛНИТЕЛЬНЫЕ СКРИПТЫ ОБСЛУЖИВАНИЯ SQL сервера:
Временами, при восстановлении базы из архива может сбиться настройка смещения дат, что чревато серьезными проблемами.
Проверить настройку смещения дат можно следующим скриптом:
SELECT TOP 1 Offset FROM _YearOffset
Для оценки самых тяжелых запросов я использую немного другие скрипты:
1. Топ 10 самых тяжелых для процессора:
SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;
2. Топ 10 самых тяжелых по вводу/выводу:
SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;
Статистика ввода/вывода по файлам баз данных:
USE master
GO
SELECT TOP 10 DB_NAME(saf.dbid) AS [База данных]
, saf.name AS [Логическое имя]
, vfs.BytesRead/1048576 AS [Прочитано (Мб)]
, vfs.BytesWritten/1048576 AS [Записано (Мб)]
, saf.filename AS [Путь к файлу]
FROM sysaltfiles AS saf
JOIN :: fn_virtualfilestats(NULL,NULL) AS vfs
ON vfs.dbid = saf.dbid
AND vfs.fileid = saf.fileid
AND saf.dbid NOT IN (1,3,4)
ORDER BY vfs.BytesRead/1048576 + BytesWritten/1048576 DESC
GO
Статистика ввода/вывода по дискам:
SELECT SUBSTRING(saf.physical_name, 1, 1) AS [Диск]
, SUM(vfs.num_of_bytes_read/1048576) AS [Прочитано (Мб)]
, SUM(vfs.num_of_bytes_written/1048576) AS [Записано (Мб)]
FROM sys.master_files AS saf
JOIN sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
ON vfs.database_id = saf.database_id
AND vfs.file_id = saf.file_id
AND saf.database_id NOT IN (1,3,4)
AND saf.type < 2
GROUP BY SUBSTRING(saf.physical_name, 1, 1)
ORDER BY [Диск]
GO
Производительность журнала транзакций:
SELECT (wait_time_ms - signal_wait_time_ms) / waiting_tasks_count AS [Время отклика долговременного носителя журнала (ms)],
max_wait_time_ms AS [Максимальное время ожидания (ms)]
FROM sys.dm_os_wait_stats
WHERE wait_type = 'WRITELOG' AND waiting_tasks_count > 0;
Многострадальный sys.dm_exec_query_stats можно еще использовать так, топ 30 запросов по длительности блокировки:
SELECT TOP 30
(total_elapsed_time - total_worker_time) / qs.execution_count AS [Average Time Blocked],
total_elapsed_time - total_worker_time AS [Total Time Blocked],
qs.execution_count AS [Execution count],
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS [Individual Query],
qt.text [Parent Query],
DB_NAME(qt.dbid) AS [Database name]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
/* этот кусок отберет по базе, если надо по всем - удалить */
/*WHERE DB_NAME(qt.dbid)='ИмяБазы'*/
ORDER BY [Average Time Blocked] DESC;
Наблюдаем за памятью: если Free Pages меньше 300, объем ОЗУ узкое место в производительности системы:
SELECT * FROM sys.sysperfinfo where counter_name like 'Page Writes%' or counter_name like 'Page reads%'
or counter_name like 'lazy%' or counter_name like 'Page Life%' or counter_name like 'Memory Grants Pending%'
or (counter_name = 'Free pages' and [object_name] LIKE '%BUFFER MANAGER%')
Перекрывающаяся статистика:
WITH autostats ( object_id, stats_id, name, column_id )
AS ( SELECT sys.stats.object_id ,
sys.stats.stats_id ,
sys.stats.name ,
sys.stats_columns.column_id
FROM sys.stats
INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
AND sys.stats.stats_id = sys.stats_columns.stats_id
WHERE sys.stats.auto_created = 1
AND sys.stats_columns.stats_column_id = 1
)
SELECT OBJECT_NAME(sys.stats.object_id) AS [Table] ,
sys.columns.name AS [Column] ,
sys.stats.name AS [Overlapped] ,
autostats.name AS [Overlapping] ,
'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id)
+ '].[' + OBJECT_NAME(sys.stats.object_id) + '].['
+ autostats.name + ']'
FROM sys.stats
INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
AND sys.stats.stats_id = sys.stats_columns.stats_id
INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id
AND sys.stats_columns.column_id = autostats.column_id
INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id
AND sys.stats_columns.column_id = sys.columns.column_id
WHERE sys.stats.auto_created = 0
AND sys.stats_columns.stats_column_id = 1
AND sys.stats_columns.stats_id != autostats.stats_id
AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0
Статистика ожиданий (более информативно чем перекрывающаяся статистика):
WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
)
SELECT
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
[W1].[WaitCount] AS [WaitCount],
CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO
Восстановление баз данных:
Восстановление SQL базы 1С 8.2. рухнувшей во время сохранения конфигурации:
("Внимание!!! При обновлении данных, после последней реструктуризации, произошла критическая ошибка. Повторить обновление?")
Сразу уточним, что конфигуратор не открывается, а если открывается, продолжение обновления вываливается в ошибку. Итак в открытом окне SQL Managment Studio ищем нашу базу - открываем Таблицы, ищем в конце списка таблицу с конфой dbo.config на таблице - правую кнопку - Открыть таблицу. Далее в правом окне спускаемся в самой таблице вниз по алфавиту на поле где FileName = "commit". Встаем на эту запись - правую кнопку мыши - Удалить. В общем удаляем запись с двоичным файлом. Далее пробуем зайти в конфу. Ошибка та же самая первая появляется. Наверно не получилось? Нажимаем Ок. И тут, прежде чем выдать как ранее 2-е сообщение о невозможности сохранить - компьютер задумался. Спустя секунд 30! Конфигуратор открылся. Пробуем сохранить конфигуратор (предварительно сохранив cf файл). Конфигуратор сохраняется. Таким образом и волки сыты и овцы целы.
Не уверен насчет полной работоспосбности базы после таких измывательств - так что посоветую сделать реструтуризацию и пересчет итогов уже потом вечером (предварительно конечно же сделав архив).
----------------------------------------------------
Ошибка SDBL (Возникает при обновлении конфигурации ИБ): Для полей, начиная с FileName, не хватило значений (pos=19)
Лечится путем запуска в консоли SQLсервера команд:
TRUNCATE TABLE _ConfigChangeRec
TRUNCATE TABLE _ConfigChangeRec_ExtProps
----------------------------------------------------
Отладка на сервере
Иногда возникает необходимость отладки серверных процедур 1С при работе с программой в клиент-серверном варианте. По умолчанию отладка на сервере
выключена. Существует, по крайней мере, два способа:
Можно сделать выгрузку конфигурации и развернуть ее в файловом варианте.
Можно включить отладку серверных процедур, запустив агента сервера с параметром debug.
Остановимся на последнем варианте более подробно.Алгоритм примерно следующий:
Останавливаем службу 1C:Enterprise 8.2 Server Agent, например, вот так: "C:\Program Files (x86)\1cv82\8.2.19.130\bin\ragent.exe" –stop
В системном реестре находим ветку «HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\1C:Enterprise 8.2 Server Agent» и для параметра «ImagePath» добавляем «-debug».
Было: "C:\Program Files (x86)\1cv82\8.2.19.130\bin\ragent.exe" -srvc -agent -regport 1541 -port 1540 -range 1560:1591 -d "C:\Program Files (x86)\1cv82\srvinfo"
Стало:"C:\Program Files (x86)\1cv82\8.2.19.130\bin\ragent.exe" -srvc -agent -regport 1541 -port 1540 -range 1560:1591 -d "C:\Program Files (x86)\1cv82\srvinfo" -debug
Запускаем службу.
Запускаем 1С:Предприятие в обычном режиме, заходим в меню «Сервис - Параметры», закладка «Системные». Проверяем, чтобы было отмечено: «Отладка разрешена».
Запускаем 1С:Предприятие в режиме Конфигуратора, заходим в меню «Отладка - Подключение», нажимаем кнопку «Автоматическое подключение», выбираем необходимые типы подключения.
Варианты запуска
***********[ вход в заблокированную базу (файловая\SQL) ]*************************
"C:\Program Files (x86)\1cv8\common\1cestart.exe" ENTERPRISE /F"C:\LocalBase8\Demo\DemoTrd" /UC555
"C:\Program Files (x86)\1cv8\common\1cestart.exe" ENTERPRISE /S"sky-1\trd" /UC555
"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /F"C:\LocalBase8\Demo\DemoTrd" /UC555
"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S"sky-1\trd" /UC555
***********[ выгрузка базы данных ]*************************
"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S[my_server]\[my_base] /N[привелигерованая_учетка] /P[пароль] /DumpIB"[путь_приема_файла]\backup.dt"
move "[путь_приема_файла]\backup.dt" "[путь_приема_файла]\%date%.dt"
"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /F"C:\LocalBase8\Demo\DemoTrd" /N"exchange" /P"exc878787" /DumpIB"C:\NS\Arxiv_1С8\Accounting_%date:~0,2%.%date:~3,2%.%date:~6,4%.dt"
"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S"sky-1\trd" /N"exchange" /P"exc878787" /DumpIB"C:\NS\Arxiv_1С8\Trd\1Cv8_Trd_%date:~0,2%.%date:~3,2%.%date:~6,4%.dt"
// Бэкап заблокированной базы
"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S"sky-1\trd" /UC555 /N"exchange" /P"exc878787" /DumpIB"C:\NS\Arxiv_1С8\Trd\1Cv8_Trd_%date:~0,2%.%date:~3,2%.%date:~6,4%.dt"
***********[ Запуск с записью в лог ]*************************
"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S"sky-1\ara" /UC55513 /Out"E:\Distr_1C8\Update\Error.txt" -NoTruncate
"C:\Program Files (x86)\1cv8\common\1cestart.exe" /UC55513 /Out"E:\Distr_1C8\LOG\Error_%date:~6,4%%date:~3,2%%date:~0,2%.txt" -NoTruncate
В приложенном архиве все это разложено по файлам, собирал долго, как обычно все валялось как попало, но тут возник заказ на выдачу инструкции для подопечной компании, прилось все прилизать. Ну а раз есть все собранное компактно, возникла мысль поделиться таким сборником срочной помощи.
P.S. Регламент обслуживания без правильной настройки сервера толку много не даст и дублировать хорошо написанное не вижу смысла, поэтому очень рекомендую ознакомится с публикацией //soft.crimea.com/public/65955/
