PK table in ODX database is growing rapidly in terms of disk space

Hi Thomas,

Thanks for the quick reply, I'm only wondering. Is it intended to work like this?

I understand the purpose of the PK table. But if you save the last 2 loads/batches in this PK table should be enough right?

Is it really needed to save all primary keys for every load/batch? 

Hi @rory.smith,

Replaying history can be a reason to save the PK's for all batches. I know that this option is quite expensive in performance. But I didn't know all PK's for all batches are saved.

Nevertheless, it would be great if we have the option to choose how many batches need to be saved in the PK tables. Maybe the storage management task can be used for this. This way we can save a lot of disk space for the PK tables.

Please post and upvote this in the idea section. Many thanks! 

Hi all, 

As a temporary workaround, I've created a dynamic SQL script to truncate all ‘PK’ tables:

_____________________________________________________________________

USE TX_ODX_LIVE

DECLARE @TableName   NVARCHAR(128)
DECLARE @TableSchema NVARCHAR(128)
DECLARE @SQL         NVARCHAR(MAX)

-- Create a cursor to loop through table names
DECLARE table_cursor CURSOR FOR
SELECT table_name , table_schema
FROM information_schema.tables a
WHERE TABLE_TYPE = 'BASE TABLE' AND table_name LIKE '%_PK_%'

-- Loop through the tables and generate TRUNCATE TABLE statements
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName, @TableSchema

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'TRUNCATE TABLE ' + @TableSchema +'.[' + @TableName + ']'
    EXEC sp_executesql @SQL

    FETCH NEXT FROM table_cursor INTO @TableName, @TableSchema
END

-- Close and deallocate the cursor
CLOSE table_cursor
DEALLOCATE table_cursor

_____________________________________________________________________

Please be aware: Do not run this script while reloads are running.

I created a idea for this a while ago: 

Let's upvote this idea and hope it will be implemented quickly.