The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

-> I was performing an In-Place upgrade of SQL Server from SQL Server 2017 to SQL Server 2019.

-> The upgrade failed with below error,

TITLE: Microsoft SQL Server 2019 Setup
------------------------------

The following error has occurred:

The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

For help, click: https://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=15.0.4013.40&EvtType=0xD8FB5EBA%25400x97A656BB%25401306%254067

------------------------------
BUTTONS:

OK
------------------------------

-> The error states that there is some issue with the data directory.

-> I right clicked on the SQL Server instance and clicked on properties. Clicked on “Database Settings” and verified “Database default locations”. The data location mentioned is “F:\data1\”. This location is not available on the database server.

-> I corrected it to F:\data which is available on the database server and then clicked OK. Started the In-Place upgrade again and it completed fine.

-> This issue also happens during Service pack and cumulative updates install also. We should make sure that the “Data” and “Log” location is correct under “Database Settings” in SQL Server Instance properties.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Cannot open database ‘JBDB’ version 869. Upgrade the database to the latest version. (Microsoft SQL Server, Error: 946)

Environment

-> Database Server JBSAG1 and JBSAG2 hosts SQL Server 2017. The requirement was to perform an In-Place upgrade to SQL Server 2019 on JBSAG1 and JBSAG2.

-> Database Server JBSAG1 is secondary replica and JBSAG2 is primary replica.

-> In-place upgrade from SQL Server 2017 to SQL Server 2019 was started on JBSAG1 as it is the secondary replica. It completed fine without any issues.

-> After the upgrade, connected to JBSAG1 using SSMS and performed a health check. The database JBDB on SQL Server instance JBSAG1 showed as Synchronized / In Recovery as show in below screenshot,

-> Checked SQL Server error log and found below message,

2020-09-10 15:05:27.020 spid53 Error: 946, Severity: 14, State: 1.
2020-09-10 15:05:27.020 spid53 Cannot open database ‘JBDB’ version 869. Upgrade the database to the latest version.

Cannot open database ‘JBDB’ version 869. Upgrade the database to the latest version. (Microsoft SQL Server, Error: 946)

-> The message is very clear. After the In-place upgrade, the database should be upgraded to latest version which is SQLS erver 2019, since the database is in Always on secondary, setup process was not able to upgrade it during the upgrade process.

-> Checking the Always On availability group dashboard from primary replica JBSAG2,

-> There are no errors and Always On availability group is in healthy state. So it is safe to failover Availability group from JBSAG2 to JBSAG1. Once after the failover, the database will get upgraded.

-> After the failover database JBDB in JBSAG1 is online and synchronized,

-> Checking the Always On dashboard,

-> JBSAG2 which is the secondary replica now and shows database JBDB as “Not Synchronizing / In Recovery”. On JBSAG2, check the database JBDB status under Availability databases and you will see that the database is paused.

-> We are seeing this in paused state because after failover the current Primary JBSAG1 is in SQL Server 2019 and the secondary JBSAG2 is in SQL Server 2017. Now it is time to perform an In-Place upgrade to SQL Server 2019 on JBSAG2 and the database JBDB in “Not Synchronizing / In Recovery” state in JBSAG2 is expected.

<TESTING ONLY – DONOT PERFORM ON LIVE SERVERS>

-> I will try resuming the database JBDB on JBSAG2 without
performing an In-Place upgrade from SQL Server 2017 to SQL
Server 2019.

-> Checking SQL Server error log. Below message can be found,


2020-09-10 15:20:21.490 spid71 ALTER DB param option: RESUME
2020-09-10 15:20:21.500 spid71 Always On Availability Groups data
movement for database ‘JBDB’ has been resumed. This is an
informational message only. No user action is required.
2020-09-10 15:20:21.500 spid77s Always On Availability Groups
connection with primary database established for secondary
database ‘JBDB’ on the availability replica ‘JBSAG1’ with Replica ID:
{850de16e-2c2b-43b1-bf52-6d8c5e2046ff}. This is an informational
message only. No user action is required.


2020-09-10 15:20:21.500 spid77s Error: 948, Severity: 20, State:
102.
2020-09-10 15:20:21.500 spid77s The database ‘JBDB’ cannot be
opened because it is version 904. This server supports version
869 and earlier. A downgrade path is not supported.


2020-09-10 15:20:21.500 spid77s Always On Availability Groups
data movement for database ‘JBDB’ has been suspended for
the following reason: “system” (Source ID 7; Source string:
‘SUSPEND_FROM_REVALIDATION’). To resume data
movement on the database, you will need to resume the
database manually. For information about how to resume an
availability database, see SQL Server Books Online.

-> The message clearly tells that the database should be upgraded
before resuming the data movement. Hence In-Place upgrade of
JBSAG2 should be performed.

<TESTING ONLY – DONOT PERFORM ON LIVE SERVERS>

-> Performed In-Place upgrade from SQL Server 2017 to SQL Server 2019 on database server JBSAG2.

-> I checked the Always On dashboard. The database JBDB was still “Not Synchronizing / In Recovery” and was suspended.

-> I resumed the data movement for database JBDB on database server JBSAG2 and it worked fine this time. Below is the Always On dashobaord,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

SQL Server Setup – Could not allocate space for object ‘dbo.Large Object Storage System object: 422212467425280’ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full

-> I installed SQL Server 2019 and then tried applying Cumulative update 5 on top of it.

-> SQL Server 2019 CU5 failed with below error,

TITLE: SQL Server Setup failure.
SQL Server Setup has encountered the following error:
The NT service ‘SQLAgent$IN01’ could not be started.
Error code 0x84B20001.


For help, click: https://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xAE7B8BF3%25400xF762992B%25401202%25401

BUTTONS:
OK

-> SQL Server was down. Received below error when tried starting SQL Service manually from configuration manager,

SQL Server Configuration Manager

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.

OK

-> Checking SQL Server error log as the above error is very generic,

2020-08-02 14:51:26.78 Server Microsoft SQL Server 2019 (RTM-CU5) (KB4552255) – 15.0.4043.16 (X64)
Jun 10 2020 18:25:25
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

-> SQL Server error log shows that SQL Server is upgraded to 15.0.4043.

-> Checking further in SQL Server error log,

2020-08-02 14:51:30.26 spid9s Database ‘master’ is upgrading script ‘msdb110_upgrade.sql’ from level 251660240 to level 251662283.
2020-08-02 14:51:30.26 spid9s ———————————-
2020-08-02 14:51:30.26 spid9s Starting execution of PRE_MSDB.SQL
2020-08-02 14:51:30.26 spid9s ———————————-
2020-08-02 14:51:30.42 spid9s Setting database option COMPATIBILITY_LEVEL to 100 for database ‘msdb’.
2020-08-02 14:51:30.46 spid9s —————————————–
2020-08-02 14:51:30.46 spid9s Starting execution of PRE_SQLAGENT100.SQL
2020-08-02 14:51:30.46 spid9s —————————————–
.
.
.
2020-08-02 14:51:35.25 spid9s Creating procedure sp_purge_jobhistory…
2020-08-02 14:51:35.29 Logon Error: 18401, Severity: 14, State: 1.
2020-08-02 14:51:35.29 Logon Login failed for user ‘JBSQL1\jvivek2k1’. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: ]
2020-08-02 14:51:35.29 spid9s
2020-08-02 14:51:35.29 spid9s Creating procedure sp_help_jobhistory…
2020-08-02 14:51:35.29 Logon Error: 18401, Severity: 14, State: 1.
2020-08-02 14:51:35.29 Logon Login failed for user ‘JBSQL1\jvivek2k1’. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: ]
.
.
.
2020-08-02 15:08:51.69 spid9s Error: 1105, Severity: 17, State: 2.
2020-08-02 15:08:51.69 spid9s Could not allocate space for object ‘dbo.Large Object Storage System object: 422212467294208’ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

2020-08-02 15:08:51.69 spid9s Error: 7399, Severity: 16, State: 1.
2020-08-02 15:08:51.69 spid9s The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
2020-08-02 15:08:51.69 spid9s Error: 7330, Severity: 16, State: 2.
2020-08-02 15:08:51.69 spid9s Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
2020-08-02 15:08:51.69 spid9s Error: 912, Severity: 21, State: 2.
2020-08-02 15:08:51.69 spid9s Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 7330, state 2, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2020-08-02 15:08:51.70 spid9s Error: 3417, Severity: 21, State: 3.
2020-08-02 15:08:51.70 spid9s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2020-08-02 15:08:51.73 spid9s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

-> The script upgrade mode fails due to insufficient space in Tempdb.

-> I have added Trace flag 902 temporarily which stops script upgrade mode for troubleshooting purpose.

-> I started the SQL Services with trace flag 902 and checked the reason why tempdb was full.

-> I was able to see that autogrowth for Tempdb was disabled and the size was just 8 MB as I left the default value during SQL Server install.

-> It seems like I disabled the autogrowth value for tempdb, but forgot to increase the tempdb file size.

-> I enabled autogrowth for Tempdb files.

-> Removed Trace flag 902 from SQL Services and restarted SQL Services.

-> It did started fine this time and could login to SQL Server without any issues.

-> I checked SQL server error log and there were no errors.

-> Since the CU 5 install crashed with “Could not start SQL Server” error. I decided to repair the SQL server instance, just to make sure there are no issues.

-> SQL Server repair completed fine.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.