vRealize Automation 7.x upgrade fails with: Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• When upgrading vRealize Automation from 7.x to 7.x, say 7.2 to 7.3, IaaS database upgrade task fails with:

[8/18/2017 10:18:03 AM] System.Data.SqlClient.SqlException (0x80131904): Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. 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.
[8/18/2017 10:18:03 AM] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
[8/18/2017 10:18:03 AM] at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
[8/18/2017 10:18:03 AM] at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

Environment

VMware vRealize Automation 7.x

Cause

Tempdb is a system level database that is utilized by all databases running on the Microsoft SQL (MSSQL) server and as such is not and should not be touched by vRealize Automation.

The autogrow setting has dramatic effect on performance.  Generally, a database administrator should consider the usage by all databases (not just vRealize Automation) before modifying this setting.

By default in all MSSQL versions before 2016 R1 this value was set to "10%, unlimited" which caused performance issues for many other database consumers.

After the release of MSSQL 2016 R1 this setting is set to by "64MB, unlimited" which provides better performance but causes issues for the vRealize Automation upgrade.

Resolution

Revert Autogrowth settings:

1. Log into SQL Server Management Studio with a user that has sysadmin privileges.
2. Once logged in, expand System Databases, right-click the tempdb and click on Properties
3. Click on 'Files' in the left pane and then click on the ellipses button
4. In the 'Change Autogrowth' window make sure 'Enable Autogrowth' is selected, and change File Growth section to 'In Percent' and select 10. Make sure the Maximum File Size field is set to unrestricted File Growth.
5. After clicking OK the new settings should be set. Roll back your database and snapshots from the failed upgrade and attempt again.

A database administrator may return the old setting once the upgrade is successfully complete.

Additional Information

References:

• https://blogs.msdn.microsoft.com/john_daskalakis/2013/11/25/sql-server-default-settings-that-you-might-want-to-change/
• https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
• https://blogs.sentryone.com/aaronbertrand/sql-server-2016-tempdb-fixes/