In the early days of Azure SQL Database (ne SQL Azure) we had size limits that were minuscule–I vaguely remember 5 and 10 GB, but it could have been smaller. Back then space was at a premium. In 2021, we have hyperscale that allows us to have an Azure SQL Database of up to 100 TB (or more) in size. However data size in the cloud means costs, so space management can matter, right?
Let’s Talk About Your Log File
While many DBAs will never ever want to shrink a data file, sometimes your transaction log file can grow for external reasons. Typically these reasons are as follows:
- Your log backups failed for a while and the log file grew
- You couldn’t write to an availability group secondary for a while and your log file grew
- You couldn’t write to a replication target for a while and your log file grew
You will note that I didn’t mention that “your log file grew because of a large index rebuild”. That’s because that is probably roughly (this is a really rough rule of thumb) how big your transaction log needs to be. But, anyway, we’re talking about Azure SQL Database, so you don’t need to worry about your transaction log file. Microsoft takes care of that for you: ‘Unlike data files, Azure SQL Database automatically shrinks log files since that operation does not impact database performance.’
What About My Data Files?
In an on-premises world, shrinking data files should come down to two scenarios:
- You were creating a database file and you accidentally added an extra zero to the size field.
- You weren’t using compression, and you compressed all of your larger tables and indexes and got a bunch of space back
The first one is easy–since SQL Server never wrote data to those pages, SHRINKDB will quickly reallocate them. The second one is much harder and depends on your data types and how busy your server is. In that case SHRINK may never complete–especially if you used LOB data types. Besides that SHRINK will fragment your data massively. So you should basically never run it.
Disk space is mostly cheap, so unless you are extremely storage constrained you should just live with empty space. In Azure SQL this is even more complex, because the ghost cleanup process, which cleans up unused dirty pages can take a long time to run. So it can be days or weeks before you can reclaim your space, if ever.
So Really, How Do I Shrink My Azure SQL Database?
You don’t. Space, even in the cloud is really cheap. If you really want to make the database smaller, you can extract the data into a new database, and then drop the original database. Congratulations, you just took downtime and took several hours of your life to save $30/month. That doc from Microsoft recommends (with a warning) enabling autoshrink on your databases. Don’t do that. Ever. It’s really, really bad for your performance. Relax and enjoy the fact that your don’t have to bug the SAN admin for space in the cloud.
Pingback: Shrinking an Azure SQL Database – Curated SQL
Great. Awesome, so I’m reducing a database from a 1.37 TB allocated size to under 1TB so it can be added to an elastic pool. I’ll just tell the client that we’re not doing that, because size doesn’t matter! I’m assuming you’re willing to pay the $2000 per month server difference?
It’s not about doing nothing at all. You can just create new db and migrate the data instead 🙂
And that’s fine if the client doesn’t mind the downtime, I just don’t like that the article is titled “How Do You Shrink Your Azure SQL Database?” and the author fails to answer that in any way whatsoever. Your 1 line answer provided more information than their entire page in regards to an actual answer.
I didn’t want to know how to shrink azure sql anyway. That’s not the way to write an article about something.
“do nothing” could not be the answer.
Spectacularly bad article with a misleading title. There are many instances where you want to shrink a DB. For me, refreshing from prod and purging very large tables not needed in dev. “Eh, it’s only money!” is not a satisfactory conclusion. You come pay for my budget then.
Exact same problem here. I have to shrink a database to make it fit inside a SQL Elastic Pool. Is not a 30$ problem, it’s a 2,000$ problem