sql server database size increasing at a very high rate

Question

We have a table that has a blob column along with other simple fields. Number of rows is about 6K, and sum of length of the blob column for all rows accounted for about 30MB. however when we ran a query to find the actual table space occupied, it was about 10GB(Database size itself is 11GB). We are wondering if,

1. Would aborting transaction still block the space allocated for blob as though it's committed?

2. Though number of records did not increase significantly, size of database jumps from 2GB to about 1OGB in couple of days. Running the query to find the real table space showed that the single table which had the blob, had about 8GB of data (there are only 6K records and sum of datalength of the blobs gives only 30MB).

Anything we can do to resolve the space issue and address why sizeof of database has to increase so much?

Answer 1

• Full recovery model and no backups?
• Open tran preventing log clearance on backup (Run DBCC OPENTRAN)
• Many deletes/inserts + no index maintenance? (and flavours)

And what does this say for all objects? (Copy/Paste of a script I use)

SELECT
    ds.[name] AS LogicalFileName,
    OBJECT_NAME(p.object_id) AS Thing,
    SUM(au.total_pages) / 128.0 AS UsedMB,
    df.size / 128 AS FileSizeMB,
    100.0 * SUM(au.total_pages) / df.size AS PercentUsed
FROM
    sys.database_files df
    JOIN
    sys.data_spaces ds ON df.data_space_id = ds.data_space_id 
    JOIN
    sys.allocation_units au ON ds.data_space_id = au.data_space_id 
    JOIN 
    sys.partitions p ON au.container_id = p.hobt_id
WHERE
    OBJECTPROPERTYEX(p.object_id, 'IsMSShipped') = 0
GROUP BY
    ds.[name], OBJECT_NAME(p.object_id), df.size
ORDER BY
    ds.[name]

Answer 2

Would aborting transaction still block the space allocated for blob as though it's committed?

Index physical changes occur on separate transactions from logical changes. When an INSERT needs to allocate a new page for the table in order to acomodate the new row, the page allocation occurs on a different transaction that is immediately commited. If the INSERT rolls back, the page allocation is unaffected. Think what would happen if the page allocation would be rolled back and another insert had already allocated a different row slot in it and commited that insert.

Things are a bit more complicated, specially for BLOB storage hobts, but the idea is that a high rate of modifications in the BLOB columns may cause a high ratio of unused pages. A high rate of rollbacks may accentuate the problem. sys.allocation_units will easily reveal this if total_pages is much higher than used_pages for allocation units of type 2 and 3.

You can try an (online) index rebuild macking sure LOB_COMPACTION is ON.