A very common requirement which can be satisfied by various tools. Personally I like using Visual Studio 2017 Community Edition and I thought I would do a quick overview of 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
If you do – shame on you and shame on me because I do.
So the decision to move to the cloud has been made but there is a fear from people that once it happens they will lose control of their job function. Does this sound familiar? DBA’s, you will be needed but expect some sort of change (That’s my opinion).
A quick elementary post which is my entry to this months’ T-SQL Tuesday entry hosted by a good friend SQLDoubleG http://www.sqldoubleg.com/2017/07/03/tsql2sday-92-lessons-learned-the-hard-way/.
We are here to talk about mistakes we used to make. There is one mistake that I am going to discuss and is something that I used to do 10 years ago, obviously I do not do this anymore.
I had a messy (another one) day, last thing I wanted was a “broken” SQL Server instance where I was faced with multiple “service terminated unexpectedly” messages. I thought it would make a decent blog post to share with you all.
A very quick post for today, recently I have been working on some code to gather metrics around SQL Server memory, more specifically, how much memory is on your server, your total / target memory and PLE. (If you want to know more about total vs target see this link: https://blobeater.blog/2017/03/01/sql-server-target-vs-total-memory/)
They are watching me and my Azure SQL Database and recently I noticed a low impact performance recommendation was made. Naturally I became very interested. Within your database (when in the portal) under operations you may notice something similar to the below:
A quick post that is hopefully useful, I wanted a quick way to find the time, size of the database file size change and who caused it.
Setting up AD authentication with Azure SQL Database sounds simple, it is assuming you plan carefully. I did run into issues but once rectified it felt great using AD authentication in Azure rather than just SQL logins.