What can cause a large data file with lots of unused space?

Chris Sijtsma 141 Reputation points

Dear colleagues,

I have a database for which the data file regularly grows to approx. 65 GB. If I Iook at the space usage, I see that some 10% of this space is actually used. There are no tables with excessive unused space or index space. If I shrink the database, everything looks ok, but a week or so later, I am in the same situation. What can cause this behaviour? The only thing I can think of is some bulk insert that also deletes (most of) the data inserted. But there are no jobs active doing this. Probably, I need to monitor what happens to find the culprit, but any ideas on the cause are welcome.

The SQL Server version is "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)"

Results of "sp_helpdb <database name>" (size converted to MB, file names and db name made anonymous).

name      |  fileid  |  filename      |  filegroup  |  size (MB)  |  growth   |  usage
xxx_data  |       1  |  xxx_data.mdf  |  PRIMARY    |  62,976     |  1024 KB  |  data only
xxx_log   |       2  |  xxx_log.ldf   |  NULL       |  417        |        0  |  log only

Results of SUM of sp_spaceused values

reserved_MB  |  data_MB  |  index_size_MB  |  unused_MB
      6,663  |    6,566  |             53  |         44

/* Script used */
CREATE TABLE space_used
( name        SYSNAME      NOT NULL
, rows        BIGINT       NOT NULL
, reserved    VARCHAR(30)  NOT NULL
, data        VARCHAR(30)  NOT NULL
, index_size  VARCHAR(30)  NOT NULL
, unused      VARCHAR(30)  NOT NULL
, reserved_MB   AS (CAST(LEFT(reserved, LEN(reserved)-3) AS BIGINT) / 1024.0)
, data_MB       AS (CAST(LEFT(data, LEN(data)-3) AS BIGINT) / 1024.0)
, index_size_MB AS (CAST(LEFT(index_size, LEN(index_size)-3) AS BIGINT) / 1024.0)
, unused_MB     AS (CAST(LEFT(unused, LEN(unused)-3) AS BIGINT) /1024.0)

EXEC sp_MSforeachtable 'insert into space_used exec sp_spaceused ''?''';

, CAST(ROUND(SUM(index_size_MB), 0) AS INTEGER) AS index_size_MB
, CAST(ROUND(SUM(unused_MB), 0) AS INTEGER) AS unused_MB
FROM space_used;

DROP TABLE space_used;
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,912 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points

    If I shrink the database, everything looks ok, but a week or so later, I am in the same situation. What can cause this behaviour?

    Elementary, my dear Watson. You shrink it. If you stop shrink it it will stop growing.

    If you want to find what is going on, you could set up a trace what is filtered for a high number on writes. (But keep in mind that traces can seriously impact workloads with many small ad-hoc statements.)

    The SQL Server version is "Microsoft SQL Server 2012 (SP1)

    Apply Sevice Pack4!

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful