The correct functioning of SQL servers is crucial to every well-run SharePoint Farm. When the disks on the SQL server become full, problems with SharePoint are sure to follow.
SQL servers are the backbone of any SharePoint farm. SharePoint uses SQL databases to store content that users produce and consume, and configurations that administrators configure.
The first instinct is to extend the disks of the SQL servers, but a lot of the time the issue is solvable without additional hardware costs. In this article, I will examine some of the most common issues that cause disks on an SQL server to become full.
The SQL Server Transaction Log Has Become Larger than the Database Content
This is perhaps the most common issue. The root cause of this error could be a number of different things. When this happens, it usually means that there is some underlying issue with the way the SQL Server is configured. The solution to this problem is twofold. The first thing to do is to check what is causing the large log files and then fix the issue. Some of the most common reasons are:
The SQL Server Transaction Logs Are Not Backed Up
This is the first thing that you need to look for. If the transactional logs are not backed, they keep growing with each operation performed on the database. The interval of backing up depends on many factors, and the most important is which Recovery Model is set and what the Recovery Point Objective (RPO) for the database is. To put it simply, what is the acceptable duration of time where you can roll back the database?
The SQL Server Database Recovery Model
It is important to select the right recovery model for your database. There are two options available; Full and Simple. The basic rule of thumb is to use Full for production databases and Simple for everything else. The Full recovery model allows you to restore the database to a specific point in time, while in Simple mode you can only restore it to the point of the last backup. If you choose to use the Full recovery model, it is advisable to back up logs often.
The Faulty SharePoint Configuration Database
Sometimes the SharePoint Configuration Database will start to produce a large number of logs. It is best to check in your Central Administration to see if there are any problems. Usually, you will find something there. Fix it and you are ready for the next step.
When you have solved the underlying problem with the generation of transaction logs, it is time to back up the database. This will create a backup of the database and in the process will reduce the size of the transaction logs. If the log file was 100% full, the log will still occupy the same space on the disk but will be mostly empty. If that is the case, you need to shrink the SQL server transaction log file as well.
While we are on the topic of SQL server transaction logs, it is always a good idea to keep the logs on separate disk drives from the content (mdf) files. This way, if the logs explode, they will have a separate disk to fill.
SharePoint Content Overflow
While the problems with the SQL server transaction logs are common, sometimes the SharePoint farm and its users are generating too much content for the SQL server. If that is the case, there is, unfortunately, no other solution than to extend your SQL server disk to accommodate data generation.
Using Central Administration, you can see how many Site Collections are in your Content Databases. It is always good to remember that Microsoft has set the max value at 10,000 (but only 2,500 regular non-personal). Also, be on the lookout for the size of content databases as the maximum supported value is 200GB.
There can be numerous issues (and sometimes just ordinary use cases) which results in full disks on your SQL server. It is important to keep track of these metrics, as it will potentially save you from downtime on your SharePoint farms. SysKit Insights is a tool that will proactively monitor the disks on your SQL servers and alert you when they are almost full. This way, you will have a chance to fix the issue before it becomes critical.