1

This question already has an answer here:

There is db space challenge at hand on Azure SQL Server database. 490 GB out of 500 GB is used. That's what I read from nice pie chart on Azure portal. Notice that some of the techniques used on-prem won't work for Azure SQL Server Database.

Biggest table is DidbStudySerumLevelDeviation.

exec sp_spaceused 'RulesEngine.DidbStudySerumLevelDeviation '

returns

+------------+------------------------------+ | name | DidbStudySerumLevelDeviation | | rows | 20 805 409 | | reserved | 133 332 840 KB | | data | 123 079 792 KB | | index_size | 9 772 192 KB | | unused | 480 856 KB | +------------+------------------------------+

Table definition: CREATE TABLE [RulesEngine].[DidbStudySerumLevelDeviation]( [Id] [uniqueidentifier] NOT NULL, [PatientTherapyId] [uniqueidentifier] NOT NULL, ... [Abstract] [nvarchar](max) NULL, ... CONSTRAINT [PK_RulesEngine.DidbStudySerumLevelDeviation] PRIMARY KEY CLUSTERED ( [Id] ASC, [PatientTherapyId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) Clustered index exists on primary key. There are also some nonclustered indexes. Abstract column data takes around 90% of space of a table.

Procedure is run to set unnecessary Abstract column data to null. That should reduce problematic column "weight" to 10%. However this doesn't reduce database size. How to reclaim that database space?

Maybe I should wait and some Azure SLQ maintenance magic occurs? It sounds like wishfull thinking though.

Regarding possible duplicate

Answer to this question might me specific to Azure deployment of SQL Server. Drop in database size reported in Azure portal or read from exec sp_spaceused didn't change.

marked as duplicate by Erik Darling, LowlyDBA, James Anderson, hot2use, McNets Sep 21 '17 at 8:33

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • @sp_BlitzErik Are you suggesting to run DBCC CLEANTABLE. I don't think you do, but I don't understand last sentance in linked answer. – Fosna Sep 20 '17 at 17:35
  • 1
    No, I'm suggesting you don't need it. That only works when you DROP a column. – Erik Darling Sep 20 '17 at 17:35
3

Please defrag all indexes. As mentioned on this article, fragmentation can claim a lot space.

Run sp_helpfile to verify the log is not consuming space also. If the log is big run the following statement to recover space.

DBCC SHRINKFILE (log, 0)

On this thread I provided some queries that may be useful about database size.

Hope this helps.

  • I'm rebuilding indexes and checking index stats as you suggested. Thx. I'll report back when it's done. – Fosna Sep 21 '17 at 14:10
  • Articles have helped. Thx. Some unused indexes were removed. That's fastest db space gain. Clustered index is rebuilt. That took some (down)time. One tiny thing. On Azure SQL database (as a service) log file size is not counted against database quota. To proove it, runexec sp_helpfile and compare sizes against database spice on overview tab in Azure portal. – Fosna Sep 21 '17 at 21:47
  • Users have reported not able to scale down when log is big. – Alberto Morillo Sep 22 '17 at 1:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.