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!
No comments:
Post a Comment