Hi Guys,

I have inherited a database on SQL 2012 standard edition. It's size has been increasing and we are trying to find out the reason. The total mdf file size is 126 GB, and the free space is almost eaten up so it might increase again. I've tried identifying index fragmentation and rebuilding the fragmented indexes but that only frees up space around 2 GB, and then it starts utilizing that free space very quickly as well.

I have tried the following script to know the total object size, but total size according to this script is just 88 GB. Can anyone please help in finding out what might be causing the database to grow?

select sum(TotalSpaceGB) from (
SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    (SUM(a.total_pages) * 8)/1024/1024 AS TotalSpaceGB, 
    (SUM(a.used_pages) * 8)/1024/1024 AS UsedSpaceGB, 
    ((SUM(a.total_pages) - SUM(a.used_pages)) * 8)/1024/1024 AS UnusedSpaceGB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
/* WHERE 
    t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 */
GROUP BY 
    t.Name, s.Name, p.Rows) a

http://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx

SELECT te.name, t.DatabaseName, t.FileName, t.StartTime, t.ApplicatioNname 
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\LOG\log_331.trc', NULL) AS t 
INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id 
WHERE te.name LIKE '%Auto Grow' 
ORDER BY StartTime ASC 


--DECLARE @path VARCHAR(255),
--@cmd VARCHAR(300);
--SET @path = 'F:\MSSQL11.MSSQLSERVER\MSSQL\Log\';
--SET @cmd = 'F:\MSSQL11.MSSQLSERVER\MSSQL\Log\log*.trc' 
--SET @cmd='dir /b ' + '"' + @cmd  + '"' 


DECLARE @path VARCHAR(255),
@cmd VARCHAR(300);
SET @path = 'O:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\';
SET @cmd = 'O:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log*.trc' 
SET @cmd='dir /b ' + '"' + @cmd  + '"' 


DECLARE @files TABLE(fn VARCHAR(64));

INSERT @files EXEC master..xp_cmdshell @cmd;
SELECT * FROM @files
DELETE @files 
WHERE fn IS NULL;SELECT e.DatabaseName, e.[FileName],
e.SPID, e.Duration, e.StartTime, e.EndTime,
FileType = CASE e.EventClass
WHEN 92 THEN 'Data' WHEN 93 THEN 'Log' END,
[TraceFile] = f.fn FROM @files f CROSS APPLY
fn_trace_gettable(@path + f.fn, DEFAULT) e
WHERE e.EventClass IN (92,93)