Hi,

We are running Bulk Move sweeps to transfer 127 million documents from a file store to an Azure Blob Advanced File Store.

We're using the "FilteredQueryTimeout" setting to ensure that the Filter Expression is included in the initial query, and that query is using an indexed field (create_date), so the sweep is not doing the standard thing of examining all documents.

Batch size is set to 5000 and Number of workers is 8. Each sweep moves about 1 million documents.

We've also been running two sweeps in parallel covering different date_ranges. So far this has gone well over two days/nights moving over 5 million docs, until contacted by the customer that the SQL Server TempDB had filled up due to the sweeps.

Does anyone know what factors in a sweep lead to such usage of the TempDB? Are these not released after each query? What in a sweep would cause a continual growth in the TempDB? Are there any steps we can take to minimise this (batch size, workers, smaller document numbers, anything less obvious etc?)

There are no error messages. No warning messages. Nothing wrong on the FileNet/WebSphere side - until the post Full TEMPDB error messages begin to appear.

The customer is going to (greatly) expand the available disk space for the TempDB from 75GB to 300GB, so this should mask the issue in future. But I'm thinking this shouldn't be happening at all.....

Any suggestions greatly appreciated.

Many thanks

Please open a support ticket so that this issue can be investigated properly. Thanks

Very well written question. Looks like you are doing everything correctly. I would be very interested to understand how to avoid the issue you are seeing.

Agreed this is likely better served in a support ticket but then we don't all get to learn what made this happen.

Hi. Yes. Already did raise a ticket at the same time, but I thought it was worth trying here as well.. Thanks. I'll let you know if there's a magic bullet solution.

Hopefully once we determine the cause of the problem, we can get a technote written to explain how to avoid the issue moving forward

Something I didn't add because I thought it was irrelevant is that the system in question has BAW along side CPE and ICN. BAW has its own databases. One of them has a long running FETCH_API_CURSORXXXXXX statement running from BAW, which appears to lock the tempdb somehow and cause the tempdb to fill during a sweep because resources are not released. Restarting the BAW SingleClusterMember service cause this staement to be restarted - releasing all the stuff in tempdb again.

So going to raise another ticket this time asking ""Why does BAW permanently lock the TempDB?" rather than "why do sweeps fill the tempdb......"

So if those who showed an interest in this issue don't have BAW then there shouldn't be a problem....

Even more interested to know what is happening now. Is it a coincidence? I can't imagine a storage move causing something to trigger in BAW. Please keep us posted.

Even more interested to know what is happening now. Is it a coincidence? I can't imagine a storage move causing something to trigger in BAW. Please keep us posted.

It's not BAW being triggered. BAW is doing its own thing but while doing so seems to prevent the tempdb from releasing the resources added during the sweeps.

Hi. Just to close this out (someone had requested an update to the resolution). The BAW database creation scripts specify Read Committed Snapshot Isolation (RCSI) be set on. We determined on our own systems that turning this off on the BAW databases stopped the problem happening. We asked IBM Support if it was mandatory to have this setting and were told they thought it would be OK to turn it off, which we did on the customer's systems. There was no explanation from them as to why RCSI caused this issue.