SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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!

Nebraska SQL from @DBA_ANDY

I’m a forty-something Microsoft SQL Server DBA of 15+ years, a devoted husband, and a father of three young boys. I have been a DBA at a public university, at a major bank, at a healthcare system, and I now work as a remote DBA with customers across the United States. I write and speak primarily about the tips and tricks that I discover along my SQL Server journey.

Comments

Leave a comment on the original post [nebraskasql.blogspot.com, opens in a new window]

Loading comments...