If you know about DBCC CHECKDB then most likely you will know about DBCC CHECKTABLE. Quite simply this command performs primitive system-catalog consistency checks, per-table consistency checks on the single table specified, and cross-table consistency checks on indexed views that reference the specified table. (Page 899 Microsoft SQL Server Internals 2012, Chapter 14, Page 899, Paul Randal)
Let’s start off with a quick overview of SQL Server versions and compatibility levels.
- 100 = SQL Server 2008 and Azure SQL Database
- 110 = SQL Server 2012 and Azure SQL Database
- 120 = SQL Server 2014 and Azure SQL Database
- 130 = SQL Server 2016 and Azure SQL Database
- 140 = SQL Server 2017 and Azure SQL Database
So with SQL Server 2017 now available to the public what level is a newly created Azure SQL Database set at?
With Halloween around the corner what better topic to discuss than phantom reads. A phantom read occurs when rows have been inserted after a read operation and becomes visible in a follow-up read operation within the same transaction.
So I had a corruption issue and I was thinking about running repair but I wanted to know what would potentially get deleted.
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.
Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?
If you know me by now I like rebuilding indexes and that is no different in Azure. Now we have the ability to resume a paused rebuilding operation rather than cancelling it (Feature currently in public preview). I like this because I have the flexibility to pause it if I feel that it is taking up too much DTU (Database Transaction Unit) usage hence I can free up resources for other operations.
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
A really quick one today, something that made me think for a minute and I thought it might make others think too. So you have enabled TDE – Transparent Data Encryption (you can see these previous posts here: https://blobeater.blog/?s=tde&submit=Search) on your SQL Server database and in the back of your mind you know TempDB gets encrypted too.