Last active
October 16, 2019 15:20
-
-
Save ddemeyer/3acd847a9c375be22faea9042c5597d7 to your computer and use it in GitHub Desktop.
What is using storage in my Microsoft SQLServer-based database of Tridion Docs Content Manager (= Knowledge Center Content Manager = LiveContent Architect = Trisoft InfoShare). In all cases make sure your database jobs are running successfully to keep BackgroundTaskService and EventMonitorService under control. Remember that a big consumer is ke…
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- 20191016/ddemeyer | |
| -- Not a set of beautiful queries but it at all works on all Microsoft SQLServer-based | |
| -- databases of Tridion Docs Content Manager (= Knowledge Center Content | |
| -- Manager = LiveContent Architect = Trisoft InfoShare). | |
| -- | |
| -- Note... | |
| -- In all cases make sure your database jobs are running successfully to keep | |
| -- BackgroundTaskService and EventMonitorService under control. Remember that a big consumer | |
| -- is keep revisions around. | |
| -- Last tested on 14/14.0.0 | |
| ---------------------------------------------------------------------------------------------- | |
| -- Selecting per table/ISHType the COUNT/MIN/MAX | |
| -- ELECTRONIC DOCUMENT COUNT - CTPUBLICATIONOUTPUT COUNT / CTMAPL COUNT ~= #REVISIONS | |
| ---------------------------------------------------------------------------------------------- | |
| SELECT 'DateValuesObject' AS 'NAME', CONVERT(NVARCHAR(40), COUNT(*)) AS 'COUNT', (CAST(MIN(VALUE) AS NVARCHAR(40))) AS 'MIN', (CAST(MAX(VALUE) AS NVARCHAR(40))) AS 'MAX' FROM DATE_VALUES | |
| UNION ALL | |
| SELECT 'NumberValuesObject' AS 'NAME', CONVERT(NVARCHAR(40), COUNT(*)), (CAST(MIN(VALUE) AS NVARCHAR(40))), (CAST(MAX(VALUE) AS NVARCHAR(40))) FROM NUMBER_VALUES | |
| UNION ALL | |
| SELECT 'TotalStringValuesObject' AS 'NAME', CONVERT(NVARCHAR(40), COUNT(*)), 'n/a', 'n/a' FROM STRING_VALUES | |
| UNION ALL | |
| SELECT 'StringValuesObject' AS 'NAME', CONVERT(NVARCHAR(40), COUNT(*)), 'n/a', 'n/a' FROM STRING_VALUES where FIELD_ID in (select FIELD_ID from FIELD where TYPE = 'STRING') | |
| UNION ALL | |
| SELECT 'LongStringValuesObject' AS 'NAME', CONVERT(NVARCHAR(40), COUNT(*)), 'n/a', 'n/a' FROM STRING_VALUES where FIELD_ID in (select FIELD_ID from FIELD where TYPE = 'LONG') | |
| UNION ALL | |
| SELECT 'CardValuesObject' AS 'NAME', CONVERT(NVARCHAR(40), COUNT(*)), (CAST(MIN(VALUE) AS NVARCHAR(40))), (CAST(MAX(VALUE) AS NVARCHAR(40))) FROM CARD_VALUES | |
| UNION ALL | |
| SELECT 'DomainValuesObject' AS 'NAME', CONVERT(NVARCHAR(40), COUNT(*)), (CAST(MIN(VALUE) AS NVARCHAR(40))), (CAST(MAX(VALUE) AS NVARCHAR(40))) FROM DOMAIN_VALUES | |
| UNION ALL | |
| SELECT 'ContentObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(LNG_CARD_ID)), CONVERT(NVARCHAR(40), MAX(LNG_CARD_ID)) FROM ISH_CONTENTOBJECT | |
| UNION ALL | |
| SELECT 'PublicationOutputObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(LNG_CARD_ID)), CONVERT(NVARCHAR(40), MAX(LNG_CARD_ID)) FROM ISH_PUBLICATIONOUTPUTOBJECT | |
| UNION ALL | |
| SELECT 'PageObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(PAGE_ID)), CONVERT(NVARCHAR(40), MAX(PAGE_ID)) FROM ALL_PAGES | |
| UNION ALL | |
| SELECT 'CardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD | |
| UNION ALL | |
| SELECT 'ElectronicDocumentCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='ELECTRONIC DOCUMENT') | |
| UNION ALL | |
| SELECT 'FolderCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTDOCMAP') | |
| UNION ALL | |
| SELECT 'MapLogicalCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTMASTER') | |
| UNION ALL | |
| SELECT 'MapVersionCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTMASTERV') | |
| UNION ALL | |
| SELECT 'MapLogicalCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTMASTERL') | |
| UNION ALL | |
| SELECT 'TopicLogicalCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTMAP') | |
| UNION ALL | |
| SELECT 'TopicVersionCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTMAPV') | |
| UNION ALL | |
| SELECT 'TopicLanguageCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTMAPL') | |
| UNION ALL | |
| SELECT 'ImageLogicalCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTIMG') | |
| UNION ALL | |
| SELECT 'ImageVersionCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTIMGV') | |
| UNION ALL | |
| SELECT 'ImageLanguageCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTIMGL') | |
| UNION ALL | |
| SELECT 'OtherLogicalCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTTEMPLATE') | |
| UNION ALL | |
| SELECT 'OtherVersionCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTTEMPLATEV') | |
| UNION ALL | |
| SELECT 'OtherLanguageCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTTEMPLATEL') | |
| UNION ALL | |
| SELECT 'LibraryLogicalCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTLIB') | |
| UNION ALL | |
| SELECT 'LibraryVersionCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTLIBV') | |
| UNION ALL | |
| SELECT 'LibraryLanguageCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTLIBL') | |
| UNION ALL | |
| SELECT 'ROLogicalCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTREUSEOBJ') | |
| UNION ALL | |
| SELECT 'ROVersionCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTREUSEOBJV') | |
| UNION ALL | |
| SELECT 'ROLanguageCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTREUSEOBJL') | |
| UNION ALL | |
| SELECT 'PublicationLogicalCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTPUBLICATION') | |
| UNION ALL | |
| SELECT 'PublicationVersionCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTPUBLICATIONV') | |
| UNION ALL | |
| SELECT 'PublicationOutputCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTPUBLICATIONOUTPUT') | |
| UNION ALL | |
| SELECT 'ContextCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTCONTEXT') | |
| UNION ALL | |
| SELECT 'BaselineCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTBASELINE') | |
| UNION ALL | |
| SELECT 'OutputFormatCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTOUTPUTFORMAT') | |
| UNION ALL | |
| SELECT 'TranslationJobCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTTRANSJOB') | |
| UNION ALL | |
| SELECT 'AnnotationCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTANNOTATIONINTERNAL') | |
| UNION ALL | |
| SELECT 'AnnotationReplyCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTANNOTATIONINTERNALREPLY') | |
| UNION ALL | |
| SELECT 'UserCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='USER') | |
| UNION ALL | |
| SELECT 'UserRoleCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTUSERROLE') | |
| UNION ALL | |
| SELECT 'UserGroupCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='CTUSERGROUP') | |
| UNION ALL | |
| SELECT 'EDTCardObject', CONVERT(NVARCHAR(40), COUNT(*)), CONVERT(NVARCHAR(40), MIN(CARD_ID)), CONVERT(NVARCHAR(40), MAX(CARD_ID)) FROM CARD WHERE TYPE_CARD_ID IN (SELECT CARD_ID FROM CARD WHERE IS_NAME='EDT') | |
| ---------------------------------------------------------------------------------------------- | |
| -- Indicates the amount of slave Full-Text-Index database connected to this CMS database | |
| ---------------------------------------------------------------------------------------------- | |
| SELECT * FROM ISH_CRAWLER | |
| ---------------------------------------------------------------------------------------------- | |
| -- Which Blob electronic types take up MBs in the ALL_PAGES table. | |
| -- NOTE | |
| -- 1. The pagelength is accurate, but actual room to save could be more | |
| -- 2. By using 'readpast' this query is not locked by any open transactions | |
| ---------------------------------------------------------------------------------------------- | |
| SELECT edt.IS_NAME AS 'DocumentType', | |
| COUNT(ap.PAGE_ID) 'NumberOfBlobs', | |
| CEILING(SUM(ap.PAGE_LENGTH) / 1024 / 1024) AS 'BlobSizeMB' | |
| FROM DOCUMENT_VALUES dv with (readpast), | |
| ALL_PAGES ap with (readpast), | |
| CARD_VALUES cv with (readpast), | |
| CARD edt with (readpast) | |
| WHERE dv.FIELD_ID=(SELECT field_id FROM FIELD WHERE ELEMENT='IS_PAGE') AND dv.value=ap.PAGE_ID | |
| AND cv.CARD_ID=dv.CARD_ID AND cv.field_id=(SELECT field_id FROM FIELD WHERE ELEMENT='EDT') | |
| AND cv.VALUE=edt.CARD_ID and edt.TYPE_CARD_ID = (SELECT CARD_ID FROM CARD WHERE IS_NAME='EDT') | |
| GROUP BY edt.IS_NAME | |
| ORDER BY 'BlobSizeMB' DESC | |
| ---------------------------------------------------------------------------------------------- | |
| -- Sys queries grouped per InfoShare capability/service. Most likely 'MetadataService' and 'BlobService' are the big consumers. | |
| ---------------------------------------------------------------------------------------------- | |
| declare @MemoryUsed table(TableName sysname, RowCounts bigint, TotalSpaceMB int, UsedSpaceMB int, UnusedSpaceMB int,ContainsBlob nchar(1), ISHServiceType nvarchar(40)) | |
| insert into @MemoryUsed(TableName, RowCounts, TotalSpaceMB, UsedSpaceMB, UnusedSpaceMB, ContainsBlob, ISHServiceType) | |
| select mu.TableName, | |
| mu.RowCounts, | |
| mu.TotalSpaceMB, | |
| mu.UsedSpaceMB, | |
| mu.UnusedSpaceMB, | |
| (case when t.lob_data_space_id = 1 then 'Y' else 'N' end) ContainsBlob, | |
| (case when t.name = 'ALL_PAGES' then 'BlobService' | |
| when t.name = 'DOCUMENT_VALUES' then 'BlobService' | |
| when t.name = 'INDEX_MESSAGE' then 'FullTextIndexService' | |
| when t.name = 'ISH_CRAWLER' then 'FullTextIndexService' | |
| when CHARINDEX('ISH_TOINDEX', t.name)>0 then 'FullTextIndexService' | |
| when t.name = 'ISH_EXTERNALTRANSJOB' then 'TranslationService' | |
| when CHARINDEX('ISH_TRANSJOB', t.name)>0 then 'TranslationService' | |
| when CHARINDEX('ISH_BT', t.name)>0 then 'BackgroundTaskService' | |
| when CHARINDEX('ISH_EVENTPROGRESS', t.name)>0 then 'EventMonitorService' | |
| when t.name = 'ISH_SESSIONSECURITYTOKEN' then 'AuthenticationService' | |
| when t.name = 'ISH_SETUP_HISTORY' then 'SetupService' | |
| else 'MetadataService' end) ISHServiceType | |
| from (SELECT t.NAME AS TableName, | |
| t.object_id TableId, | |
| p.rows AS RowCounts, | |
| SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB, | |
| SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB, | |
| (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB | |
| FROM sys.tables t | |
| 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%' | |
| AND t.is_ms_shipped = 0 | |
| AND i.OBJECT_ID > 255 | |
| GROUP BY t.Name, t.object_id, p.Rows) mu | |
| inner join sys.tables t on (mu.TableId = t.object_id) | |
| inner join sys.schemas s on (s.schema_id = t.schema_id) | |
| select * from @MemoryUsed | |
| order by ISHServiceType, TableName | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment