Who should be running DBCC CHECKDB for Azure SQL Database? Should it be Microsoft or should customers be scheduling it? All official information just tells you that you CAN run it (below shows the green tick) but still no clarity around the question.

I was exchanging messages with Azure Support and even though I didn’t get a concrete answer to confirm this I ended up asking the question within a Microsoft based yammer group and yes they do automatically carry out consistency checks.

This is great but it is one less thing for me to worry about and if there is serious corruption, you know potential data loss (which would be rare) then they will definitely tell you and work with you.

However, it doesn’t mean you CAN’T run it, I was curious so I ran DBCC CHECKDB on my Azure SQL Databases, but like with any other consistency check it is best to do it OFF-PEAK hours. I would probably take it a step further and wouldn’t even bother running it.

Why? Looking at it from a database perspective you can see that CHECKDB pretty much consumes all my DTUs.

This particular database is in my elastic pool, with nothing else running CHECKDB consumes just under 80% eDTUs allocated to my Standard 100 eDTU pool.

You can then get specific; the green line below is the specific database consuming all its allocated eDTUs.

This is the impact on a different database that is a basic (5) tier one.

I even get an alert!

What happens if I scale to a S2 (50 DTUs)?

Still consumes all my DTUs but it does take half the time to complete.

I am guessing that Microsoft are running their checks on a replica somewhere else, I would not want to run this even during off-peak hours especially if “some” users are still accessing the database.

Going back to corruption. Another reason why I thought Microsoft would look after consistency checks is because if you need to do something like run emergency mode repair where the first step is to go into emergency mode. Well you can’t in Azure.

ALTER DATABASE [TestDB] SET EMERGENCY;
GO

Msg 42008, Level 16, State 5, Line 1 ODBC error: State: 42000: Error: 1468 Message:'[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The operation cannot be performed on database “TestDB” because it is involved in a database mirroring session or an availability group.

Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.’.

Msg 5069, Level 16, State 3, Line 1

ALTER DATABASE statement failed.

I guess the message shows what is under the covers driving high availability and lets hope that I will never come across a corrupted Azure SQL Database. Before you ask I did try DBCC WRITEPAGE – that didn’t work.

Filed under: Azure, Azure SQL DB, CHECKDB, SQL SERVER Tagged: Azure, CHECKDB, Corruption, SQL database, SQL server