SSAS tabular processing error: Could not allocate a new page for database 'TEMPDB' because...

Question

Hi,

We get an error while processing a tabular model on sql server 2017 (v 14.0.3421.10).

The error is:

Executed as user: NT Service\SQLSERVERAGENT. Microsoft.AnalysisServices.Xmla.XmlaException: The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: '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.. The exception was raised by the IDataReader interface.'.. at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.ExecuteStatement(String statement, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalog) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.SendCommand(String command, Boolean withResults, String properties) at OlapEvent(SCH_STEP* pStep, SUBSYSTEM* pSubSystem, SUBSYSTEMPARAMS* pSubSystemParams, Boolean fQueryFlag)

I do not know which TempDB it is referring to.
All disks have enough space available.
I checked the TempDir setting of the Analysis server. That folder is empty. (?)
The TempDB files of the sql server were incresed by a large amount, also the growth steps are big. (not sure if that tempdb has anything to do with it: ssas is called SERVER\TM instance, while the db engine is the default instance.

Can anyone point me in the right direction? Or maybe even the solution?

Many thanks, Edgar

Answer 1

Hi @Edgar Walther (ITsmart) ，

Failed to execute XMLA. Error returned: '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..

The cause of this error may be that SQL Server has run out of space in the TEMPDB database.

• Scenario#1 - At least one of the SQL server's hard drives is full
• Scenario#2 - The TEMPDB has been configured to have a maximum size, and it has not reached it and cannot grow any further.

Please try the following solutions:

Scenario#1 - Free up hard drive space on the SQL server

Scenario#2 - Increase the allocated space for TEMPDB, and also (ideally) unrestrict its maximum size.

Please review the following to optimize Tempdb at the same time:
How to optimize tempdb performance.
Recommendations to reduce allocation contention in SQL Server tempdb database.

Note：If you regularly reboot your SQL database server (e.g. during a period of downtime at the weekend) then the TEMPDB will automatically be deleted/recreated from a copy of the 'model' database. This ensures that it does not grow too large.

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I do not know which TempDB it is referring to.

TempDB is a system database of the relational MS SQL Server, see https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16
It's e.g. heavy utilize to sort data fetched from SSAS for data processing.