Category Archives: SQL Server Video Clips

[Video] SQL Server Deadlocks

Posted on by
2

SQL Server Deadlocks – Also known as “The deadly Embrace” occurs when there are 2 processes and neither can advance and get the relevant lock it needs because the other process involved is preventing it from getting the resource it requires.

deadlocks

In this quick video clip I create a deadlock scenario and check how to get some deadlock information via system health extended events session.

Below is the T-SQL for querying the system health session. This was against a SQL Server 2014 install so you may need to change the file location.

SELECT top(1)
CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime2') AS [TimeOfDeadlock],
CONVERT(xml, event_data).query('/event/data/value/child::*') AS [DeadlockGraph]
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\system_health*.xel', null,  null, null)
WHERE object_name = 'xml_deadlock_report'
ORDER BY [TimeOfDeadlock] DESC
Advertisement

Setting SQL Server Max Memory Dangerously Low

Posted on by
Reply

Hopefully you know the relevance and importance of setting a correct value for max memory on your SQL Server. By default it will be the value 2147483647 which is not a random number but the 8th Mersenne prime! In a computing sense it is the maximum positive value for a 32-bit signed binary integer and that is big.

Continue reading →

Why did my SQL Server database just go offline?

Posted on by
Reply

Note: This post contains a video clip where I show quirky behaviour of the scripted offline task. Personally I rarely use SQL Server Management Studio GUI to do day-to-day tasks, I rather just load a window and start typing stuff (I learn better that way). Recently I became lazy and just loaded up the tool and found something quite quirky, let me explain.

Continue reading →