Windchill Shrink DB

Question

I'm trying to shrink the primary file for my Windchill database.  When I try, it has taken over a day to try to go form 25GB to 13GB.  I think that the culprit might be the entryNumber_seq table.  It has the largest record count and through a query, it is what the shrink stays on the longest.  It is the largest table record wise with 77 million rows.  Does anyone know what it is really for?  It has only two columns and one is just an 'x'. Can it be truncated?

mmeadows-3 · Accepted Answer

This is the response from PTC Technical Support...

Regarding the case, kindly refer to the below information and the article attached.

We do have customers who have much more data on the sequence table, e.g. as below.
That is not a problem, just means the system has big data and need big sequence ids.

Table Name                                                                          rows
-------------------------------------------------------            --------------------
entryNumber_seq                                                         256541430
id_sequence                                                                   67836810

While it is fine to truncate, just follow steps here - just change to entryNumber_seq; Refer to CS202101 and steps suggested below:
The size of the sequence table may be causing a perf issue, but the solution here is to truncate the sequence table and reseed the sequence.
The instructions to reseed a sequence are in CS153950;

Note: Please have a complete backup of database firstly.

The below SQL can be used to clear out the entries from id_sequence. Windchill must be stopped before running these commands:

First, get the current identity value for the table:
- DBCC CHECKIDENT('id_sequence');
Then, truncate the table:
- TRUNCATE TABLE id_sequence;
The truncate command will reset the identity value, so we must reseed the table:
- DBCC CHECKIDENT('id_sequence',RESEED,<Identity value from first statement>);

Let me know if anything comes out of the above suggestions. Feel free to contact me if you have more concerns related to the issue.

So, we can truncate sequence tables. Ultimately that is what we did and our shrink operation continued forward without further issues.

avillanueva · Answer

Not sure that's a table. Using SQL Server right? In Oracle, that is a sequence. Likely its the source of IDA2A2 values. I have 144 Million numbers used. Perhaps SQL Server does that differently. But I do not think its the source of your file size in the DB. If this still works from 2005:

https://stackoverflow.com/questions/3927231/how-can-you-tell-what-tables-are-taking-up-the-most-space-in-a-sql-server-2005-d

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.