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)
);
GO
EXEC sp_MSforeachtable 'insert into space_used exec sp_spaceused ''?''';
GO
SELECT CAST(ROUND(SUM(reserved_MB), 0) AS INTEGER) AS reserved_MB
, CAST(ROUND(SUM(data_MB), 0) AS INTEGER) AS data_MB
, 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;
GO
DROP TABLE space_used;
GO
