Created
December 4, 2018 11:35
-
-
Save giskou/643f61329233ce3c204d4722aba1d357 to your computer and use it in GitHub Desktop.
Downscale azure
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- https://community.dynamics.com/crm/b/crmchap/archive/2018/05/20/an-unexpected-error-occured-while-processing-the-request-error-when-downscaling-an-azure-sql-database | |
| SET NOCOUNT ON | |
| DECLARE @CurrentFileSize INT, @DesiredFileSize INT, @ShrinkChunkSize INT, @ActualSizeMB INT, | |
| @ErrorIndication INT, @dbFileID INT = 1, @LastSize INT, @SqlCMD NVARCHAR(MAX), | |
| @msg NVARCHAR(100) | |
| /*Set these values for the current operation, size is in MB*/ | |
| SET @DesiredFileSize = 350000 /* filesize is in MB */ | |
| SET @ShrinkChunkSize = 100 /* chunk size is in MB */ | |
| SELECT @CurrentFileSize = size/128 FROM sysfiles WHERE fileid = @dbFileID | |
| SELECT @ActualSizeMB = (SUM(total_pages) / 128) FROM sys.allocation_units | |
| SET @msg = 'Current File Size: ' + CAST(@CurrentFileSize AS VARCHAR(10)) + 'MB' | |
| RAISERROR(@msg,0,0) WITH NOWAIT | |
| SET @msg = 'Actual used Size: ' + CAST(@ActualSizeMB AS VARCHAR(10)) + 'MB' | |
| RAISERROR(@msg,0,0) WITH NOWAIT | |
| SET @msg = 'Desired File Size: ' + CAST(@DesiredFileSize AS VARCHAR(10)) + 'MB' | |
| RAISERROR(@msg,0,0) WITH NOWAIT | |
| SET @msg = 'Interation shrink size: ' + CAST(@ShrinkChunkSize AS VARCHAR(10)) + 'MB' | |
| RAISERROR(@msg,0,0) WITH NOWAIT | |
| SET @ErrorIndication = CASE | |
| WHEN @DesiredFileSize > @CurrentFileSize THEN 1 | |
| WHEN @ActualSizeMB > @DesiredFileSize THEN 2 | |
| ELSE 0 END | |
| IF @ErrorIndication = 1 | |
| RAISERROR('[Error] Desired size bigger than current size',0,0) WITH NOWAIT | |
| IF @ErrorIndication = 2 | |
| RAISERROR('[Error] Actual size is bigger then desired size',0,0) WITH NOWAIT | |
| IF @ErrorIndication = 0 | |
| RAISERROR('Desired Size check - OK',0,0) WITH NOWAIT | |
| SET @LastSize = @CurrentFileSize + 1 | |
| WHILE @CurrentFileSize > @DesiredFileSize /*check if we got the desired size*/ AND @LastSize>@CurrentFileSize /* check if there is progress*/ AND @ErrorIndication=0 | |
| BEGIN | |
| SET @msg = CAST(GETDATE() AS VARCHAR(100)) + ' - Iteration starting' | |
| RAISERROR(@msg,0,0) WITH NOWAIT | |
| SELECT @LastSize = size/128 FROM sysfiles WHERE fileid = @dbFileID | |
| SET @sqlCMD = 'DBCC SHRINKFILE('+ CAST(@dbFileID AS VARCHAR(7)) + ',' + CAST(@CurrentFileSize-@ShrinkChunkSize AS VARCHAR(7)) + ') WITH NO_INFOMSGS;' | |
| EXEC (@sqlCMD) | |
| SELECT @CurrentFileSize = size/128 FROM sysfiles WHERE fileid =@dbFileID | |
| SET @msg = CAST(getdate() AS VARCHAR(100)) + ' - Iteration completed. current size is: ' + CAST(@CurrentFileSize AS VARCHAR(10)) | |
| RAISERROR(@msg,0,0) WITH NOWAIT | |
| END | |
| PRINT 'Done' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment