[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I retrieve past queries that caused blocking

Posted on 2010-09-15
2
Medium Priority
?
553 Views
Last Modified: 2012-06-21
Today, our main SQL 2008 production server experienced major blocking that eventually resulted in serious timeouts for our application. We have Idera installed to monitor the server, but are not running the query monitor because of the excess load it would put on the server. Idera did track that there were about 120 blocked sessions that occurred at one point this morning. Unfortunately, by the time I ran my queries to view the blocked sessions, it finally cleared up. I am trying to find out what those queries were so we can find out what the root of the problem was.

One thing we do is we have transaction log backups that occur every 10 minutes. Is there any way we can capture what was in that transaction log and output it to a file that we can review? I just want to make sure this situation does not occur again.

Any assistance in this matter is greatly appreciated.

0
Comment
Question by:richecker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Expert Comment

by:mnachu
ID: 33683474
You could look at the cached plans to see what queries were recently executed or updated. There is some DMV for it in SQL 2008.

-Nachi
0
 

Accepted Solution

by:
richecker earned 0 total points
ID: 33684467
Nevermind, I was able to find what I was looking for in Idera. In Idera, there is blocking history tracking and I was able to find the process that was blocking everything else. I just had to point back to a previous snapshot, and find the data.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question