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

Toolbox - Which Tables are Using All of My Space?

This is the next in a new series of blogs I am going to create talking about useful tools (mostly scripts) that I use frequently in my day-to-day life as a production DBA.  I work as a Work-From-Home DBA for a Remote DBA company, but 90+% of my job functions are the same as any other company DBA.

Many of these scripts come directly from blogs and articles created and shared by other members of the SQL Server community; some of them I have slightly modified and some I have lifted directly from those articles.  I will always give attribution back to the original source and note when I have made modifications.

--

In the previous post in this series "Toolbox - Where Did All My Space Go?" I shared a script for finding which database files consumed the most space and which of those files had free space in them.  The next step after finding out which databases are using the space is determining which tables in those databases are occupying that space to consider purge/archive opportunities.  In many cases you will find a single large table (often called a "mother table") taking up most of the space in your database.

https://www.newslinq.com/wp-content/uploads/2014/05/table.png
(That's a *big* table!)

I found a script created by a developer from Switzerland in a question/answer on StackOverflow.com and modified it slightly to return the specifics I wanted.  Among other things I added the InstanceName and DatabaseName because in my job I frequently create documentation or reports for external clients who don't necessarily know the result set came from a particular instance and a particular database:

--
/*
Object Sizes
Modified from http://stackoverflow.com/questions/15896564/get-table-and-index-storage-size-in-sql-server
*/
SELECT TOP 50
@@SERVERNAME as InstanceName
, DB_NAME() as DatabaseName
, s.NAME AS SchemaName
, t.NAME  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 t.Name
, s.Name
ORDER BY TotalSpaceMB DESC
--

The results look like this:

--

InstanceName DatabaseName SchemaName TableName RowCounts TotalSpaceMB TotalSpaceGB UsedSpaceMB UnusedSpaceMB
Instance01 Database15 SI_USER TRANS_DATA 17730150 16263.72 15.88 16234.14 29.58
Instance01 Database15 SI_USER WORKFLOW_CONTEXT 50785680 7623.27 7.44 7622.52 0.75
Instance01 Database15 PTM EI_HISTORY_MSG 22704543 3701.59 3.61 3701.19 0.40
Instance01 Database15 SI_USER WORKFLOW_LINKAGE 72643908 2657.21 2.59 2657.06 0.15
Instance01 Database15 SI_USER CORRELATION_SET 13762284 2542.87 2.48 2542.59 0.27
Instance01 Database15 SI_USER DOCUMENT 9833616 1445.55 1.41 1445.32 0.23
Instance01 Database15 PTM EI_HISTORY_TRANDTL 30673680 1257.23 1.23 1256.99 0.24
Instance01 Database15 SI_USER ACT_SESSION_GUID 18728114 1246.77 1.22 1246.70 0.07
Instance01 Database15 SI_USER DATA_FLOW_GUID 13635838 908.08 0.89 907.98 0.10
Instance01 Database15 PTM EI_HISTORY_TRAN 18560608 699.97 0.68 699.73 0.24
Instance01 Database15 SI_USER DATA_TABLE 174048 460.91 0.45 460.45 0.46
Instance01 Database15 SI_USER DOCUMENT_EXTENSION 1630855 363.54 0.36 363.15 0.39
Instance01 Database15 SI_USER ACT_NON_XFER 1579422 284.48 0.28 284.13 0.35
Instance01 Database15 SI_USER ACT_XFER 804270 217.98 0.21 217.61 0.38
Instance01 Database15 SI_USER ACT_SESSION 1008875 209.04 0.20 208.66 0.38
Instance01 Database15 SI_USER ARCHIVE_INFO 4203976 113.34 0.11 113.10 0.24
Instance01 Database15 SI_USER WF_INST_S 1061373 101.52 0.10 101.37 0.16
Instance01 Database15 SI_USER ACT_AUTHORIZE 298908 70.27 0.07 70.11 0.16
Instance01 Database15 SI_USER DATA_FLOW 420930 56.59 0.06 56.35 0.23
Instance01 Database15 SI_USER ACT_AUTHENTICATE 269200 45.80 0.04 45.31 0.48
Instance01 Database15 SI_USER EDIINTDOC 182672 43.83 0.04 43.69 0.14
Instance01 Database15 SI_USER MSGMDNCORRELATION 74656 27.86 0.03 26.42 1.44
Instance01 Database15 SI_USER EDI_DOCUMENT_STATE 57498 22.19 0.02 18.21 3.98
Instance01 Database15 SI_USER ENVELOPE_PARMS 134691 19.50 0.02 19.34 0.16
Instance01 Database15 SI_USER SAP_TID 81598 14.13 0.01 14.00 0.13
Instance01 Database15 PTM EI_PARTNER_REPORT 74617 13.39 0.01 13.38 0.02
Instance01 Database15 SI_USER EDI_ELEMENT_CODES 89583 10.63 0.01 10.55 0.08
Instance01 Database15 SI_USER EDI_COMPLIANCE_RPT 37500 10.23 0.01 9.52 0.70
Instance01 Database15 SI_USER DOCUMENT_LIFESPAN 29454 10.10 0.01 8.44 1.66
Instance01 Database15 SI_USER ACTIVITY_INFO 43269 6.00 0.01 5.70 0.30
Instance01 Database15 SI_USER YFS_USER_ACT_AUDIT 14025 4.90 0.00 4.18 0.72
Instance01 Database15 SI_USER BPMV_LS_WRK2 19110 4.20 0.00 2.70 1.50
Instance01 Database15 SI_USER CODELIST_XREF_ITEM 14332 3.50 0.00 2.76 0.74
Instance01 Database15 SI_USER DOC_STATISTICS 8948 3.43 0.00 3.01 0.42
Instance01 Database15 SI_USER MAP 4848 2.37 0.00 2.26 0.11
Instance01 Database15 SI_USER YFS_RESOURCE 5628 1.98 0.00 1.82 0.16
Instance01 Database15 SI_USER MDLR_PAL_ITEM_DESC 4767 1.38 0.00 1.35 0.03
Instance01 Database15 SI_USER SERVICE_PARM_LIST 6290 1.28 0.00 1.12 0.16
Instance01 Database15 SI_USER ADMIN_AUDIT 2100 1.15 0.00 0.76 0.39
Instance01 Database15 SI_USER DOC_STAT_KEY 2860 1.08 0.00 0.82 0.26
Instance01 Database15 SI_USER MAPPER_ERL_XREF 4317 1.07 0.00 1.02 0.05
Instance01 Database15 PTM EI_MSG_PROFILE 4830 0.89 0.00 0.76 0.13
Instance01 Database15 SI_USER WF_INST_S_WRK 2708 0.86 0.00 0.78 0.08
Instance01 Database15 SI_USER YFS_ORGANIZATION 909 0.84 0.00 0.38 0.46
Instance01 Database15 SI_USER YFS_STATISTICS_DETAIL 792 0.83 0.00 0.48 0.35
Instance01 Database15 SI_USER RESOURCE_CHECKSUM 5827 0.76 0.00 0.73 0.02
Instance01 Database15 SI_USER YFS_RESOURCE_PERMISSION 1611 0.73 0.00 0.56 0.16
Instance01 Database15 PTM EI_COMM_PROFILE_AUDIT 1512 0.72 0.00 0.63 0.09
Instance01 Database15 SI_USER WFD 3406 0.72 0.00 0.63 0.09
Instance01 Database15 SI_USER XMLSCHEMAS 1678 0.70 0.00 0.69 0.01

--

This allows you to easily find the largest tables (you can modify the ORDER BY to find the tables with the most free space as well to look for inefficient indexing or design).

Once you have the largest tables in hand you have the starting point for a discussion on potential purges or archives.

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...