SQl server is unable to start in a timely fashion. The error log says that it is unable to find the locations to some files on "d:" drive. The funny part is that I only have a single partition disk "C:"

Question

2021-12-15 00:53:23.22 Server Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Home Single Language 10.0 <X64> (Build 22000: )

2021-12-15 00:53:23.22 Server UTC adjustment: 5:30
2021-12-15 00:53:23.22 Server (c) Microsoft Corporation.
2021-12-15 00:53:23.23 Server All rights reserved.
2021-12-15 00:53:23.23 Server Server process ID is 16220.
2021-12-15 00:53:23.23 Server System Manufacturer: 'LENOVO', System Model: '82LM'.
2021-12-15 00:53:23.23 Server Authentication mode is WINDOWS-ONLY.
2021-12-15 00:53:23.23 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2021-12-15 00:53:23.23 Server The service account is 'NT Service\MSSQLSERVER'. This is an informational message; no user action is required.
2021-12-15 00:53:23.23 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-T 1800
2021-12-15 00:53:23.23 Server Command Line Startup Parameters:
-s "MSSQLSERVER"
2021-12-15 00:53:23.23 Server SQL Server detected 1 sockets with 8 cores per socket and 16 logical processors per socket, 16 total logical processors; using 16 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2021-12-15 00:53:23.23 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2021-12-15 00:53:23.23 Server Detected 7514 MB of RAM. This is an informational message; no user action is required.
2021-12-15 00:53:23.23 Server Using conventional memory in the memory manager.
2021-12-15 00:53:23.23 Server Page exclusion bitmap is enabled.
2021-12-15 00:53:23.36 Server Buffer Pool: Allocating 1048576 bytes for 905853 hashPages.
2021-12-15 00:53:23.39 Server Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
2021-12-15 00:53:23.41 Server Buffer pool extension is already disabled. No action is necessary.
2021-12-15 00:53:23.44 Server Query Store settings initialized with enabled = 1,
2021-12-15 00:53:23.45 Server The maximum number of dedicated administrator connections for this instance is '1'
2021-12-15 00:53:23.45 Server This instance of SQL Server last reported using a process ID of 5328 at 15-12-2021 00:51:33 (local) 14-12-2021 19:21:33 (UTC). This is an informational message only; no user action is required.
2021-12-15 00:53:23.45 Server Node configuration: node 0: CPU mask: 0x000000000000ffff:0 Active CPU mask: 0x000000000000ffff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2021-12-15 00:53:23.45 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2021-12-15 00:53:23.45 Server Lock partitioning is enabled. This is an informational message only. No user action is required.
2021-12-15 00:53:23.45 Server In-Memory OLTP initialized on lowend machine.
2021-12-15 00:53:23.46 Server [INFO] Created Extended Events session 'hkenginexesession'

2021-12-15 00:53:23.46 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2021-12-15 00:53:23.46 Server Total Log Writer threads: 2. This is an informational message; no user action is required.
2021-12-15 00:53:23.47 Server clwb is selected for pmem flush operation.
2021-12-15 00:53:23.47 Server Software Usage Metrics is disabled.
2021-12-15 00:53:23.47 spid9s Starting up database 'master'.
2021-12-15 00:53:23.48 spid9s 3 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2021-12-15 00:53:23.48 spid9s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
2021-12-15 00:53:23.48 spid9s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2021-12-15 00:53:23.53 spid9s Resource governor reconfiguration succeeded.
2021-12-15 00:53:23.53 spid9s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2021-12-15 00:53:23.53 spid9s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2021-12-15 00:53:23.54 spid9s SQL Trace ID 1 was started by login "sa".
2021-12-15 00:53:23.54 spid9s Server name is 'ANIKET'. This is an informational message only. No user action is required.
2021-12-15 00:53:23.55 spid21s Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2021-12-15 00:53:23.55 spid21s Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2021-12-15 00:53:23.55 spid9s Starting up database 'msdb'.
2021-12-15 00:53:23.55 spid14s Starting up database 'mssqlsystemresource'.
2021-12-15 00:53:23.55 spid9s Error: 17204, Severity: 16, State: 1.
2021-12-15 00:53:23.55 spid9s FCB::Open failed: Could not open file d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
2021-12-15 00:53:23.55 spid9s Error: 5120, Severity: 16, State: 101.
2021-12-15 00:53:23.55 spid9s Unable to open the physical file "d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2021-12-15 00:53:23.55 spid14s The resource database build version is 15.00.2000. This is an informational message only. No user action is required.
2021-12-15 00:53:23.55 spid9s Error: 17207, Severity: 16, State: 1.
2021-12-15 00:53:23.55 spid9s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'd:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.
2021-12-15 00:53:23.55 spid9s File activation failure. The physical file name "d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf" may be incorrect.
2021-12-15 00:53:23.57 spid14s Starting up database 'model'.
2021-12-15 00:53:23.57 spid14s Error: 17204, Severity: 16, State: 1.
2021-12-15 00:53:23.57 spid14s FCB::Open failed: Could not open file d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\model.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
2021-12-15 00:53:23.57 spid14s Error: 5120, Severity: 16, State: 101.
2021-12-15 00:53:23.57 spid14s Unable to open the physical file "d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\model.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2021-12-15 00:53:23.57 spid14s Error: 17207, Severity: 16, State: 1.
2021-12-15 00:53:23.57 spid14s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'd:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
2021-12-15 00:53:23.57 spid14s File activation failure. The physical file name "d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\modellog.ldf" may be incorrect.
2021-12-15 00:53:23.57 spid14s Error: 945, Severity: 14, State: 2.
2021-12-15 00:53:23.57 spid14s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2021-12-15 00:53:23.57 spid14s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Answer 1

Hi @AniketL ,

It seems the file location of databases do not match the file location information that existed in master database. Please check the real data and log files location of mode and MSDB. Then using below command to change the file location information in master database.

1.Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

o For the default (MSSQLSERVER) instance, run the following command:

NET START MSSQLSERVER /f /T3608  

o For a named instance, run the following command:

NET START MSSQL$instancename /f /T3608  

2.For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.

ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'Real file_path\os_file_name' )    

3.Exit the sqlcmd utility or SQL Server Management Studio.
4.Stop the instance of SQL Server. For example, run NET STOP MSSQLSERVER.
5.Start the instance of SQL Server. For example, run NET START MSSQLSERVER.
6.Verify the file change by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc    
FROM sys.master_files    
WHERE database_id = DB_ID(N'<database_name>');    

Refer to MS document Move System Databases to get more.

If it is not work, please let us know.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

First, do you really need TF1800? From my understanding of what it does, I can't see that it has any relation to your problem.

On the other hand, I can see that 3608 helps. The documentation says:

Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require TempDB are initiated, then model is recovered and TempDB is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Move System Databases and Move User Databases.

>

Note: Do not use during normal operation.

So that last line answers your question. No, it is not normal.

If you start with 3608, are you able to access your databases? Are you able to access model or msdb? Can you create temp tables?

If you look in sys.master_files, what locations do you see for the system databases?

Possibly, you could use ALTER DATABASE MODIFY FILE to change the locations of msdb and model, but I suspect that the installation is still incomplete because of that error about the database startup handle. At that point, Setup wants to run post-install scripts. So it is likely that you would still get an installation which is at best is working so-so.

Personally, I would be inclined to uninstall and try again. How did you run Setup? Myself, I prefer to download the ISO and work from there.

Answer 3

Hi @AniketL ,

> But it needs to have the startup parameters -T1800 and -T3608 to do so. Is this normal?

No, it is not normal.

What is the error message that you got when you did not add startup parameters -T1800 and -T3608 to start SQL server service? Please share the related error message for us.

Please use the repair option to repair the SQL server installation. Control Panel\Programs\Programs and Features.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 4

It seems to have gone from bad to worse. The errorlog just stops at some point during startup of SQL Server, and there is no error message.

The screen dump of the event log tells nothing. The warning from DistributedCOM is just a warning, and it seems to be recurring. The errors there may be possible telling us something, although they are not from SQL Server. Then again, one is from .NET Runtime, and the last thing that happens in the log is that the CLR is started.

Also, I note that the timestamps in your screenshot does not match the timestamps in the errorlog file. You may to go back in time to find any errors.

I will have to admit that I see little hope that this will sort out.

I'm kind of curios to know what happens if you start with 3608, but without 1800. Not that this is a resolution, since 3608 is unwanted.

Answer 5

The server is now able to start even without the -T3608 trace flag, but it needs -T1800 to be compulsorily there.

That seems to be good news. This documentation says about this trace flag:

Enables SQL Server optimization when disks of different sector sizes are used for primary and secondary replica log files, in SQL Server Always On and Log Shipping environments. This trace flag is only required to be enabled on SQL Server instances with transaction log file residing on disk with sector size of 512 bytes. It is not required to be enabled on disk with 4k sector sizes. For more information, see this Microsoft Support article.

Since you don't have an availability group, I couldn't see how this could apply, but maybe the documentation is not wholly accurate. After all, it talks about sector sizes, and you found this message:

There have been 256 misaligned log IOs which required falling back to synchronous IO. The current IO is on file C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf.

So obviously, there is something with your disk setup that causes the problems for SQL Server, and TF 1800 resolves the problem.

Given the description of the trace flag, I cannot see anything wrong with having this flag on permanently. It's 3608 that is dubious, and which is only intended to make it possible to resolve urgent situations.

By the way, out of curiousity, where did you pick up that TF 1800 would be useful? I can't see that neither I nor Cathy suggested it in this thread.