SharePoint Development Environment Database Optimization script

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

So if you are like me you have more development VMs than SSD space and do everything you can to keep your VM space down. One of the biggest things you can do is set your database options to Simple recovery model. This means that you won’t be able to do point in time backups from your SQL log files, but most of us developers just use the full backup feature in our SP dev environments, so this is no big loss. The up side is that the log files stay really small and hence your hard drive needs less space.
The other thing you can do to create better performance is to auto update your statistics and set you file growth to a larger  consistent size. The statistics update will allow the queries to perform better and setting the file growth size to a fixed reasonable size will prevent you databases from auto growing on you every time you add an item to a list.
The below script can be run on a SharePoint development environment master database to set the above settings. Some of these settings are good for your production environment as well but you should consult your Perficient SharePoint infrastructure expert before making those changes.
This script is not intended for production SharePoint environments!

DECLARE @Sql AS NVARCHAR(2000) = '
Print''?''
ALTER DATABASE [?]
SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE [?]
SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [?]
MODIFY FILE
(NAME=[?],FILEGROWTH=60MB);
ALTER DATABASE [?]
SET RECOVERY SIMPLE
USE [?]
Print ''Shrinking data file ?''
DBCC SHRINKFILE (N''?'' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N''?_log'' , 0, TRUNCATEONLY)​
'
EXEC sys.sp_MSforeachdb
@command1 = @Sql