0

For some time now I have had a problem with saving data in the database.

The problem is that after a sudden shutdown of the server, the data that was sent a minute earlier from the application was not saved in the database.

I suppose that the problem is the large database and the fact that all saved data goes to the buffer and the data will not have time to be written to the server disks.

The question is whether it is possible to turn off the buffer so that the data is immediately written to the disks. I know that this will slow down the application, but until we solve the problem with the server, this will allow us to continue working.

Thank you in advance for your answers.

Improve this question
5
  • You misunderstand of how SQl Server saves the data, it's not important if there was no checkpoint and the data was not written do mdf file, the problem is in your application that did not issue any commit within 1 minute. If it did so you'll be able to see your data after server restarts as all the committed transactions are re-done Commented Apr 9, 2024 at 13:11
  • The commit doesn't have to happen from the application in a specific interval, just before the instance is shut down. @sepupic Commented Apr 9, 2024 at 14:00
  • Does this apply ? learn.microsoft.com/en-us/sql/relational-databases/logs/… Commented Apr 9, 2024 at 14:01
  • "The question is whether it is possible to turn off the buffer so that the data is immediately written to the disks." - If you're using Disk Write Caching, then don't. Otherwise, SQL Server will open the files with write through and no hardware caching (whether or not Windows/hardware honors this is another question) Commented Apr 9, 2024 at 14:01
  • It is a desired behavior that uncommited transactions will be rolled back to preserve data integrity. Commented Apr 9, 2024 at 14:32

1 Answer 1

Reset to default
2

To summarize the comments, data will not be lost on shutdown unless:

  1. The application failed to commit the transaction that wrote the data.

  2. The application configured delayed durability.

or

  1. The storage system is using write caching despite being told by the OS not to.
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.