-2

I have a Azure SQL Database that's been operational for around 5 years, and is now approaching 300GB in size.

The bulk of the data is in a table with around 4 millions rows. One of the columns of that table is NVARCHAR(MAX) and stores raw JSON. The size of the JSON stored is variable, with most records having JSON < 200KB, however some rows have JSON up to 16MB.

My initial thought was to migrate the JSON from being stored in the database to being stored in Azure Blob Storage, and just update the row in the database with the URL to the Blob. Then, after the migration is complete, run DBCC SHRINKDATABASE to free the previously allocated space that is no longer needed.

Is this a valid approach, or is there a better strategy? Are there pitfalls to watch out for when using DBCC SHRINKDATABASE?

Redwing19
  • 75
  • 7
  • Bit of an open-ended question. For your current strategy, you also need to do `REORGANIZE WITH ( LOB_COMPACTION = ON`. Either way, shrinking a database is usually a bad idea unless you really need the space. – Charlieface Jul 06 '23 at 17:31
  • @Charlieface, thanks for the response. The only reason we're thinking of shrinking the db is to try to save $ and prepare for the future. As it is, we could continue to periodically increase the Azure SQL DB limit, all the way to 1 TB. But it's at that point (probably a couple years down the road) that it gets very expensive. Our thinking is that changing the DB structure to store the JSON in Blob storage (at least for older data) would not only save us money now, but buy us a lot more time before hitting that 1 TB mark. – Redwing19 Jul 06 '23 at 18:01
  • OK but might be more worthwhile to just rebuild the database by copying over all the table definitions and data into a new one. Shrinking often causes big problems with fragmentation, and can often take hours to do anyway. – Charlieface Jul 06 '23 at 23:10

1 Answers1

0

Wow, very good question.

I've been trying to use DBCC SHRINKDATABASE to make databases fit into an Elastic Pool and nothing was working.

In the end the only think that worked was Auto-scale:

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT SET AUTO_SHRINK ON;

And zero collateral issues for now.

Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113