Shrinking Databases

In most cases we do not recommend shrinking databases as this often just leads to more autogrow events and reduced performance when the database has to grow back up later. However, sometimes it makes sense to do it, particularly on databases that are huge but have lots of free space inside them. Typically, this can happen if you haven’t performed any cleaning in the confirmlog database previously and have just started doing it, or have been using DocumentStore/NodeStore for a year without enabling the DocumentStore/NodeStore cleanup tasks. In cases like this, you can consider shrinking those databases. Note that the shrink itself can cause performance problems in the database when it’s executed, so off-peak hours is the best time for doing it.

SQL Server LOB Compaction

SQL Server offers functionality to compact pages that are sparsely populated. Run the following statement to de-allocate unused pages:

use [survey_pXXXXXXXX]
go
DBCC SHRINKFILE (1, 1)
WITH (lob compaction=ON)

Note that we do not recommend shrinking unless you:

• Are certain the database isn’t going to grow back up again.
• Are in a situation where you don’t have much choice.