CODEWITHSUNDEEP
sqlsql-serverspacedisk
Christian
Christian

Reputation: 386

Approximate disk space consumption of rows on SQL Server

I'd like to understand, what causes the size of a SQL Server 12 database. The mdf has 21.5 GB. Using the "Disk Usage by Top Tables" report in SQL Server Management Studio, I can see that 15.4 GB are used by the "Data" of one table. This table has 1,691 rows of 4 columns (int, varchar(512), varchar(512), image). I assume the image column is responsible for most of the consumption. But

Select (sum(datalength(<col1>)) + ... )/1024.0/1024.0 as MB From <Table>

only gives 328.9 MB.

What might be the reason behind this huge discrepancy?

Additional information: For some rows the image column is updated regularly.

This is a screenshot of the report: enter image description here If we can trust it, indices or unused space should not be the cause.

Upvotes: 1

Views: 1260

Answers (2)

Christian
Christian

Reputation: 386

The reason was a long running transaction on another unrelated database (!) on the same SQL Server instance. The read committed snapshot isolation level filled the version store. Disconnecting the other application reduced the memory usage to a sensible amount.

Upvotes: 0

GabrielVa
GabrielVa

Reputation: 2388

Maybe you are using a lot of indexes per table, these will all add up. Maybe your auto-grow settings are wrong.

Upvotes: 0

Related Questions