A quick 2 minute upload (with sound, my voice) showing you how easy it is to create an Azure SQL Database using the Azure portal and then using SSMS (SQL Server Management Studio) to connect to it.
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.
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)','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
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.
A quick video clip where I show two things.
Firstly I show how since SQL Server 2014 SP1+ the snapshot is no longer hidden then finally how I check that DBCC CHECKDB has ran successfully.