Last updated on September 28th, 2022 at 09:32 am
When your SQL Server database is left to “auto-grow” without a maximum limit, you will run out of hard drive space. Believe me, I learned the hard way!
In this blog post, I will talk about the two ways you can keep your transaction log file small; either backup your database when full recovery model is in use, or set your recovery model to simple.
When most people (including myself) talk about a database they are talking about it as a whole item. A SQL Server database, however, is made up of two distinct items: the database (#1) itself and the transaction log (#2).
How can you prevent SQL Server from running out of disk space because of the transaction logs?
There are a couple of ways to prevent this from happening. You can:
· Backup your database
AND/OR
· Set your database recovery model to simple
Which of these options should you use? The answer isn’t that easy because there are a lot of variables to consider. Before deciding ask yourself, “Do I need to restore the database right up to the failure point or is the last full backup okay?” As well, “How often is the database backed-up, if at all?” These questions should help you determine the best option.
Ultimately, it will depend on the purpose of the database and whether or not you need it to be recovered from the moment prior to a failure. These two options are NOT mutually exclusive, so you can do both and, depending on your environment, you should do both!
For more detailed reading, see the SQL Server online documentation topic, Back Up and Restore of SQL Server Databases.
Database Checkpoints
If you haven’t done a lot of SQL Server work then the term, “database checkpoint,” might sound unfamiliar. This is one of those topics that is a bit hard to explain, but in a nutshell, SQL Server caches the database in memory (RAM) and at database checkpoints it writes the cached data from RAM to disk. For more details, please read the Database Checkpoints (SQL Server) topic on Microsoft Docs.
It is only after the truncation of the logs, will the database logs release the space so that it can be reused. The truncation of the logs happens at different points based on what recovery model is used.
Quoting from the SQL Server documentation, The Transaction Log (SQL Server):
Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space allocated to physical log files.
Database in Full Recovery Model
When in full recovery model, the default for SQL Server databases, you must back the database and log file up. Otherwise the transaction log file will grow until you run out of hard drive space.
When you backup your SQL Server database (both the database and log file) it also truncates the transaction logs. Thereby releasing the space within the transaction log file. This is done so that the space within the transaction log can be re-used. Only items with a database checkpoint will be truncated during the backup. All remaining items will be kept within the transaction log file until the next backup. Therefore if you don’t backup your database and transaction log, this will cause your database transaction logs to become huge.
Database in Simple Recovery Model
When you use a simple recovery model, the database transaction log is truncated after each database checkpoint. This means that the database transaction log file is generally small as there is virtually nothing in it.
What is the downside of this method? You can’t restore the database from the point of failure. Do you really care if it’s for SSRS or ConfigMgr? Most likely not. However, if the database is for an online ordering system, such as Amazon, then absolutely! I want every transaction recovered until the system failed. You will not get that when the database recovery model is set to simple.
SQL Server Reporting Services (SSRS) Database
In a previous blog post (What Is the Problem with My SSRS Server?). I discovered that my SQL Server Reporting Services (SSRS) database was using a ridiculous amount of disk space. I was not backing it up nor using the simple recovery model.
In my opinion you don’t need to recover your SSRS database to the failure point. So a simple recovery model will prevent the SSRS logs from growing out of control.
Do you still need to backup your SSRS database? Yes, but I would also recommend that you keep a copy of all of your RDL files. I realize that you can extract the RDLs from the SSRS database (How Do You Backup a Single ConfigMgr Report? or How Do You Backup All of Your Custom ConfigMgr Reports?). But you still need a central repository for RDLs. It will make your life easier if you have one!
If you have any questions, please feel free to con
tact me @GarthMJ.