I was doing some normal activities on one of my Azure SQL Databases, I went to make a cup of tea and returned to the following message:

The statement has been terminated. Msg 40544, Level 17, State 12, Line 15 The database ‘TestDB’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

Obviously I have met my size quota. The options given by the message are quite varied. Partitioning is not exactly easy to do straightaway, deleting data is not usually possible and dropping indexes would make sense if I had many duplicate indexes (or un-used ones). The question now is; how do I find duplicate indexes within Azure SQL Database?

The answer is sp_BlitzIndex. You can find this free tool from this link: https://www.brentozar.com/blitzindex/. It is a stored procedure that quickly does a sanity check on your database and diagnoses your indexes major disorders where then it reports back to you and yes it does work on Azure which I didn’t know until today.

So, I re-connect to Azure SQL Database and issue the command.

 SELECT @@VERSION
EXEC dbo.sp_BlitzIndex

If you scroll across you will see a More Info column with extra T-SQL to run. For example:

EXEC dbo.sp_BlitzIndex @DatabaseName='TestDB', @SchemaName='dbo', @TableName='Audit';

This is where all the powerful information is held. I have LOADS of duplicates.

Removing these will reclaim disk space. Let’s see the before and after effect.

Under monitoring within the Azure portal you can see the Database Size option. (Okay I know this example is over simplified with the 2GB limit but the concepts stay the same).

I then use the information from sp_BlitzIndex and drop the duplicates.

I re-run sp_BlitzIndex.

Brent (and everyone else involved) tells me I have done nice work – I feel good now.