I could not read my error log on one of my local SQL Servers, when I executed the following code:
I received the below:
Msg 22004, Level 16, State 1, Line 2 Failed to open loopback connection. Please see event log for more information. Msg 22004, Level 16, State 1, Line 2 Error log location not found.
It’s good to be proactive and one way is to setup alerts and it is no different when using Azure SQL Database. I like creating alerts for my Azure SQL Databases and I encourage you to do the same.
Let’s work through some code to do an encrypted backup. This feature is available to you if you are using SQL Server 2014 onwards but I decided to use SQL Server 2017.
To encrypt during backup, you must specify an encryption algorithm, and an “encryptor” to secure the encryption key. I have decided to use the following options:
- Encryption Algorithm: AES 256
- Encryptor: A certificate
Here is a quick Extended Events script I knocked up where I wanted to track Tempdb file size changes for both the data and log file. I wanted to know who caused the tempdb growth, when it was done, what the T-SQL was and what sizes were involved. Not exactly complicated but hopefully useful.
You have the ability to actually pause SQL Server, if you are in SQL Server Management Studio (SSMS), you might have noticed it as the below image.
Have you ever wanted SQL Server Management Studio (SSMS) 17 to have a dark theme? Seeing the below image (visual experience color theme options) really got me excited.
Apparently there is a new tool from Microsoft where you can discover, track, and remediate potential database vulnerabilities. This tool is available for both on-premises SQL Server and Azure SQL Database. I actually cannot find the download for the on-premises version so I decided to give it a go in Azure SQL Database.
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.
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.