MSSQL Loose all the days transactions

Question

I had a rather interesting problem I written an application using Dev XAF for a client and the client had a program which I removed called Macrium reflect doing I assume a mirror copy from 1 hard drive to another. The first issue is the Macruim is a free version the second issue is the primary68716-sql-logs-2.log drive is an SSD and the secondary drive is a normal hdisk. Anyway what happen on the Sunday night the pc was shut down for some reason and in the morning it was restart naturally but for some reason all the SQL transaction for the day didnt commit to the database. I suspect Macruim locked the MSSQL file or the MSSQL backup stopped it not sure. However the the strangest thing happened all apps appeared normal but non of the changes committed to the database until at 17h26 the server restart however all the days transactions where lost.

Does anyone have any idea where I can look to see what happened or what caused the problem

The Server environment is to me is a little wrong they running MSSQL std 2017 windows 10 home 64 bit which is an issue and the database is 140gig contains quite a lot of pictures.

I have attached the MSSQL logs files for the day in question

Answer 1

It is difficult to say what may have happened with your vague description, and which may not be accurate. You say that the transactions were committed, but how do you know that they were? Furthermore, I am not familiar with Macruim.

But I can think of two possibilities:

1. The transactions were in fact not committed, but Macruim clinged to an open transaction, and therefore everything was rolled back when the server was restarted.
2. Someone restored a backup and applied logs and for some reason stopped at 17:26.

On the other hand, Macruim did not "lock the MSSQL file" - that can't be done.

I make some other observations in the file which calls for attention (beside the ones you have already pointed out):

1. They are on SQL 2017 RTM. They should apply CU22 which is the latetest cumulative update.
2. Look at the message 11:16:45. That is bad for performance and everything else. Maybe SQL Server should have Lock Pages in Memory. Or you should set max server error, if there are other applications on the machine.
3. And 14:21:06 there is another warning sign - I/O should not take 15 seconds, not even on a spinning disk.

Answer 2

Hi,

I am not quite clear about your description(your environment) in the question. How do you determine that all the SQL transaction for the day didnt commit to the database.

In order to prevent data loss/downtime due to unexpected software or hardware failures, it is recommended that you use the HA and DR solutions for SQL Server .

Answer 3

What you describe cannot really happen, unless the files were physically restored or modified outside of SQL Server. I would start by asking if there was a restore or something happened to the hard drives.

Answer 4

Thank for the advice I also run a disk scan and found there are 3 bad sectors on the disk I suspect now we might have a failing disk on top of this issue and going to get there It dept to change the disk immediately

Oops! That's bad news. And this opens for a third explanation for the missing transactions: corruption.

You should absolutely run DBCC CHECKDB on the database.

If the disk is in bad shape, that could explain the message about disk access taking more than 15 seconds.