Caused by: java.sql.BatchUpdateException: Could not allocate a new page for database ‘TEMPDB’

sadrangans · March 6, 2023, 12:54pm

Hi , we are running a analysis on Atlas (2.10) and getting the temp error. We have enough temp db (6 TB approx) to run extensive queries. We have tempdb issues with Achilles, we resolved it by adding some indexes. In this scenario we can’t indexes as these are the temp tables. Any idea how can optimise the queries within Atlas. Does new version of Atlas have indexes on temp tables.

Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [with cteEndDates (person_id, end_date) AS – the magic
(
SELECT
person_id
, DATEADD(day,-1 * 0, event_date) as end_date
FROM
(
SELECT
person_id
, event_date
, event_type
, MAX(start_ordinal) OVER (PARTITION BY person_id ORDER BY event_date, event_type ROWS UNBOUNDED PRECEDING) AS start_ordinal
, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY event_date, event_type) AS overall_ord
FROM
(
SELECT
person_id
, start_date AS event_date
, -1 AS event_type
, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY start_date) AS start_ordinal
FROM #cohort_rows

		UNION ALL
	

		SELECT
			person_id
			, DATEADD(day,0,end_date) as end_date
			, 1 AS event_type
			, NULL
		FROM #cohort_rows
	) RAWDATA
) e
WHERE (2 * e.start_ordinal) - e.overall_ord = 0

),
cteEnds (person_id, start_date, end_date) AS
(
SELECT
c.person_id
, c.start_date
, MIN(e.end_date) AS end_date
FROM #cohort_rows c
JOIN cteEndDates e ON c.person_id = e.person_id AND e.end_date >= c.start_date
GROUP BY c.person_id, c.start_date
)
select person_id, min(start_date) as start_date, end_date
into #final_cohort
from cteEnds
group by person_id, end_date
; DELETE FROM RESULTS.cohort_cache where design_hash = 606330993; INSERT INTO RESULTS.cohort_cache (design_hash, subject_id, cohort_start_date, cohort_end_date)
select 606330993 as design_hash, person_id, start_date, end_date
FROM #final_cohort CO
; delete from RESULTS.cohort_censor_stats_cache where design_hash = 606330993; TRUNCATE TABLE #strategy_ends; DROP TABLE #strategy_ends; TRUNCATE TABLE #cohort_rows; DROP TABLE #cohort_rows; TRUNCATE TABLE #final_cohort; DROP TABLE #final_cohort; TRUNCATE TABLE #inclusion_events; DROP TABLE #inclusion_events; TRUNCATE TABLE #qualified_events; DROP TABLE #qualified_events; TRUNCATE TABLE #included_events; DROP TABLE #included_events; TRUNCATE TABLE #Codesets; DROP TABLE #Codesets]; SQL state [null]; error code [0]; Failed to execute batch update; nested exception is java.sql.SQLException: Failed to execute batch update
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:418)
at org.ohdsi.webapi.util.CancelableJdbcTemplate.batchUpdate(CancelableJdbcTemplate.java:102)
at org.ohdsi.webapi.generationcache.GenerationCacheHelper.runCancelableCohortGeneration(GenerationCacheHelper.java:82)
at org.ohdsi.webapi.cohortcharacterization.GenerateLocalCohortTasklet.lambda$generateCohort$4(GenerateLocalCohortTasklet.java:125)
at org.ohdsi.webapi.generationcache.GenerationCacheHelper.lambda$computeCacheIfAbsent$1(GenerationCacheHelper.java:64)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
at org.ohdsi.webapi.generationcache.GenerationCacheHelper.computeCacheIfAbsent(GenerationCacheHelper.java:51)
at org.ohdsi.webapi.cohortcharacterization.GenerateLocalCohortTasklet.generateCohort(GenerateLocalCohortTasklet.java:123)
at org.ohdsi.webapi.cohortcharacterization.GenerateLocalCohortTasklet.lambda$null$2(GenerateLocalCohortTasklet.java:99)
at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1384)
at java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:580)
at org.ohdsi.webapi.cohortcharacterization.GenerateLocalCohortTasklet.lambda$execute$3(GenerateLocalCohortTasklet.java:99)
at java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1640)
… 3 more
Caused by: java.sql.SQLException: Failed to execute batch update
at org.ohdsi.webapi.util.CancelableJdbcTemplate$1BatchUpdateStatementCallback.doInStatement(CancelableJdbcTemplate.java:74)
at org.ohdsi.webapi.util.CancelableJdbcTemplate$1BatchUpdateStatementCallback.doInStatement(CancelableJdbcTemplate.java:44)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
… 15 more
Caused by: java.sql.BatchUpdateException: Could not allocate a new page for database ‘TEMPDB’ because of insufficient disk space in filegroup ‘DEFAULT’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeBatch(SQLServerStatement.java:1845)
at org.ohdsi.webapi.util.CancelableJdbcTemplate$1BatchUpdateStatementCallback.doInStatement(CancelableJdbcTemplate.java:58)
… 17 more

Chris_Knoll · March 6, 2023, 2:39pm

Short answer is no: we don’t apply indexes on temp tables, typically because temp tables are holding results, and joins with these tables typically involve full table scans (ie: we’re not selecting specific rows out of the temp table to return, all rows are used). We also have limitations on what sort of index functionality is supported on the multiple DBMS platforms our CDM analysis sql is supported on.

The Atlas UI allows you to see the SQL that is generated, is it possible to copy the SQL and execute the query to determine where the error occurs (it’s happening in batch in the app).

Also, I find it hard to believe a 6TB temp db would run out of space, could you provide some info about the number of people in your DB and the estimated number of people you think would match in the cohort? Also, if you could provide the cohort definition that you are trying to execute, maybe there’s a way to optimize it there.

Also, could you let me know what the filegroup ‘DEFAULT’ is configured as? It’s one thing to have the space, but another to have it allocated to a filegroup. I’m referring to this error:

Could not allocate a new page for database ‘TEMPDB’ because of insufficient disk space in filegroup ‘DEFAULT’.