Azure SQL Database not supporting DBCC SHRINKFILE

Stephen Planck 21

Hey guys, I have recently tried to shrink a database file in Azure SQL Database, but am getting a message saying this function is not supported in this version of SQL Server. Anybody know when this support was removed? What is the replacement in Azure SQL DB to perform this function? The documentation still says it is supported, so this is a bit strange. Stephen

Anurag Sharma 17,571 Reputation points

2021-03-10T06:24:29.08+00:00
Hi @Stephen Planck , welcome to Microsoft Q&A forum.

I tried creating a new Azure SQL Database and was able to run the below command successfully:

DBCC SHRINKDATABASE (testdb, 10);

Could you please let me know the version of Azure SQL you are running this on. You can use below query for the same:

select @@Version

My Azure SQL Version: Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 20 2021 17:51:58 Copyright (C) 2019 Microsoft Corporation
tibor_karaszi@hotmail.com 4,301 Reputation points

2021-03-10T10:28:58.75+00:00

Perhaps it is the GUI that does something that isn't supported? Did you run the command directly in a query window?
Stephen Planck 21 Reputation points

2021-03-10T22:54:45.04+00:00

Hey there,

We can run SHRINKDATABASE which is still supported, but this doesn't work because we have a very active database and the transaction logs are always being used. Here's the version information:

Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 20 2021 17:51:58 Copyright (C) 2019 Microsoft Corporation

It's only when we try to use SHRINKFILE that we get the error about it not being supported by this version.
Stephen Planck 21 Reputation points

2021-03-10T22:57:13.553+00:00

Yep. We are running the queries directly against the database. This is a 6.5TB database that needs to shrink by about half, and considering the commands are executed on the UI thread when using the GUI, that probably wouldn't work out well. :)
tibor_karaszi@hotmail.com 4,301 Reputation points

2021-03-11T08:56:41.377+00:00

Hehe, I see why you don't want to use the GUI for that shrink!.

Weird, below command work for me:

DBCC SHRINKFILE(data_0, 30)
And it als performed a shrink.

Can you share the command you are running?

(I have the same version/timestamp as you, btw.)

1 answer

Stephen Planck 21 Reputation points

2021-03-16T23:35:48.577+00:00

Hey there,

We figured out the issue. We're using the hyperscale service tier which apparently doesn't support SHRINKFILE. This is from the official Microsoft documentation:

"DBCC SHRINKDATABASE or DBCC SHRINKFILE isn't currently supported for Hyperscale databases."

It doesn't mention anything about an alternative option, so I'm not sure if this is even possible with the hyperscale service tier.

If anyone is interested, here's the documentation page where we found the answer:

https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale

Thanks for your help.
Please sign in to rate this answer.

3 people found this answer helpful.
tibor_karaszi@hotmail.com 4,301 Reputation points

2021-03-17T13:54:09.12+00:00

Good catch!
Sign in to comment