-
1. Re: P360 v10.0 database tempdb does not stop growing in filesize
Hi Arjen,
I've experienced the same issue on our PIM SQL servers (dev and production, although it's more prevalent on production). SQL tempdb files will be emptied out upon restart of the MS SQL service but this isn't always an optimal solution.
There are some settings changes that can help prevent tempdb inflation:
(assuming GUI use)
1. In "[Right click] > Properties > Options > Recovery model" make sure that "Simple" is selected
2. In "[Right click] > Properties > Files" you should be able to review the auto-sizing increments and limits. You may want to investigate the auto-sizing that best fits your needs.
Attached I have a script of simple SQL functions that aid in exploring the situation further. PLEASE REVIEW THE FUNCTIONS BEFORE RUNNING ANY OF THEM!
Normally it is considered bad practice to outright shrink a SQL database due to the vicious cycle of having to re-index which then expands the file size and, thus, having to shrink again. Tempdbs are different however in that this rule isn't exactly applicable. I have had success shrinking the tempdb database, files, and logs without consequence (so long as a large task isn't currently running).
To directly answer your questions:
1. It is neither normal nor abnormal, it's not uncommon to happen from time to time but it should not be a frequent occurrence.
2. Infinite database growth (for any of the databases) is a setting that can be changed. If a database is growing infinitely you may just have the default SQL settings active.
Hopefully some of these pointers were able to help. It would be best for you to discuss any actions with your SQL Administrator before attempting. From what I know of PIM SQL; there aren't any specific precautions regarding the tempdb aside from standard SQL best practices.
-
2. Re: P360 v10.0 database tempdb does not stop growing in filesize
Thanks for your swift reply Ryan, much appreciated!
Kind of "glad" to see that you recognize these challenges, at least to have some common ground.
The settings you have been proposing are known to us and used, however we reckon we could turn off autogrow indeed to see what happens when tempdb hits it filesize limit. At the moment SQL server thinks it can autogrow with 2048MB a file until infinity and it tries to do so while the remaning disk space is actually insufficient. Hence it might work when we up front tell the system that it cannot autogrow and hence forcing it to drop records for new records instead -- so thanks for the insights into this experiment already.
Also thanks for your scripts attached, we will scan through these with care!
At some point in time we were wondering whether we had to make schedule a bat file to stop-start P360 server service instead of the SQL database, but even that could cause some real troubles when automated....
We will try out some stuff and revert, thanks again.
Any other tips from other SMEs out here are still welcome ofcourse!
-
3. Re: P360 v10.0 database tempdb does not stop growing in filesize
Hi Arjen, Hi Ryan,
we're currently investigating this issue, if not already done, please open a support case with our support team.
Arjen, please add any exception / error message to the support ticket. Especially with the limited auto grow.
The internal issue number is HPM-54341 just in case you want to mention it to the support representatives
Be assured we're working on this issue top priority!
Can you also reproduce it on a dev or qa system?
Thank you
Andreas
-
4. Re: P360 v10.0 database tempdb does not stop growing in filesize
Hi Arjen,
Can you also share us the MS SQL version please.
Thanks,
Sai Manoj. -
5. Re: P360 v10.0 database tempdb does not stop growing in filesize
Hi Sai Manoj,
MSSQL14
and
SSMS v17.2
-
6. Re: P360 v10.0 database tempdb does not stop growing in filesize
Hi abuehler,
Thanks for your note of importance!
We will be opening a support case on your request, whilst we cannot provide any error messages or so since there is none. This seems to be normal operation until the disk is full, with all the effects that brings with it.
We cannot reproduce this directly on any other environment (DEV,TST,UAT) since these environments are not under daily load of data maintenance activities like PROD env is..
Thanks again!
P.S.
We reckon that setting up server-side cleanup jobs for PIM server processes and soft deletes could maybe help to reduce the stress on the DB, but not sure whether this will improve or worsen the tempdb utilization..
-
7. Re: P360 v10.0 database tempdb does not stop growing in filesize
We were more looking for the full version number, including the hotfixes of sql server.
However, we most likely found the reason for the issue and will provide corresponding ebf's once confirmed.
The support team will keep you updated through the support ticket.
-
8. Re: P360 v10.0 database tempdb does not stop growing in filesize
Setting up the cleanup jobs is always a good idea actually - but it's not related to this issue.
-
9. Re: P360 v10.0 database tempdb does not stop growing in filesize
On behalf of Arjen, letting you all know that we have received a patch file (HPM-54341_server_patch_for_10.0.0.01.05.delta) from Informatica Support to be applied on server/plugins folder.
We will monitor how this goes!
-
10. Re: P360 v10.0 database tempdb does not stop growing in filesize
Hello everyone,
i have the same issue.
IDQ mapping that is running each day now immediatly got stuck and let the tempdb fulfill to the max.
We are reading from C360 table (5-10k is the amount of query result).
We restarted SQL Server+ INFA Plaftorm+MDM and increased the disk space by 200gb (we always worked with 40gb and everything was fine for a year).
After 5 run ofthe same mapping everything was fine.
On the 6th run (same amount of data) temdb occupied 100gb filling all the space.
We also check that after we stopped a mapping that query got stuck and kept running.
How did you solve it?
And is related to INFA or DB?
Regards,
Daniele