Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Понадобилось тут изменить существующий ddl триггер на сервер.
Ранее с ними не работал.

При попытке сделать alter долго висел, срубил процесс.
Пробовал делать dasabled, потом alter - без разницы.
Пошел по другому пути:
Решил создать другой макимально простой триггер:
CREATE TRIGGER [DB_test]
ON ALL SERVER 
FOR alter_table 
AS

print(1)


1й раз также 2.35 висел в suspended, потом отвалился по дедлоку.
На 2й попытке висел 18.26 и так же отвалился по дедлоку: Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Версия сервера: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

Я что-то не так делаю?
---
Проходя мимо разложенных граблей, ты теряешь драгоценный опыт. (с)
13 окт 17, 17:59    [20868413]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
Megabyte,

у вас там работа кипит, отключите всех юзеров и делайте спокойно. Например протокол удаленных подключений отключите или на файрволе правило.
13 окт 17, 18:07    [20868423]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Владислав Колосов
Megabyte,

у вас там работа кипит, отключите всех юзеров и делайте спокойно. Например протокол удаленных подключений отключите или на файрволе правило.

Нельзя, у нас круглосуточная работа. Вечером, конечно, меньше подключений.

Что, ddl триггеру ресурсов, что ли, не хватает?
13 окт 17, 18:30    [20868471]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Megabyte
Владислав Колосов
Megabyte,

у вас там работа кипит, отключите всех юзеров и делайте спокойно. Например протокол удаленных подключений отключите или на файрволе правило.

Нельзя, у нас круглосуточная работа. Вечером, конечно, меньше подключений.

Что, ddl триггеру ресурсов, что ли, не хватает?
На каком ресурсе ожидание?
13 окт 17, 18:32    [20868474]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Гавриленко Сергей Алексеевич
Megabyte
пропущено...

Нельзя, у нас круглосуточная работа. Вечером, конечно, меньше подключений.

Что, ddl триггеру ресурсов, что ли, не хватает?
На каком ресурсе ожидание?

Тип ожидания какой? Не обратил внимания. Запущу еще раз, посмотрю.
Заметил только, что cpu и диск использует средне, я и потяжелее процессы видел.
13 окт 17, 18:37    [20868478]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Сессия имеет статус suspeded когда кого-то и чего-то ждет.
Кого именно и на каком ресурсе видно в sys.dm_os_waiting_tasks.
13 окт 17, 18:42    [20868483]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Гавриленко Сергей Алексеевич
Сессия имеет статус suspeded когда кого-то и чего-то ждет.
Кого именно и на каком ресурсе видно в sys.dm_os_waiting_tasks.

Посмотрю.
13 окт 17, 19:15    [20868515]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
В общем, sys.dm_os_waiting_tasks.resource_description постоянно меняется.

У меня один запрос:
select *
from sys.dm_os_waiting_tasks
where session_id = 1114
-- id моего процесса
Примеры результатов:
waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description
0x0000000000743B88 1114 0 4 PAGEIOLATCH_SH 0x00000000DAFD0A40 NULL NULL NULL 14:1:272532

waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description
0x0000000000743B88 1114 0 12 PAGEIOLATCH_SH 0x0000000168FB79C0 NULL NULL NULL 14:1:30251

waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description
0x0000000000743B88 1114 0 36 PAGEIOLATCH_SH 0x0000000229FBB740 NULL NULL NULL 14:1:769599

Другой на расшифровку resource_description(прим. resource_description = '14:1:730139'):
SELECT /*wt.session_id, wt.wait_type, wt.wait_duration_ms
, */s.name AS schema_name
, o.name AS object_name
, i.name AS index_name
FROM sys.dm_os_buffer_descriptors bd
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
where bd.database_id = 14
and bd.file_id = 1
and bd.page_id = 730139

2й запрос нашел в инете, почти всегда пусто показывает.

Пробовал ранее его в таком варианте:
SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms
, s.name AS schema_name
, o.name AS object_name
, i.name AS index_name
FROM sys.dm_os_buffer_descriptors bd
JOIN (
SELECT *
  , CHARINDEX(':', resource_description) AS file_index
  , CHARINDEX(':', resource_description
  , CHARINDEX(':', resource_description)) AS page_index
  , resource_description AS rd
FROM sys.dm_os_waiting_tasks wt
WHERE session_id = 1114
) AS wt
ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
AND bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index)
AND bd.page_id = SUBSTRING(wt.rd, wt.page_index, LEN(wt.rd))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id

Тоже пустой и долго работает.

Как-то запускал создание триггера вечером, когда нагрузка меньше, процесс иногда переходил в состояние runnable.

p.s. В инете искал что-то про подобные проблемы, не нашел...
18 окт 17, 11:15    [20878368]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
Megabyte,

Вы не сможете изменить триггер, пока кто-то будет держать объекты которые используют триггера. А у вас там кипит работа.
18 окт 17, 11:18    [20878384]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Владислав Колосов
Megabyte,

Вы не сможете изменить триггер, пока кто-то будет держать объекты которые используют триггера. А у вас там кипит работа.

Сейчас я пытаюсь сделать новый простой триггер:
CREATE TRIGGER [DB_test]
ON ALL SERVER 
FOR alter_table 
AS

print(1)

Ему что может мешать?
18 окт 17, 11:24    [20878403]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
o-o
Guest
поди в мастере выполняете ваш "запрос на расшифровку"?
а база-то 14-ая, никак не мастер.
сперва получите базу, потом в ее контексте ищите свой объект.

мне обычно хватает и вот такого,
но если оч.хочется, то можно конечно джойнить схемы-индексы:
select db_name(14);
use ...;

SELECT object_id(p.object_id) as obj
FROM sys.dm_os_buffer_descriptors bd
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
where bd.database_id = 14
and bd.file_id = 1
and bd.page_id = 730139
18 окт 17, 11:28    [20878424]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
o-o
Guest
Megabyte
Владислав Колосов
Megabyte,

Вы не сможете изменить триггер, пока кто-то будет держать объекты которые используют триггера. А у вас там кипит работа.

Сейчас я пытаюсь сделать новый простой триггер:
CREATE TRIGGER [DB_test]
ON ALL SERVER 
FOR alter_table 
AS

print(1)

Ему что может мешать?

что на кофейной гуще-то гадаете, смотрите ожидания снова.
читает какую-то лабуду у вас, PAGEIOLATCH_SH,
ищите, что читает
18 окт 17, 11:30    [20878433]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
o-o
Guest
и почему бы заодно не смотреть, что за bd.page_type?
вдруг это какой-то LOB.
зачем вообще DDL- триггер на сервер лезет в какую-то 14-ую базу?
18 окт 17, 11:33    [20878452]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Поймал resource_description = 'ridlock fileid=1 pageid=74919 dbid=4 id=lockcdac6e00 mode=X associatedObjectId=72057594063945728'.

Нажал отмену запроса, откат шел минут 20, потом отвалился по дедлоку.
И один из прогеров прислал ошибку(дедлок), где он пытался сделать create таблицы(хотя у меня триггер на alter был).

Выходит, что прав Владислав Колосов , что эти триггеры надо создавать при отключении всех от базы?

з.ы. хотя мне что-то не верится, что это так. Как-то предыдущий разработчик создал наш уже существующий триггер без остановки всего сервера...
18 окт 17, 12:05    [20878599]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
o-o
зачем вообще DDL- триггер на сервер лезет в какую-то 14-ую базу?

Я тоже этого не понимаю. :)
Это одна из пользовательских баз. Видел, он лез и в другие пользовательские базы(7, 9).
Поэтому и сложилось впечатление, что делаю что-то не так...
18 окт 17, 12:28    [20878679]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Megabyte,

т.е. сздаете триггер для всего сервера и удивляетесь что лезет в базы?
18 окт 17, 12:35    [20878722]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
TaPaK
Megabyte,

т.е. сздаете триггер для всего сервера и удивляетесь что лезет в базы?

Так в том и вопрос: Что, надо реально все коннекты обрубать, чтоб сделать такой ddl-триггер?
Как тогда его делают на сервере, где работа 7х24???

з.ы. Запустил на локальном сервере, таки быстро сделался.
18 окт 17, 12:42    [20878758]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Eleanor
Member

Откуда:
Сообщений: 2867
Megabyte,

Согласуйте с руководством окно на регламентные работы на 5 минут. Руководство оповестит всех клиентов о регламентных работах, заглушки на сайты вывесит и пр.
Потом спокойно выгоните всех и обновите свой триггер.
18 окт 17, 13:15    [20878887]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Eleanor
Megabyte,

Согласуйте с руководством окно на регламентные работы на 5 минут. Руководство оповестит всех клиентов о регламентных работах, заглушки на сайты вывесит и пр.
Потом спокойно выгоните всех и обновите свой триггер.

Да это мне как-то придется все заглушки делать, если что. :)

Пробую на дублирующем сервере(развернуты вчерашние бэкапы) сделать сначала. Там, по идее, нет коннектов. Простой триггер сделался быстро. А вот alter сущетвующего все равно висит.
Код реального триггера:
ALTER TRIGGER [DB_Changes]
ON ALL SERVER 
--ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS

set ansi_padding on -- Shur 20130621 need for xml 
set concat_null_yields_null on -- Shur 20130621 need for xml 

DECLARE @data XML
/*
<EVENT_INSTANCE>
    <EventType>type</EventType>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name</ServerName>
    <LoginName>name</LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>
    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>
    <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>
*/
declare @EventType varchar(128)
declare @DatabaseName varchar(128)
declare @SchemaName varchar(128)
declare @ObjectName varchar(128)
declare @ObjectType varchar(128)
declare @SQLText nvarchar(max) 
declare @LoginName varchar(256)
declare @UserName varchar(256)
declare @HostName varchar(128)

SET @data = EVENTDATA()
Select 
 @EventType     = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')  
,@DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')  
,@SchemaName   = @data.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')  
,@ObjectName   = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType   = @data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
,@SQLText      = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')
,@LoginName    = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)') -- Shur 20120927
,@UserName     = @data.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(256)') -- Shur 20120927 -- dbo
,@HostName     = HOST_NAME()  -- Shur 20130514

if @EventType in ('ALTER_EXTENDED_PROPERTY','GRANT_DATABASE', 'CREATE_TABLE', 'ALTER_TABLE', 'DROP_TABLE') return -- Shur 20130516
--if @EventType in ('ALTER_INDEX') and @SQLText like '%REBUILD' return -- Shur 20130516

set @ObjectName = @SchemaName+'.'+@ObjectName
if @DatabaseName <> 'master' 
--and @HostName = 'IT-6'
and @ObjectName not like 'Tmp_%' -- when opens Table for edit, SQL MMS creates new table with prefix 'Tmp_'
begin
   --if @HostName = 'IT-6'
   --   print 'EXEC master.dbo.db_CheckChanges '''+@DatabaseName+''','''+@ObjectName+''',0,'''+@LoginName+''','''+@HostName+''''
   EXEC master.dbo.db_CheckChanges @DatabaseName,@ObjectName,0,@LoginName,@HostName
   if @EventType = 'RENAME'
   begin 
		declare @NewName varchar(max)
		set @NewName = substring(@SQLText,CHARINDEX('@newname = N''',@SQLText)+13,LEN(@SQLText))
		set @NewName = substring(@NewName,1,CHARINDEX('''',@NewName)-1)
      --print 'EXEC master.dbo.db_CheckChanges '''+@DatabaseName+''','''+@NewName+''',0,'''+@LoginName+''','''+@HostName+''''
      EXEC master.dbo.db_CheckChanges @DatabaseName,@NewName,0,@LoginName,@HostName
   end
end

в master.dbo.db_CheckChanges у нас там логируются операции DDL.
18 окт 17, 13:53    [20879011]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Megabyte, у Вас версия 2008R2 SP2, поэтому попробуйте сервер запускать с флагом -T2470 (Fixes performance problem when using AFTER triggers on partitioned tables).
18 окт 17, 14:24    [20879171]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
Andy_OLAP
Megabyte, у Вас версия 2008R2 SP2, поэтому попробуйте сервер запускать с флагом -T2470 (Fixes performance problem when using AFTER triggers on partitioned tables).


Вы хоть в курсе что делает этот TF или просто так от балды пишите увидев слово "trigger"?
Давайте для чистоты эксперимента еще перед созданием триггера будем сервер перезагружать точно быстрее будет - пользователи возможно не успеют залезть обратно.
18 окт 17, 14:54    [20879310]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
felix_ff
Andy_OLAP
Megabyte, у Вас версия 2008R2 SP2, поэтому попробуйте сервер запускать с флагом -T2470 (Fixes performance problem when using AFTER triggers on partitioned tables).


Вы хоть в курсе что делает этот TF

В скобках после флага написана расшифровка. Полагаю, что в 2008R2 в случае больших секционированных таблиц любая работа с триггерами по какой-то внутренней причине затормаживается. Полагаю, что в 2012 это исправлено. Полагаю, что от попытки поработать с этим флагом ничего плохого не случится. Может быть, будет лучше, может быть, так же, как раньше.
P.S. А что, танцы с бубнами при работе с MSSQL уже отменили?
18 окт 17, 14:59    [20879344]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
Megabyte,

dm_os_buffer_descriptors показывает то что сейчас в баффер пуле
возможно поэтому не находите.

dbcc traceon (3604);
dbcc page(14, 1, 272532, 0);
dbcc page(14, 1, 30251, 0);
dbcc page(14, 1, 769599, 0);
dbcc traceoff (3604);


в Metadata: ObjectId = {something_id} будет id объекта.
use {db_name_with_id_14};
select object_name({something_id_from_dbcc_page});


у вас ddl тригер, скорее всего ожидание на системные таблицы (sysschobjs?)
единственное странное что происходит ожидание латча, или у вас огромное количество объектов в базе или существуют постоянные операции изменения схемы.
случаем уведомления о событиях или уведомлений запросов не используется в базе с id 14? можете добавить XEvent с отловом события object_altered, object_created, object_deleted и посмотреть количество событий
18 окт 17, 15:49    [20879529]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
o-o
Guest
Andy_OLAP
Megabyte, у Вас версия 2008R2 SP2, поэтому попробуйте сервер запускать с флагом -T2470 (Fixes performance problem when using AFTER triggers on partitioned tables).

а еще лучше со всеми возможными и невозможными флагами.
авось, какой-то поможет.
----
Козлов, велкам.
для сведения: у меня аллергия на все стадо.
лучше не доставай своей фигней без особой надобности,
как только у меня появится время на чтение фигни,
я ее непременно сопровожу соответствующими комментариями
18 окт 17, 16:01    [20879572]     Ответить | Цитировать Сообщить модератору
 Re: Create alter ddl trigger долго висит в suspended  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Andy_OLAP
felix_ff
пропущено...


Вы хоть в курсе что делает этот TF

В скобках после флага написана расшифровка. Полагаю, что в 2008R2 в случае больших секционированных таблиц любая работа с триггерами по какой-то внутренней причине затормаживается. Полагаю, что в 2012 это исправлено. Полагаю, что от попытки поработать с этим флагом ничего плохого не случится. Может быть, будет лучше, может быть, так же, как раньше.
P.S. А что, танцы с бубнами при работе с MSSQL уже отменили?

Секционированных таблиц нет в Standart edition.
19 окт 17, 10:56    [20881532]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить