Шпаргалка по deadlock в MSSQL

Включить трассировку

DBCC TRACEON (1222,-1)

-1 = сбор информации изо всех сессий.

Проверить трассировку

DBCC TRACESTATUS (-1)

или

DBCC TRACEON(1204)
DBCC TRACEON(3604)
DBCC TRACEON(3605)

логи о дедлоках будут писаться в errorlog сервера

Рекомендации:

1.Перед включением трассировки настроить сохранение логов сервера, скажем, не 6, а 24 и более. Это делается в EM, Management / SQL Server logs / Right mouse click / Configure / [X] Limit… Maximem number of поставить, скажем, в 30.

2.Настроить на планировщике ежедневный вызов sp_cycle_errorlog – эта процедура вызывает сброс текущего лога.

После того, как будут пойманы 1 – 2 – 3 дедлока выключить трассировку:

DBCC TRACEOFF(1204)
DBCC TRACEOFF(3604)
DBCC TRACEOFF(3605)

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

  • 1204 – сбор расширенной информации о взаимоблокировке.
  • 3605 – выдача информации в EventLog.
  • 3406 – выдача информации в файл errorlog.
  • 1206 – сбор информации не только о блокировках, участвующих во тупиковой ситуации (что делает флаг 1204), но и об остальных блокировках, наложенных заблокированными транзакциями.
  • 1200 – сбор информации о порядке наложения блокировок (недокументированный).

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

1. Запустить SQL Profiler, специальную программу для отслеживания работы сервера, и настроить в ней перехват ошибок (event class Errors and Warnings: Exception and Error Log), а затем выставить флаг трассировки 3605. В этом случае вся дополнительная информация о работе SQL-сервера будет сбрасываться в Event Log и перехватываться профайлером, где ее в последствии можно будет посмотреть.
2. Выставить флаг отладки 3406. В этом случае вся дополнительная информация будет сбрасываться в файл errorlog, который по умолчанию находится в каталоге LOG директории SQL сервера.

PAG: 7:1:845557
DBCC PAGE ({dbid | dbname}, filenum, pagenum[, printopt]) в результатах вывода которой можно увидеть object_id (а по нему уже и мя объекта получить)

Полезные ссылки

http://www.sql.ru/articles/mssql/2007/011005DeadlockTroubleshootingPart1.shtml
http://rsdn.ru/article/db/deadlocks.xml

31 комментарий к “Шпаргалка по deadlock в MSSQL

  1. I?m satisfied, I need to say. Truly seldom do I run into a blog site that?s both educative and amusing, as well as let me inform you, you have struck the nail on the head. Your suggestion is impressive; the problem is something that insufficient individuals are speaking smartly around. I am extremely pleased that I came across this in my search for something relating to this.

  2. Can I simply say what a relief to uncover a person that genuinely understands what they’re discussing on the internet. You certainly understand how to bring a problem to light and make it important. More people should check this out and understand this side of the story. I can’t believe you aren’t more popular since you most certainly possess the gift.

  3. There are some interesting points in this short article but I don?t recognize if I see all of them center to heart. There is some legitimacy however I will certainly hold viewpoint until I check into it even more. Great write-up, many thanks and we desire a lot more! Contributed to FeedBurner as well

  4. There are some intriguing points in time in this short article however I don?t understand if I see every one of them center to heart. There is some credibility but I will certainly hold point of view until I consider it further. Good write-up, many thanks as well as we want extra! Contributed to FeedBurner also

  5. Admiring the commitment you put into your site and in depth information you present. It’s good to come across a blog every once in a while that isn’t the same outdated rehashed information. Wonderful read! I’ve saved your site and I’m including your RSS feeds to my Google account.

  6. After research study a few of the article on your site now, as well as I absolutely like your means of blog writing. I bookmarked it to my book marking web site list and also will be inspecting back soon. Pls have a look at my web site as well and let me know what you think.

  7. Hey are using WordPress for your site platform? I’m new to the blog
    world but I’m trying to get started and create my own. Do
    you require any coding knowledge to make your own blog?
    Any help would be really appreciated! 2CSYEon cheap flights

  8. Hello, Neat post. There is an issue with your site in internet explorer, may check this?
    IE nonetheless is the market leader and a huge component
    to other people will leave out your wonderful writing because of this problem.

  9. You really make it appear so easy together with your presentation however I find this matter to be really one
    thing which I believe I’d by no means understand.
    It seems too complicated and extremely huge for me.
    I am looking ahead for your next submit, I will
    try to get the cling of it!

  10. When I originally commented I clicked the -Notify me when brand-new comments are added- checkbox as well as now each time a remark is added I obtain four e-mails with the same comment. Exists any way you can eliminate me from that solution? Many thanks!

  11. An outstanding share, I just given this onto a colleague who was doing a little evaluation on this. And he in fact bought me breakfast since I located it for him. smile. So let me reword that: Thnx for the reward! However yeah Thnkx for investing the moment to discuss this, I really feel strongly about it and like reading more on this topic. When possible, as you end up being expertise, would certainly you mind upgrading your blog with even more information? It is highly handy for me. Large thumb up for this article!

  12. There are some interesting points in this write-up but I don?t recognize if I see every one of them facility to heart. There is some validity however I will certainly take hold point of view till I explore it further. Excellent article, many thanks and we want extra! Added to FeedBurner also

  13. This is the ideal blog site for any person who intends to learn about this subject. You understand so much its nearly difficult to argue with you (not that I really would want?HaHa). You certainly placed a brand-new spin on a topic thats been blogged about for years. Fantastic things, simply excellent!

Оставить ответ

Ваш адрес email не будет опубликован. Обязательные поля помечены *