Should i shrink the database and how if yes ?

sam nick 286 Reputation points
2022-01-23T19:55:33.027+00:00

Hi,
For one of our azure Db's which was around 400 gb is now only 50 gb. Primarily because we had to remove legacy data and move it to different location. We are still paying for 400gb and very sure that we never will reach 100 gb as data has been diverted accordingly. This is a provisioned database and no intention of moving to serverless db.

My question is should i drink the database from 400 to 150 gb (as a worst unseen case scenario). If so, can i just execute dbcc srinkdatabase command or are there any other additional recommendations.

Please can you advise.

Azure SQL Database
0 comments
Accepted answer
  1. Ronen Ariely 15,096 Reputation points
    2022-01-24T04:43:20.843+00:00

    Hi,

    For one of our azure Db's which was around 400 gb is now only 50 gb... My question is should i drink the database from 400 to 150 gb

    (1) Do you speak about the database size (the size of the files) or the size of the data ?!?

    When you are using sp_helpfile the value of the [size] is NOT the size of the data but the size of the database file, which less help you know what is the size which you should use.

    You can get the size of the files and the space size which is used for the ROWS in the file, you can use sys.database_files and the function FILEPROPERTY. 

    SELECT name,   
 size/128.0 FileSizeInMB,   
 SpaceUsed = FILEPROPERTY(name, 'SpaceUsed')/128.0  
FROM sys.database_files;  
GO

    Note: You can also get the information using sp_spaceused or using sys.resource_stats

    (2) Shrinking the database is NOT the main parameter to reduce the cost directly, since you pay according to the type of service tier, amount of DTUs, and the Database max size - less according to the actual size of the data or the data files (the storage size).

    (3) Shrinking the transaction log file is HIGHLY not recommended in most cases.

    Using On-premises database it is usually a bad idea to shrink the log file since it can impact performance, but using Azure SQL Database it even make less sense in most cases, since Azure SQL Database automatically shrinks transaction log file to avoid excessive space usage.

    If you still decide to shrink the log file, then you should probably use the parameter TRUNCATEONLY to releases the free space at the file's end to the operating system but without perform any page movement inside the file !

    https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage#shrinking-transaction-log-file

    (4) In order to to reduce the cost you will need to change the Compute + storage of the database, which mean (as I mentioned above) to change the service tier, the DTUs, the Max size of the database, number of vCores or memory, and so on (depending on the type of pricing tier you use).

    In some cases in order to change the parameters of the Compute + storage you will have to first reduce the size of the database - in this case and only in this case it will probably make sense to shrink the database file size to fit the new configuration you want to use.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,896 Reputation points MVP
    2022-01-23T20:35:22.85+00:00

    Yes, recover that space as you will pay less for storage. Shrinking will help you pay for what you use.

    You can first display information about the database using 

    sp_helpfile

    You can shrink the database using 

    DBCC SHRINKDATABASE(N'dbname')

    You can shrink only the log, if you see that is the only datafile with over allocated space: 

    DBCC SHRINKFILE (log, 0)

    You can also set a maximum size for the database with the following ALTER DATABASE statement: 

     ALTER DATABASE DATABASE_NAME
 MODIFY (EDITION='STANDARD', MAXSIZE=150 GB)
    0 comments
  2. Francesco Mantovani 11 Reputation points
    2022-12-09T10:54:06.98+00:00

    Hi @Alberto Morillo , I'm running DBCC SHRINKDATABASE(N'dbname') on a 30GB Azure SQL Database.
    The database is inside an Elastic Pool with other databases.

    After nearly 1h it's still running:

    268885-image.png

    I'm checking time by time if anything changes: 

    SELECT  
	name,  
	CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 as SpaceUsedInMB,  
	size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB  
FROM sys.database_files

    Yeh, I'm gaining more space because I went form 29GB of used space to 22 GB of used space.
    However I'm asking myself when this thing will stop.

    Does killing DBCC SHRINKDATABASE will rollback everything?

    0 comments