Toolbox - Why is My Database So Big????

I have written previously (here) about how to tell which database files have free space in them when your drive starts to fill.

What if all of your database files are full and you are still running out of space?

DRIVE	DATABASE NAME	FILENAME	FILETYPE	FILESIZE	SPACEFREE	PHYSICAL_NAME
F	DB03	DB03_MDF	DATA	35.47 GB	225.63 MB	F:\MSSQL\Data\DB03_Data.mdf
F	DB02	DB02Data	DATA	110.25 MB	92.38 MB	F:\MSSQL\Data\DB02.mdf
F	DB01	DB01Data	DATA	142.06 MB	72.69 MB	F:\MSSQL\Data\DB01.mdf
F	DB05	DB05_Data	DATA	35.72 GB	71.44 MB	F:\MSSQL\Data\DB05_Data.mdf
F	DB06	DB06_MDF1	DATA	36.47 GB	58.50 MB	F:\MSSQL\Data\DB06_Data.mdf
F	DB04	DB04Data	DATA	36.47 GB	38.00 MB	F:\MSSQL\Data\DB04_Data.mdf

http://www.joemartinfitness.com/wp-content/uploads/2013/11/Post-holiday-bloat.jpg

The next step is to see what is taking up the space in the individual databases.  Maybe there's an audit table that never gets purged...or a Sales History that can be archived away, it only there were an archive process...

https://s3.amazonaws.com/lowres.cartoonstock.com/business-commerce-work-workers-employee-employer-staff-ear0117_low.jpg

**ALWAYS CREATE AN ARCHIVE/PURGE PROCESS ** #ThisIsNotAnItDepends

--

There is an easy query to return the free space available in the various tables in your database - I found it in an StackOverflow at https://stackoverflow.com/questions/15896564/get-table-and-index-storage-size-in-sql-server and then modified it somewhat to make the result set cleaner (to me anyway):

--

/*
Object Sizes

Modified from http://stackoverflow.com/questions/15896564/get-table-and-index-storage-size-in-sql-server
*/

SELECT 
@@SERVERNAME as InstanceName
, DB_NAME() as DatabaseName
, ISNULL(s.name+'.'+t.NAME, '**TOTAL**')  AS TableName
, SUM(p.rows) AS RowCounts
--, SUM(a.total_pages) * 8 AS TotalSpaceKB
, SUM(a.total_pages) * 8/1024.0 AS TotalSpaceMB
, SUM(a.total_pages) * 8/1024.0/1024.0 AS TotalSpaceGB
, SUM(a.used_pages) * 8/1024.0 AS UsedSpaceMB
, (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024.0 AS UnusedSpaceMB
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 s.name+'.'+t.Name
WITH ROLLUP
ORDER BY TotalSpaceMB DESC

--

Running the query against an individual database will return all of the tables in the database and their sizes, as well as the total size of the database (from the ROLLUP):

--

InstanceName	DatabaseName	TableName	RowCounts	TotalSpaceMB	UsedSpaceMB	UnusedSpaceMB
Instance01	Database99	**TOTAL**	148409590	36,042.063	32,890.922	3151.141
Instance01	Database99	dbo.BusinessRulesAuditing	20686280	17,877.813	17,860.938	16.875
Instance01	Database99	dbo.BusinessRulesRuleSet	18840	3,895.766	877.945	3,017.820
Instance01	Database99	dbo.ModelWorkbookVersionExt	4383	1,818.453	1,806.445	12.008
Instance01	Database99	dbo.EquityOutputVersions	35040362	1,746.688	1,739.281	7.406
Instance01	Database99	dbo.NominalOutputs	3592710	258.305	251.047	7.258
Instance01	Database99	dbo.Auditing	173494	33.391	27.586	5.805
Instance01	Database99	dbo.EquityOverrides	13105402	515.859	511.188	4.672
Instance01	Database99	dbo.ContingentOutputs	1371465	364.641	361.719	2.922
Instance01	Database99	dbo.ContingentWSStaging	292907	333.320	329.359	3.961
Instance01	Database99	dbo.TransformedAdminContingent	585848	76.070	73.000	3.070

--

There are two different useful cases I normally find with this result set.

The first (highlighted in aqua) is the very large table.  In this sample my 35GB database has one large 17GB table.  This is a situation where you can investigate the table and see *why* it is so large.  In many cases, this is just the way it is - sometimes one large "mother" table is a fact of life (You take the good, you take the bad, you take them both, and there you have..."

https://memegenerator.net/img/instances/11140850/god-im-old.jpg

Often though you will find that this table is an anomaly.  As mentioned above, maybe there is a missing purge or archive process - with a very large table look at the table definitions to see if their are date/datetime columns, and then select the top 10 order by those columns one by one to see how old the oldest records are.  You may find that you are storing years of data when you only need months and can purge the oldest rows.  You may also find that even though you do need years of data, you may not need them live in production, which allows you to periodically archive them away to another database (maybe even another instance) where they can be accessed only when needed.

https://blog.parse.ly/wp-content/uploads/2015/05/say_big_data.jpg

--

The second case (highlighted in yellow) is a table with significant free space contained in the table itself.  In this example a 3.7GB table has 3.0GB of free space in it!

How do you account for this?  There are a few ways - when you delete large numbers of rows from the table, the space usually isn't released until the next time the related indexes are rebuilt.  Another possibility is index fill factor - the amount of free space that is included in indexes when they are rebuilt.  I have run into several instances where a client DBA misunderstood the meaning of the fill factor and did a reverse to themselves, setting the fill factor to 10 thinking it would leave 10% free space when in fact it left 90% free space, resulting not only in exceeding large indexes but also in very poor performance as the SQL Server needs to scan across many more pages to retrieve your data.

To help determine which index(es) may contribute to the problem, you can add the indexname to the query to break the data out that one more level:

--


/*
Object Sizes With Indexes

Modified from http://stackoverflow.com/questions/15896564/get-table-and-index-storage-size-in-sql-server
*/

SELECT 
@@SERVERNAME as InstanceName
, DB_NAME() as DatabaseName
, ISNULL(s.name+'.'+t.NAME, '**TOTAL**')  AS TableName
, ISNULL(i.Name, '**TOTAL**')  AS IndexName
, SUM(p.rows) AS RowCounts
--, SUM(a.total_pages) * 8 AS TotalSpaceKB
, SUM(a.total_pages) * 8/1024.0 AS TotalSpaceMB
, SUM(a.total_pages) * 8/1024.0/1024.0 AS TotalSpaceGB
, SUM(a.used_pages) * 8/1024.0 AS UsedSpaceMB
, (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024.0 AS UnusedSpaceMB
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 s.name+'.'+t.Name
, i.Name
WITH ROLLUP

--

InstanceName	DatabaseName	TableName	IndexName	RowCounts	TotalSpaceMB	UsedSpaceMB	UnusedSpaceMB
Instance01	Database99	**TOTAL**	**TOTAL**	148409590	36,042.063	35,890.922	151.141
Instance01	Database99	BusinessRulesAuditing	PK__BusinessRules	20686280	17,877.813	17,860.938	16.875
Instance01	Database99	BusinessRulesAuditing	**TOTAL**	20686280	17,877.813	17,860.938	16.875
Instance01	Database99	BusinessRulesRuleSet	IX_RuleSet1	18840	2,100.000	137.000	1,963.000
Instance01	Database99	BusinessRulesRuleSet	IX_RuleSet2	18840	190.000	180.000	10.000
Instance01	Database99	BusinessRulesRuleSet	PK_RuleSet	18840	1,600.000	560.000	1,040.000
Instance01	Database99	BusinessRulesRuleSet	**TOTAL**	56520	3,895.766	877.945	3,017.820
Instance01	Database99	EquityOutputVersions	PK_EquityOutputVersions	35040362	1,746.688	1,739.281	7.406
Instance01	Database99	EquityOutputVersions	**TOTAL**	35040362	1,746.688	1,739.281	7.406
Instance01	Database99	EquityOverrides	PK_EquityOverrides	13105402	515.859	511.188	4.672
Instance01	Database99	EquityOverrides	**TOTAL**	13105402	515.859	511.188	4.672
Instance01	Database99	Auditing	PK_Auditing	173494	33.391	27.586	5.805
Instance01	Database99	Auditing	**TOTAL**	173494	33.391	27.586	5.805

<result set snipped for space>

--

In this case you could look at the fill factor of the IX_RuleSet1 and PK_RuleSet indexes to see why there is so much free space.  Failing that it is possible that these indexes need to be rebuilt to release that space, possibly after a large delete from the table cleared that space.


--

I find I use this query more than I ever would have thought with space issues - I start with the Database Free File query (from the previous post) and then move next to this query.

--

Hope this helps!