How to find out the tablespace usage in RTC
Our CCM tablespace expands 60-100M every day. Originally I thought it is because the baselines created during the build, However it seems that the size of baseline is very small and not noticeable, it must be something else causing the DB expansion. Kryzstof provided a method here https://jazz.net/forum/questions/111357/how-to-check-which-tables-are-using-the-most-space to display the table usage. I tried it out but I don't think the "Content size" in the output provides the real tablespace usage.
=======================================================================================================================================================
ComponentItem TypeItem Count State Count Content Size (K)
=======================================================================================================================================================
Impex ImportRequest 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
admin ComponentIndexingTable 0 0 0
SearchConfiguration 0 0 0
SearchEngine 0 0 0
SearchStatistics 1 1 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
application
migration ApplicationMigrationStatus 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
auth AppCreds 0 0 0
JAuthToken 240 240 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
build BuildActivities 129 129 0
BuildAverageData 31 31 0
BuildDefinition 31 381 0
BuildEngine 15 125 0
BuildEngineActivity 14 14 0
BuildRequest 129 258 0
BuildResult 124 550 0
BuildResultContributions129 129 79264
BuildSecrets 0 0 0
CompileContribution 0 0 0
CompilePackage 0 0 0
JUnitTestClass 0 0 0
JUnitTestSuiteContribution 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
compatibilitypack URIMapping 399 616 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
dashboardDashboard0 0 0
DashboardContributorInfo0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
diagnostictests DiagnosticTestResultItem10 10 52795
-------------------------------------------------------------------------------------------------------------------------------------------------------
discoveryServiceContributionResource 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
dsdef DataSetDefinition 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
enumeration Enumeration 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
filesystem FileItem 1282132 1671037 0
SymbolicLink 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
friends Friend 1 1 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
interop ExternalProxy 9 334 0
ExternalRepositoryConnection 0 0 0
ExternalState 9 325 0
SyncInfo 0 0 0
SyncRule 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
langdef LanguageDefinition 0 0 0
MetadataScanner 0 0 0
Translator 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
linkClosure Relation 100 100 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
links AuditableLink 41784 41784 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
markers Marker 527 527 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
model Attachment 122 122 28514
Attribute1065 1082 0
Category 67 129 0
Deliverable 0 0 0
TimeSheetEntry 1 1 0
WorkItem 430 1167 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
nucleus IterationPlanRecord 97 99 0
SharedPlanMode 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
opaque Opaque 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
plansnapshot BaselineMember 0 0 0
EstimateRollUp 0 0 0
IterationBaselineMember 0 0 0
PlanSnapshot 0 0 0
PlanSnapshotType 0 0 0
ProcessItemBaselineMember 0 0 0
SizeRollUp 0 0 0
WorkItemBaselineMember 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
process AccessGroup 0 0 0
DevelopmentLine 22 25 0
Iteration125 127 0
IterationType 97 99 0
ProcessAttachment 1147 1147 30034
ProcessChangeEventCheck 1 1 0
ProcessDefinition 8 16 3425
ProcessDomainDefinition 0 0 0
ProcessGenericItem 0 0 0
ProcessProviderCacheItem0 0 0
ProjectArea 22 707 127510
ProjectLink 0 0 0
ReadAccessList 22 22 0
TeamArea 58 1593 53
-------------------------------------------------------------------------------------------------------------------------------------------------------
query QueryDescriptor 499 526 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
reports FolderDescriptor 65 65 0
ReportDescriptor 682 682 373743
ReportQueryDescriptor 540 540 0
StorageNode 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
repository AuditableBaseline 0 0 0
ChangeEvent 5401 5401 0
ContributorDetails 398 642 1898
ContributorGroup 0 0 0
ContributorRecord 400 810 0
FloatingLicenseLease 0 0 0
FloatingLicensePolicy 0 0 0
JPIMapping 0 0 0
LicenseAssignment 0 0 0
LicenseKey 0 0 0
LicenseType 0 0 0
MappingEvent 0 0 0
OAuthConsumerRegistration 8 8 0
RepositoryRoot 1 1 0
ServerVersionRecord 35 43 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
resource ApplicationStorageArea 2 2 0
ContributorAbsence 2 2 0
ContributorResourceDetails 3 6 0
DefaultTextDescription 0 0 0
FieldsTextDescription 0 0 0
IndexDescription 0 0 0
IndexEntry 0 0 0
Indexer 0 0 0
Resource 2 342 50
WorkResourceDetails 0 0 0
XMLSchema0 0 0
XMLTextSchema 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
scm Baseline 49626 49640 0
BaselineSet 2083 2083 0
ChangeHistory 54255 54255 0
ChangeSet24134 124520 0
Component235 374 0
ComponentEntry 3485 3485 0
ComponentOwner 235 235 0
Configuration 48221 48221 0
ContributorSCMRecord 92 92 0
Folder 142683 147863 0
FolderLastModifiedInWorkspaceEntry 0 0 0
HistoricBasis 146803 146803 0
Pending 0 0 0
RepositoryProgressMonitor 0 0 0
Suspended0 0 0
Versionable 0 0 0
Workspace927 927 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
scmi AutoLoadConnectionInfo 0 0 0
ProjectToLibraryMap 0 0 0
WorkspaceI 14 14 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
snapshot BaslineMember 0 0 0
CriticalPath 0 0 0
IterationBaselineMember 0 0 0
PlanSnapshot 0 0 0
ProcessnBaselineMember 0 0 0
RollUp 0 0 0
WorkItemBaselineMember 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
social LinkedCommunity 0 0 0
SocialSiteCredential 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
supa ApproveToRemoveEntry 0 0 0
EngineInfo 0 0 0
SUPADeliveryEnvelope 0 0 0
SUPASearchRequest 0 0 0
SUPASearchStatistic 0 0 0
SUPAStatusReport 0 0 0
SUPATermsRequest 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
svn SvnRepository 0 0 0
-------------------------------------------------------------------------------------------------------------------------------------------------------
wiki WikiPage 98 98 0
WikiPageAttachment 2 2 0
2 answers
Hi Kevin,
Can you run the tool tomorrow and compare which artifacts increased the most?
What is the database you are using and how are you checking its size?
What comes to my mind is that there are indexes dramatically increasing. You can try running repotools-ccm -reindex to check if it fixes the issue.
Can you run the tool tomorrow and compare which artifacts increased the most?
What is the database you are using and how are you checking its size?
What comes to my mind is that there are indexes dramatically increasing. You can try running repotools-ccm -reindex to check if it fixes the issue.
Comments
Hi Krzysztof,
We are using Oracle as the database and I'm checking the space usage from "DB Instance console -> Space summary" and it display the sizes for tables like CCM, CCM_TMP, JTS, etc. The one that increases everyday is CCM.
Do I need to shut down the server/DB when I run the repotools-ccm -reindex command?
I will re-run your method later to check what artifacts increased the most.
By the way, our server was upgraded to V4.0 from V3.0.1 about 5 months ago. And it seems that we only have this space issue since the server upgrade - hope this can provide some hints.
Kevin,
You are not able to run this command when the server is running. You have to stop server first.
The tablespace grows 129MB between Dec, 10 and 11.
I compared the IDBTableSizeHttpService reports for Dec, 10 and 11 and here is the difference. Please advice what we should do at this stage since the problem becomes more and more severe.
====== 2013-12-10/2013-12-11 =======
JAuthToken 349/524 349/524 0/0
build BuildActivities 128/127 128/127 0/0
BuildRequest 128/127 256/254 0/0
BuildResult 124/127 550/562 0/0
BuildResultContributions 128/127 128/127 79261/79273
diagnostictests DiagnosticTestResultItem 10/10 10/10 53057/53367
filesystem FileItem 1282167/1284755 1671551/1674390 0/0
linkClosure Relation 100/102 100/102 0/0
links AuditableLink 41786/41791 41786/41791 0/0
markers Marker 529/535 529/535 0/0
model Attachment 122/123 122/123 28514/28528
WorkItem 430/431 1167/1172 0/0
TeamArea 58/58 1595/1600 53/53
ChangeEvent 5712/6086 5712/6086 0/0
Resource 2/2 346/350 50/51
scm Baseline 49743/49883 49757/49897 0/0
BaselineSet 1708/1717 1708/1717 0/0
ChangeHistory 54215/54415 54215/54415 0/0
ChangeSet 24207/24296 124852/125269 0/0
Component 236/241 375/380 0/0
ComponentEntry 3321/3370 3321/3370 0/0
ComponentOwner 236/241 236/241 0/0
Configuration 48173/48353 48173/48353 0/0
Folder 142690/142999 147871/148180 0/0
HistoricBasis 147096/147540 147096/147540 0/0
RepositoryProgressMonitor 1 /0 1/0 0/0
Workspace 874/879 874/879 0/0