Шпаргалка по 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

175 комментариев к “Шпаргалка по 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!

  14. This is really fascinating, You’re an excessively professional blogger.
    I have joined your rss feed and look ahead to looking for more of your wonderful post.
    Additionally, I’ve shared your website in my social networks

  15. Hello very nice site!! Guy .. Beautiful .. Amazing ..
    I will bookmark your blog and take the feeds additionally?
    I am glad to search out so many helpful information right here within the post, we want
    develop more techniques in this regard, thank you for
    sharing. . . . . .

  16. My developer is trying to convince me to move to .net from PHP.
    I have always disliked the idea because of the costs.

    But he’s tryiong none the less. I’ve been using WordPress on several websites for about a year and am anxious about switching to another platform.
    I have heard good things about blogengine.net.
    Is there a way I can transfer all my wordpress posts into it?
    Any help would be really appreciated!

  17. First off I would like to say awesome blog! I had a quick question in which I’d like to ask if you don’t mind. I was interested to know how you center yourself and clear your mind before writing. I’ve had a difficult time clearing my mind in getting my ideas out there. I do enjoy writing however it just seems like the first 10 to 15 minutes tend to be lost simply just trying to figure out how to begin. Any ideas or tips? Kudos!

  18. Thanks for your personal marvelous posting! I genuinely enjoyed reading it, you could be a great author.I will make certain to bookmark your blog and definitely will come back someday. I want to encourage you to definitely continue your great job, have a nice day!

  19. My coder is trying to convince me to move to .net from PHP. I have always disliked the idea because of the expenses. But he’s tryiong none the less. I’ve been using WordPress on several websites for about a year and am anxious about switching to another platform. I have heard good things about blogengine.net. Is there a way I can transfer all my wordpress content into it? Any kind of help would be really appreciated!

  20. Hey, I think your site might be having browser compatibility issues. When I look at your blog site in Safari, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, awesome blog!

  21. With havin so much content and articles do you ever run into any problems of plagorism or copyright violation? My blog has a lot of unique content I’ve either created myself or outsourced but it seems a lot of it is popping it up all over the internet without my agreement. Do you know any ways to help stop content from being ripped off? I’d certainly appreciate it.

  22. Hello there! I know this is kind of off topic but I was wondering if you knew where I could get a captcha plugin for my comment form? I’m using the same blog platform as yours and I’m having trouble finding one? Thanks a lot!

  23. The other day, while I was at work, my sister stole my iphone and tested to see if it can survive a forty foot drop, just so she can be a youtube sensation. My apple ipad is now broken and she has 83 views. I know this is entirely off topic but I had to share it with someone!

  24. Pretty section of content. I just stumbled upon your web site and in accession capital to assert that I get in fact enjoyed account your blog posts.
    Any way I’ll be subscribing to your feeds and even I achievement
    you access consistently fast.

  25. wonderful put up, very informative. I’m wondering why the opposite experts of this sector don’t notice this.
    You should continue your writing. I’m confident, you
    have a huge readers’ base already!

  26. Magnificent items from you, man. I’ve be mindful your stuff previous to and you’re simply extremely great.

    I actually like what you have obtained here, certainly like what you’re saying and
    the way in which wherein you say it. You are making it entertaining and you still take care of to stay it wise.
    I can not wait to read much more from you. This is actually a wonderful website.

  27. Having read this I believed it was rather enlightening.
    I appreciate you finding the time and energy to
    put this short article together. I once again find myself personally spending a
    lot of time both reading and leaving comments.
    But so what, it was still worthwhile!

  28. Thanks for a marvelous posting! I truly enjoyed reading it, you can be a great author.I will be sure to bookmark your blog and will come back in the foreseeable future.
    I want to encourage continue your great job, have a nice evening!

  29. It is perfect time to make some plans for the long run and it is time to
    be happy. I have read this publish and if I could I want to suggest you few attention-grabbing issues or advice.

    Maybe you could write next articles regarding this article.
    I want to learn more issues approximately it!

  30. Hey there! Quick question that’s totally off topic.
    Do you know how to make your site mobile friendly? My website looks weird when browsing from my iphone4.
    I’m trying to find a template or plugin that might be
    able to correct this problem. If you have any recommendations, please share.
    Many thanks!

  31. Hi, I do believe this is a great web site. I stumbledupon it
    😉 I will revisit yet again since I book marked it.

    Money and freedom is the greatest way to change, may you be rich and continue to help other people.

  32. Today, I went to the beachfront with my kids. I found a sea shell and gave it
    to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed.
    There was a hermit crab inside and it pinched her ear.
    She never wants to go back! LoL I know this is completely
    off topic but I had to tell someone!

  33. Its such as you read my thoughts! You seem to grasp a lot approximately this,
    such as you wrote the e book in it or something.
    I feel that you could do with a few percent to power the message house
    a little bit, but other than that, that is magnificent blog.
    An excellent read. I’ll certainly be back.

  34. Hello there! I could have sworn I’ve been to this website before but after browsing through some of the post I realized it’s new to me.
    Anyhow, I’m definitely happy I found it and I’ll be book-marking and checking back
    frequently!

  35. Hey I am so grateful I found your blog, I really found you by mistake, while I was browsing on Askjeeve for something else, Anyways I am
    here now and would just like to say cheers for a remarkable post and a all round interesting blog (I
    also love the theme/design), I don’t have time to read through it all
    at the moment but I have bookmarked it and also included your RSS
    feeds, so when I have time I will be back to read a lot more, Please do keep up
    the awesome b.

  36. It’s appropriate time to make some plans for the longer term
    and it’s time to be happy. I have read this submit and if I may just I desire
    to suggest you few fascinating issues or advice. Maybe you could write subsequent articles relating to this article.
    I want to read even more issues approximately it!

  37. Howdy! Quick question that’s completely off topic. Do you
    know how to make your site mobile friendly? My website looks weird
    when viewing from my iphone4. I’m trying to find a theme or plugin that might
    be able to fix this problem. If you have any suggestions, please share.
    Thank you!

  38. Hello there! Do you know if they make any plugins to help with Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good results. If you know of any please share. Thank you!

  39. I really like your blog.. very nice colors & theme. Did you create this website yourself or did you hire someone to do it for you? Plz respond as I’m looking to design my own blog and would like to find out where u got this from. many thanks

  40. Aw, this was a really good message. In idea I wish to put in creating similar to this additionally? taking some time and actual effort to make an excellent article? however what can I say? I procrastinate alot as well as by no means appear to get something done.

  41. Woah! I’m really loving the template/theme of this blog. It’s simple, yet effective. A lot of times it’s very difficult to get that “perfect balance” between user friendliness and appearance. I must say you’ve done a awesome job with this. In addition, the blog loads very quick for me on Firefox. Superb Blog!

  42. Greetings! Quick question that’s completely off topic. Do you know how to make your site mobile friendly? My web site looks weird when browsing from my iphone. I’m trying to find a template or plugin that might be able to fix this problem. If you have any recommendations, please share. Many thanks!

  43. Please let me know if you’re looking for a article writer for your blog. You have some really good articles and I believe I would be a good asset. If you ever want to take some of the load off, I’d absolutely love to write some material for your blog in exchange for a link back to mine. Please send me an email if interested. Cheers!

  44. Hey! I understand this is sort of off-topic but I needed to ask. Does running a well-established blog such as yours take a lot of work? I am completely new to operating a blog but I do write in my journal on a daily basis. I’d like to start a blog so I will be able to share my personal experience and views online. Please let me know if you have any ideas or tips for new aspiring bloggers. Appreciate it!

  45. Hmm is anyone else experiencing problems with the pictures on this blog loading? I’m trying to determine if its a problem on my end or if it’s the blog. Any feedback would be greatly appreciated.

  46. Interesting blog! Is your theme custom made or did you download it from somewhere? A theme like yours with a few simple adjustements would really make my blog stand out. Please let me know where you got your theme. Appreciate it

  47. I know this if off topic but I’m looking into starting my own blog and was curious what all is required to get set up? I’m assuming having a blog like yours would cost a pretty penny? I’m not very web savvy so I’m not 100 certain. Any recommendations or advice would be greatly appreciated. Many thanks

  48. Hi! I understand this is kind of off-topic however I needed to ask. Does building a well-established blog like yours require a large amount of work? I am brand new to operating a blog but I do write in my diary daily. I’d like to start a blog so I can easily share my personal experience and thoughts online. Please let me know if you have any kind of ideas or tips for new aspiring bloggers. Thankyou!

  49. Hmm it seems like your website ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I too am an aspiring blog blogger but I’m still new to everything. Do you have any tips and hints for rookie blog writers? I’d definitely appreciate it.

  50. This design is steller! You definitely know how to keep a reader amused. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Fantastic job. I really enjoyed what you had to say, and more than that, how you presented it. Too cool!

  51. Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point. You definitely know what youre talking about, why throw away your intelligence on just posting videos to your blog when you could be giving us something informative to read?

  52. Hello there! Quick question that’s completely off topic. Do you know how to make your site mobile friendly? My weblog looks weird when browsing from my apple iphone. I’m trying to find a theme or plugin that might be able to fix this problem. If you have any recommendations, please share. Many thanks!

  53. Howdy! This is kind of off topic but I need some advice from an established blog. Is it difficult to set up your own blog? I’m not very techincal but I can figure things out pretty quick. I’m thinking about setting up my own but I’m not sure where to start. Do you have any points or suggestions? Many thanks

  54. Have you ever thought about including a little bit more than just your articles? I mean, what you say is fundamental and everything. Nevertheless just imagine if you added some great graphics or video clips to give your posts more, “pop”! Your content is excellent but with pics and video clips, this site could certainly be one of the very best in its niche. Excellent blog!

  55. I’m really loving the theme/design of your website. Do you ever run into any web browser compatibility issues? A couple of my blog readers have complained about my website not operating correctly in Explorer but looks great in Chrome. Do you have any ideas to help fix this problem?

  56. Excellent blog you have here but I was curious if you knew of any community forums that cover the same topics talked about in this article? I’d really love to be a part of online community where I can get responses from other experienced individuals that share the same interest. If you have any recommendations, please let me know. Thanks!

  57. There are certainly a great deal of details like that to take into account. That is a fantastic indicate raise. I use the ideas over as general inspiration however plainly there are concerns like the one you raise where the most essential thing will be operating in truthful good faith. I don?t know if finest techniques have arised around things like that, however I make sure that your task is plainly recognized as an up for grabs. Both children and also ladies feel the effect of simply a moment?s satisfaction, for the remainder of their lives.

  58. I just want to mention I am very new to blogs and certainly savored your website. Most likely I’m planning to bookmark your website . You certainly have terrific articles. Thanks a bunch for sharing your blog.

  59. I just want to say I’m newbie to blogging and really liked your web site. Likely I’m likely to bookmark your site . You surely come with great well written articles. Thank you for sharing with us your blog.

  60. It’s a shame you don’t have a donate button! I’d certainly donate to this superb blog! I suppose for now i’ll settle for bookmarking and adding your RSS feed to my Google account. I look forward to fresh updates and will talk about this blog with my Facebook group. Talk soon!

  61. Its like you read my mind! You appear to know a lot about this, like you wrote the book in it or something. I think that you could do with a few pics to drive the message home a little bit, but instead of that, this is fantastic blog. A fantastic read. I will certainly be back.

  62. Today, I went to the beach with my children. I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is entirely off topic but I had to tell someone!

  63. What i don’t understood is in reality how you are now not actually a lot more well-appreciated than you may be right now. You are very intelligent. You already know thus significantly with regards to this matter, produced me in my opinion imagine it from so many various angles. Its like women and men don’t seem to be interested except it¡¦s one thing to do with Girl gaga! Your own stuffs excellent. Always take care of it up!

  64. Hi, Neat post. There’s an issue with your site in internet explorer, could test this… IE still is the marketplace chief and a huge section of other folks will leave out your fantastic writing due to this problem.

  65. I have been browsing online greater than three hours as of late, but I by no means discovered any interesting article like yours. It is beautiful price sufficient for me. In my opinion, if all webmasters and bloggers made excellent content as you did, the web might be a lot more useful than ever before.

  66. I just want to say I am new to blogging and site-building and honestly liked you’re blog. Very likely I’m want to bookmark your website . You actually have fabulous writings. Thank you for sharing your webpage.

  67. Hello! I know this is kinda off topic however , I’d figured I’d ask. Would you be interested in exchanging links or maybe guest writing a blog article or vice-versa? My website goes over a lot of the same subjects as yours and I believe we could greatly benefit from each other. If you might be interested feel free to shoot me an email. I look forward to hearing from you! Fantastic blog by the way!

  68. My personal sis recommended me personally regarding your web website and exactly how excellent it is. She’s proper, I am genuinely impressed using the composing and slick design. It appears in my experience you’re simply scratching the surface when it comes to what you might achieve, however you’re off to a good start!

  69. Howdy, I think your blog could be having internet browser compatibility issues. When I look at your website in Safari, it looks fine however when opening in I.E., it’s got some overlapping issues. I merely wanted to provide you with a quick heads up! Apart from that, great website.

  70. There are some attention-grabbing closing dates in this article however I don’t know if I see all of them heart to heart. There is some validity but I’ll take maintain opinion until I look into it further. Good article , thanks and we would like extra! Added to FeedBurner as nicely

  71. Having read this I thought it was really informative. I appreciate you spending some time and effort to put this informative article together. I once again find myself spending way too much time both reading and commenting. But so what, it was still worth it!

  72. Thank you for sharing superb informations. Your site is so cool. I’m impressed by the details that you¡¦ve on this website. It reveals how nicely you perceive this subject. Bookmarked this website page, will come back for extra articles. You, my friend, ROCK! I found simply the info I already searched everywhere and simply could not come across. What an ideal web site.

  73. I blog quite often and I genuinely thank you for your content. The article has truly peaked my interest. I will bookmark your website and keep checking for new information about once a week. I subscribed to your Feed too.

  74. Please let me know if you’re looking for a writer for your weblog. You have some really good articles and I feel I would be a good asset. If you ever want to take some of the load off, I’d love to write some content for your blog in exchange for a link back to mine. Please shoot me an e-mail if interested. Cheers!

  75. With havin so much written content do you ever run into any problems of plagorism or copyright violation? My blog has a lot of completely unique content I’ve either written myself or outsourced but it seems a lot of it is popping it up all over the internet without my permission. Do you know any methods to help reduce content from being stolen? I’d definitely appreciate it.

  76. Can I simply just say what a comfort to uncover someone that genuinely knows what they’re talking about on the net. You definitely know how to bring a problem to light and make it important. A lot more people must look at this and understand this side of your story. I was surprised you aren’t more popular since you certainly have the gift.

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

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