Azure Sql database - Growing In Size

Question

Hi,

Day by day, the size of DB is getting increased which also incur more cost. Is there any way to optimize cost in Azure Sql database

Thanks

Answer 1

Really depends on route cause for your DB growth

you need to do some investigation in why the growth is occurring - for example are your tables simply gaining more data therefore they take more space to store ?

you can look at archiving old rows/records from the DB to another output for example Storage account as parquet files (you can use ADF/Synapse Pipelines to complete this with a Copy Activity source and sink data sets/linked services)

you can review the data type casting on the columns you have with large tables - for example if you consistently store data with length <255 length in a nvarchar(4000) you may want to reduce the size to 255 or what ever the expected max for the column is. you can do other tweaks to ints/smallint/tinyint etc based on data contained in the "standard" type to squeeze extra space out of the DB.

Answer 2

@AaronHughes Thanks Aaron, will review the database

Answer 3

sp_spaceused is handy for looking at space consumption for individual tables. Google around for a script that can run it for all DBs or run this to generate

select 'exec sp_spaceused ''' +s.name+'.' +o.name+'''' from sys.objects o , sys.schemas s
where o.type = 'U'
and o.schema_id = s.schema_id