question

StephenPlanck-9495 avatar image
1 Vote"
StephenPlanck-9495 asked TiborKaraszi commented

Azure SQL Database not supporting DBCC SHRINKFILE

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

azure-sql-database
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @StephenPlanck-9495, 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







0 Votes 0 ·

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.


0 Votes 0 ·

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

0 Votes 0 ·

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. :)

0 Votes 0 ·

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.)

0 Votes 0 ·

1 Answer

StephenPlanck-9495 avatar image
2 Votes"
StephenPlanck-9495 answered TiborKaraszi commented

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://docs.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale

Thanks for your help.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good catch!

0 Votes 0 ·