Share via

SharePoint dev machine - keep your SQL databases sizes neat

  • Article

Ok, time to go back to the blog posting.

 

I do often create single-server SharePoint based environments purely for development purposes. In this environment we often face an issue of overgrowing databases logs.

To make things worse, now I use SSDs in Azure, which are rather limited in size.

So for DEVELOPER machine it would be fine to switch the recovery model to simple and thus the log should be truncated.

Prior to SQL 2012, there was a special option "truncate log on checkpoint", but as of SQL 2012 I cannot find this option anymore. See https://technet.microsoft.com/en-us/library/ms187310(v=sql.105).aspx

 

The blog here https://microtechpoint.com/index.php?route=blog/article&article_id=10 contains a good set of commands to keep your database in place (I really like the title "Shrinking Ginormous SharePoint Database Transaction Log Files" :)), but it only deals with one database.

 

So I have found an undocumented procedure sp_MSForEachDB (you could read more here https://www.c-sharpcorner.com/UploadFile/63f5c2/sp_msforeachtable-stored-procedure-in-sql-server-2012/ or here https://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx ) that can run a particular command on every database.

 

The final syntax I use comes like this (but, of course, you could use your commands and avoid shrinking or set autoshrink if it suits your environment).

EXEC sp_MSforeachdb 'USE [?]; print "?"; ALTER DATABASE [?] SET RECOVERY SIMPLE; CHECKPOINT; DBCC SHRINKDATABASE ([?],10); '

 

Disclaimer: I am not in any case saying that simple recovery model is ok for production.

Hope this post would be useful.

Comments

  • Anonymous
    July 14, 2016
    a few good links here to further reduce your dev machine sizehttps://blogs.msdn.microsoft.com/spses/2013/05/06/how-to-reduce-the-size-of-logging-database-by-purging-old-data-from-it/https://nickwilliams.net/2014/10/27/usage-and-health-database-is-taking-over/http://www.chrismcnulty.net/blog/Lists/Posts/Post.aspx?ID=83http://blog.binaryrepublik.com/2014/01/reduce-growing-disk-space-by-reducing.html#.V4hMEbh94yM