Everything you ever wanted to know about MS SQL transaction logs
Every few month I hear people asking for help because their MS SQL Server is not working anymore.
Normally it is because the transaction logs of their database sever have grown so much (e.g. 500 GB) that the server ran out of disk space.
Sometimes this also happens at the customer and we lately also had an incident where it finally ruined the complete server.
Please everybody understand:
Installing a database it not enough!
You need a maintenance plan!
This is nothing special for our products it has to do with MS SQL and EVERYBODY working with databases needs to know about it, especially the people going out to the customer and deploying our tools.
But let's come back to transaction logs...
What is it?
https://www.mssqltips.com/sqlservertutorial/3302/what-is-the-transaction-log/
Why does it grow?
https://www.mssqltips.com/sqlservertutorial/3308/why-does-the-transaction-log-grow/
How do recovery models influence growth?
My final recommendation
You need a backup plan for your database to keep transaction logs from growing eternally (and finally crashing your system because you run out of disk space)
How to create a backup plan? See here https://www.sqlshack.com/multiple-methods-for-scheduling-a-sql-server-backup-automatically/
For test purposes only (NOT for productive use) you can also set the recovery model to Simple and forget about transaction logs.
We also have a nice book about administrating SQL Servers:
