October 30, 2019 at 7:20 pm
After a reboot of the computer, we see in the SQL Error Log problems to connect to one of the database STORESQL. Our software using this database cannot connect obviously. But after a simple restart of the SQL engine, the connection to the database is possible. Do you have an idea why? The logs are provided below
This is for a machine: Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows 8.1 Pro 6.3 <X64> (Build 9600: )
Logs after reboot concerning database STORESQL:
2019-10-30 05:08:00.29 spid15s Error: 17204, Severity: 16, State: 1.
2019-10-30 05:08:00.29 spid15s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\STORESQL_log.ldf for file number 2. OS error: 32(The process cannot access the file because it is being used by another process.).
2019-10-30 05:08:00.34 spid15s Error: 5120, Severity: 16, State: 101.
2019-10-30 05:08:00.34 spid15s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\STORESQL_log.ldf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2019-10-30 05:08:00.43 spid15s Error: 5105, Severity: 16, State: 1.
2019-10-30 05:08:00.43 spid15s A file activation error occurred. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\STORESQL_log.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
2019-10-30 05:08:00.43 spid15s File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\STORESQL_log.ldf" may be incorrect.
2019-10-30 05:08:00.43 spid15s The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Log after a restart of SQL server engine
2019-10-30 06:21:07.90 spid15s Starting up database 'STORESQL'
2019-10-30 06:21:10.35 spid15s CHECKDB for database 'STORESQL' finished without errors on 2019-10-29 23:56:04.010 (local time). This is an informational message only; no user action is required.
October 30, 2019 at 9:08 pm
This happens when SQL comes online before underlying disk drives become online and made available to be used by OS, hence whatever files SQL is looking for are not available yet. By the time you recycled SQL services, the disks were online, hence SQL was happy and started without any issues.
October 31, 2019 at 1:56 pm
Adding to above point , as it is C drive check if Antivirus or any other tools holds the file for SQL not to be able to access.
Try SQL service to set as "delay start" as alternative option, but not recommended.
Regards
Durai Nagarajan
October 31, 2019 at 7:42 pm
I have seen this happen to machines that have DHCP enabled. The SQL Engine comes up before the DHCP address is resolved. If this is the case, you should be able to see in the errorlog messages for listening on 127.0.0.1 or ::1, but no messages about listening on other IP addresses.
October 31, 2019 at 9:01 pm
Hi Sreekanth B,
Thank you for your answer. Since there is only one C: drive in this machine and the OS, SQL Server and Databases all reside on this drive, I do not see how this would be a case of an offline drive, Maybe my knowledge on these processes is not deep enough?
Regards,
Stephane Thinel
October 31, 2019 at 9:05 pm
HI durai nagarajan,
Indeed I do not know if exclusions on SQL Server data files are set for the Antivirus, I will check that. Thanks for the pointer
The automatic (delay) was already tried to no prevail. some kind of lock is still found on DB STORESQL and only a restart of the service deals with it.
Regards,
Stephane Thinel
October 31, 2019 at 9:14 pm
HI crow1969
Thanks for the InputThis machine has a fixed IP address. From what I see in the logs is appears ok
2019-10-30 06:21:08.03 spid11s Server is listening on [ 'any' <ipv6> 56333].
2019-10-30 06:21:08.03 spid11s Server is listening on [ 'any' <ipv4> 56333].
2019-10-30 06:21:08.04 spid11s Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2019-10-30 06:21:08.04 spid11s Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2019-10-30 06:21:08.04 Server Server is listening on [ ::1 <ipv6> 49983].
2019-10-30 06:21:08.04 Server Server is listening on [ 127.0.0.1 <ipv4> 49983].
Regards,
Stephane Thinel
October 31, 2019 at 9:42 pm
According to the log file you provided - some other process has locked the .ldf file and is preventing SQL Server from bringing that database online. When SQL Server cannot access one (or more) of the files for a database - that database will not be brought online and will go into suspect mode.
The reason your application cannot login is most likely because it has the default database for its login assigned to this database - and since that database is not available the application cannot login. Even if the application could login it would not work because the database it needs isn't available.
SQL Server does not retry when this happens - so once the process that has the file locked is done, a SQL Server restart is now able to bring that database online because it can access all files.
What you need to do is figure out what process is locking the file - and that could be your anti-virus or it could be some startup process that is scanning the file system - possibly an inventory type scan. Once you have figure out what process is locking the file - then you can modify that process to exclude SQL Server files and/or SQL Server folders.
Using a delayed startup of SQL Server may work - but if the process locking the files takes longer than the delay you will still have the same problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 4, 2019 at 7:15 pm
The path to the ldf file caught my eye:
C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\STORESQL_log.ldf
This looks like a SQL Server 2014 Express path. Do you still have SQL Server 2014 Express installed on your PC and could something be causing it to access this file on startup? Just taking wild guess with this but I didn't see anyone else mention it.
November 4, 2019 at 7:24 pm
Hi Jeffrey Williams 3188,
Thank you for your answer. I will approach this problem considering a lock on the database coming from a security programs just after the reboot. I am building my tools suitcase to analyze this.
Regards,
Stephane
November 4, 2019 at 7:25 pm
HI TUellner,
This was indeed an SQL 2014 express that was upgraded. This is why the DATA path stayed the same.
Regards,
Stephane
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply