I recently got a question about how to reclaim database space in SharePoint. The specific database mentioned was WSS_Logging although this could have been any of the SharePoint databases or any other SQL Server database for that matter. The database was growing really fast so the farm was configured to only keep a week’s worth of data. But because the database did not get any smaller, they decided to shrink it to reclaim space. To which I responded, “Are you running out of free space on your disk?”
Why I’m Against Regular Database Shrinking
I still see this on a regular basis – a database maintenance plan that takes daily backups and shrinks the database after an index maintenance task. The reasoning behind it is that the administrator simply wants to reclaim disk space every day. Very good intentions without understanding the effects of the action. That’s why I take my time to explain why it is a bad idea to regularly shrink databases.
In a previous blog post, I talked about how databases are like boxes being filled with content every day. As the box gets filled, the amount of free space decreases. But if you know that you will need to fill it up with more content, wouldn’t you go get a bigger box? Sadly, that’s not what we’re doing when we’re shrinking databases. We’re basically throwing away the box with free space to replace it with a smaller box with just enough space to keep all of the existing content. Imagine what you would do to accomplish this task – empty the box of its contents, throw away the box, replace it with a smaller box and put the contents back in. Wow, that’s a lot of work just to replace the box with a smaller one only to see someone else replace it back with a bigger one tomorrow (that someone else is SQL Server running autogrowth to accommodate new data being stored in the database.) But that’s similar to what’s happening to your databases every time you shrink them – you’re basically wasting resources every time you’re doing so.
But more than wasting resources just to reclaim free space is the performance impact this process has on the database. One of my favourite demos to show is how fragmentation in the database immediately happens as a side effect of shrinking. If you don’t believe me, watch this video recording before proceeding any further.
Shrinking a database increases fragmentation which causes performance problems.
Let’s move on. Imagine a database maintenance plan that has an index maintenance task immediately followed by a shrink database task. Ugh! You’ve just fragmented the indexes that have been defragmented on your database.
Let me get something straight. I’m not against shrinking databases especially in an emergency situation where you run the risk of causing downtime because of a disk space issue. Besides, there are other ways to resolve a disk space issue like adding a new file and moving data into it. I just don’t like shrinking the databases on a regular basis. So, do yourself a favor and check if your database maintenance plans have the Shrink Database Task included anywhere. If you can’t, ask your database administrator to make sure you don’t have it. Remove it if you find any. Ignore anything that any SharePoint expert tells you otherwise, even when the response comes from the TechNet/MSDN forum and marked as Answered. Your SharePoint users will thank you for it.
Dealing With Database Space Issues The Right Way
The proper way to deal with database space issues is to do capacity planning and monitoring. You need to understand what your space requirements will be within the next two years or more and plan accordingly. This should give you enough information to ask for budget allocation for that new storage infrastructure.
Now, you might ask, “my database server has been running for almost a year now and we didn’t capture usage statistics to determine disk capacity. What do we do?”
To which I would respond by asking, “Do you have database backups?” Well, I sure hope you do. In the next blog post, I’ll show you how you can use your database backups for more than just disaster recovery.
Please note: I reserve the right to delete comments that are offensive or off-topic.