If you’ve ever woken up to alerts about sudden application timeouts or slow checkout flows on your e-commerce site, deadlocks are almost certainly one of the first issues you’ll check for. These frustrating conflicts happen when two or more transactions block each other permanently, with neither able to proceed, and SQL Server will automatically kill one of them to break the cycle. The problem is, you can’t fix recurring deadlocks if you only have data from the last five minutes. That’s why knowing how to check deadlock history in SQL Server is one of the most critical skills for any database admin or backend developer. I’ve spent hours sifting through logs after a holiday sale outage a few years back, and the only way I found the root cause was by pulling three days of deadlock history to spot a pattern with conflicting inventory update queries.
How to Check Deadlock History in SQL Server Using Built-in System Views
The fastest way to pull recent deadlock data is by querying SQL Server’s built-in system dynamic management views (DMVs). These views store real-time performance and transaction data directly on the server, so you don’t need to enable any extra tools to access them. If you're looking for deadlocks from the last 24 hours, this method is the fastest way to check deadlock history in SQL Server without switching to other tools.
You'll need VIEW SERVER STATE permission on the SQL Server instance to run these queries, so make sure you have the right access level before you get started. The most useful view for recent deadlocks is sys.dmtrandeadlocks, which will show you the time of the deadlock, the session IDs involved, the resources being locked, and which transaction was chosen as the victim to be terminated. You can join this with sys.dmexecsql_text to pull the exact query each session was running when the deadlock occurred.
The biggest downside of this method is that DMV data is stored in volatile memory. If your server restarts, or if the buffer pool clears old data to make room for new activity, you’ll lose all deadlock history stored in these views. It’s not a good option if you need to look up deadlocks that happened more than a few days prior.
Use Extended Events to Pull Long-Term Deadlock History
For deadlocks older than 48 hours, or if you want to track deadlocks over weeks or months, extended events are the most reliable tool available. Extended events are a lightweight performance tracking system built into SQL Server, and they use far fewer resources than the older deprecated Profiler tool.
The best part is you don’t even need to set up a custom extended event session to start tracking. The default systemhealth session runs automatically on all SQL Server instances, and it captures all deadlock graphs by default. The systemhealth session stores data in rolling .xel files, usually located in the SQL Server LOG directory. These files typically retain data for 1-2 weeks by default, depending on your server activity level. This is the best method if you need to check deadlock history in SQL Server that's older than 48 hours, as system views usually don't retain data that long.
You can query these .xel files directly through SQL Server Management Studio, or write a T-SQL query to parse them into a readable table format. The deadlock graph you get from extended events will show you the full stack of each transaction, the order locks were requested, and even the priority level that determined which transaction was killed. I always use this method when I’m investigating recurring deadlocks, because the level of detail you get makes it easy to spot exactly which queries or indexes are causing the conflict.
Key Details to Prioritize When Analyzing Deadlock History
Once you pull your deadlock history, you don’t need to sift through every line of raw data to find the root cause. Focus on the high-impact details first, and you’ll usually find the issue in minutes instead of hours. The most important details to look for are:
- Deadlock victim selection: Check which transaction was terminated by SQL Server, and what business process it supported (like payment processing or account updates) to measure the real-world impact of the deadlock.
- Lock type and resource: Note if the deadlock was caused by row locks, page locks, or full table locks, and which table or index was the source of the conflict. Full table locks are almost always a sign of a missing index or inefficient query.
- Query execution time: Look for long-running queries that hold locks longer than necessary, as these are the most common trigger for frequent deadlocks during peak traffic hours.
- Frequency pattern: Note if deadlocks happen at the same time every day, like during peak sales hours or scheduled report runs, to spot conflicts with recurring scheduled jobs.
So many teams skip this analysis step and just increase timeout limits or restart the server after a deadlock outage, but that only hides the problem. If you see deadlocks spiking every weekday at 2 PM, for example, you probably have a scheduled revenue report that’s locking entire sales tables while frontline users are trying to process orders.
Common Mistakes to Avoid When Tracking Deadlock History
Even if you use the right tools to pull deadlock data, small mistakes can make your work far less effective, or even cause new issues on your server. The first and most common mistake I see is teams relying on the old SQL Server Profiler tool for deadlock tracking. Don't rely solely on SQL Server Profiler for deadlock tracking — it’s been deprecated since SQL Server 2016, and it uses up to 10x more server resources than extended events. Running it on a busy production instance can cause more performance issues than it fixes.
Another mistake is only looking at the most recent deadlock when troubleshooting. I once worked on a case where a SaaS company kept fixing individual deadlocks as they popped up, but the real issue was a missing index on their user search table that was causing full table scans every time a user looked up a customer record. We only found this when we pulled two weeks of deadlock history and saw 90% of all conflicts were tied to the same search query.
Don't forget to archive deadlock history regularly too. If you set up a custom extended event session to track deadlocks long-term, make sure you have a retention policy in place to delete or archive old .xel files. If you don’t, these files can eat up all available disk space on your server in a few months, especially if you have a high-traffic instance with hundreds of deadlocks a day. Even low-severity deadlocks that kill background jobs shouldn’t be ignored, because they can escalate to major outages as your user base grows.
At the end of the day, knowing how to check deadlock history in SQL Server isn’t just about fixing immediate outages, it’s about proactively making your database more stable for every user. You don’t need expensive third-party monitoring tools to do this either — all the methods we covered use built-in features that are available in every supported version of SQL Server. Next time you get an alert about slow queries or failed transactions, skip the guesswork and pull your deadlock history first. You’ll probably find the root cause in minutes instead of hours, and save yourself from repeat outages down the line.