increase in database size

Question

Hi All,

We have a 2 TB disk to stored of the sql database files. db has 3 data files and 1 log file. log file is stored on a different drive.

The issue is with the data file drive. Current disk usage (E:\ drive) reached is 1.20 TB out of 2TB.

After monitoring for last week 5 days , we are seeing one of the index data is getting increased as below.

monday - 100GB

tuesday - 20GB

wednesday -150GB

thursday - 50GB

friday - 60GB

So, i am little worried the disk will be full very soon.

Questions

1. how much more disk can I add? with this rate of growth?
2. How many days will this drive will this drive sustain before the drive gets completely filled up. I want to know the calculation behind it.

The problem is, we are reached out the number of disk limits for the Azure VM. only option is to go to next VM size. or create new VM and move some db's to the new VM.

Before that, we need to provide data to the management and app team that the data is growing and very soon we might run out of disk space and hit database downtime.

Alternatively, we are figuring out to archiving some of the unwanted backup tables and old data in large tables within the databases. Also, trying to figureout unused indexes and disable / dropping them.

Regards,

Sam

Answer 1

No one can really answer your post.

You have to dig deeper into, which table increases and is this wanted or only "trash data" or so called "unused space", cause by heap tables etc.?

You can query table/index size with

-- List all database tables and there indexes with 
-- detailed information about row count and 
-- used + reserved data space. 
SELECT DISTINCT SCH.name AS SchemaName
      ,OBJ.name AS ObjName
      ,OBJ.type_desc AS ObjType
      ,INDX.name AS IndexName
      ,INDX.type_desc AS IndexType
      ,PART.partition_number AS PartitionNumber
      ,PART.rows AS PartitionRows
      ,STAT.row_count AS StatRowCount
      ,STAT.used_page_count * 8 AS UsedSizeKB
      ,STAT.reserved_page_count * 8 AS ReservedSizeKB
      ,PART.data_compression_desc
      ,DS.name AS FilegroupName
      ,(STAT.reserved_page_count - STAT.used_page_count) * 8 AS Unused
      ,CONVERT(money, ROUND(FRG.avg_fragmentation_in_percent, 2)) AS FragScale, FRG.fragment_count AS FragCnt
FROM sys.partitions AS PART
     INNER JOIN sys.dm_db_partition_stats AS STAT
         ON PART.partition_id = STAT.partition_id
            AND PART.partition_number = STAT.partition_number
     INNER JOIN sys.objects AS OBJ
         ON STAT.object_id = OBJ.object_id
     INNER JOIN sys.schemas AS SCH
         ON OBJ.schema_id = SCH.schema_id
     INNER JOIN sys.indexes AS INDX
         ON STAT.object_id = INDX.object_id
            AND STAT.index_id = INDX.index_id
     INNER JOIN sys.data_spaces AS DS
         ON INDX.data_space_id = DS.data_space_id
     INNER JOIN
     sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS FRG
        ON FRG.object_id = OBJ.object_id           
           AND FRG.index_id = INDX.index_id
ORDER BY OBJ.name
        ,INDX.name
        ,PART.partition_number

Answer 2

Hello,Sam

The current size and growth rate of the data file you mentioned are common in some large-scale applications because the growth of the data file is closely related to the way client applications are used.

If you find this growth unacceptable, you must identify which tables are occupying the file space, why it is so large, and how to quickly free up space.

You can shrink the database to reduce the size of the MDF file, but this does not fundamentally solve the issue of your large, incrementally growing file.

Here are some commands to help you troubleshoot abnormal data file sizes:

1.Calculate the total number of extents and the number of used extents in a database data file.

DBCC SHOWFILESTATS

2.Use the Dynamic Management View (sys.dm_db_partition_stats) and the sys.objects table. Calculates and aggregates the different types of page counts and row counts for each table or index object, with the results ordered by reserved page count in descending order：

SELECT o.name ,
         SUM (p.reserved_page_count) AS reserved_page_count,
         SUM (p.used_page_count) AS used_page_count,
         SUM (
            CASE
                WHEN (p.index_id < 2) THEN (p.in_row_data_page_count + 
                  p.lob_used_page_count + p.row_overflow_used_page_count)
                ELSE p.lob_used_page_count + p.row_overflow_used_page_count
            END
            ) AS DataPages,
         SUM (
            CASE
                WHEN (p.index_id < 2) THEN row_count
                ELSE 0
            END
            ) AS rowCounts
    FROM sys.dm_db_partition_stats p INNER JOIN sys.objects o
        ON p.object_id = o.object_id
    GROUP BY o.name
	ORDER BY reserved_page_count DESC

3.Calculate the storage usage for each table in the database and display it in MB.

SELECT 
    schema_name(o.schema_id) AS SchemaName,
    o.name AS TableName,
    SUM(p.reserved_page_count) * 8 / 1024 AS reserved_mb,
    SUM(p.used_page_count) * 8 / 1024 AS used_mb,
    SUM(p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count) * 8 / 1024 AS data_mb
FROM 
    sys.dm_db_partition_stats p
JOIN 
    sys.objects o ON p.object_id = o.object_id
WHERE 
    o.type = 'U'  
GROUP BY 
    schema_name(o.schema_id), 
    o.name
ORDER BY 
    reserved_mb DESC;

In summary, please do not forget to balance business needs with storage space limitations, and discard unnecessary data in a timely manner.