search

yochananrachamim / AzureSQL

132 stars 61 forks source link

Unable to shrink database using - Incremental Shrink #6

Closed avantida closed 5 years ago

avantida commented 5 years ago

Hi Yochanan,

Thank you for these scripts 👍 We had a database that grew over time to > 250GB; basically forcing us to upgrade the service tier from S0 to S3. We have been able to 'prune' the database back into a 'normal' size of ~80GB, but the Allocated space is still ~280 GB. so I'm attempting to run your script to reduce the size. But I keep getting the below exception. I had ran it before, and then got a timeout, and than ran it again. I have now left the database alone for several days until running it again, but still:

Current File Size: 286432MB
Actual used Size: 80096MB
Desired File Size: 100000MB
Interation shrink size: 50MB
Desired Size check - OK
Jan 25 2019  2:28PM - Iteration starting
File ID 1 of database ID 6 cannot be shrunk as it is either being shrunk by another process or is empty.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Jan 25 2019  2:30PM - Iteration completed. current size is: 286432
Done

ps: I found the scripts via https://social.msdn.microsoft.com/Forums/en-US/57a67dde-59d9-4977-9ebe-9074b31582d6/how-to-shrink-logs-on-azure-sql Since that topic is already very old I did not want to revive that.

Kind regards,-

avantida commented 5 years ago

Fix for Azure SQL is to Scale the database up/down to trigger a 'SQL Server restart'.