SharePoint 2010 - Drives are running out of free space.

Copyright from: blah.winsmarts.com

As this post on blogs.msdn.com details out, this is due to a health analyzer rule configured in SharePoint. While that blogpost does a great job explaining why this monitoring is necessary, how you can tweak it, it still becomes a nuisance on SharePoint virtual machines used for development.

It also becomes a nuisance on production environments because SharePoint databases are set to auto grow. In other words, as the database is being used, it only grows, and grows, and GROWS!

Seriously, how many of you have put in work to compact the database on a regular basis? Those of you who answered no, you’re sitting on  a time bomb. Shame on you!

Anyway, compacting databases isn’t something you do blindly. This is a science on it’s own, and how and when you compact the database depends on the usage and purpose of the database. Usually this is a consideration for production environments.

In this blogpost, I am not going to go in depth in all that. This blog post is about development virtual machines that are starved on disk space, and this ever growing database issue, makes it worse. So here is how you can give yourself the gift of extra disk space on your dev. vm.

Step #1: Do the usual stuff first, delete files you don’t need. Running the Disk Cleanup Tool is a good start.

Step #2: Go to central admin, and decrease the # of days to store the log files.

• In central admin, go to monitoring
• Go to reporting\Configure diagnostic logging.
• In the Trace Log section, in the Number of days to store log files box, type in a smaller number – I usually am happy with the last 20 mins of logs or so :)
• Go to 14\Logs and delete all the files in there. Don’t delete the “LOGS” directory.

Step #3: All those managed services that the farm wizard created – well they use disk space, in the form of databases generally. Not just disk space, they also take CPU cycles. Delete the services you are not using.

Step #4: Compact databases, login to SQL Server management studio with a user that has sysadmin rights, and run the following script -

  1: DECLARE @DB VARCHAR(255)

  2: DECLARE C CURSOR FOR

  3: SELECT NAME FROM SYS.DATABASES WHERE IS_READ_ONLY=0 AND STATE=0

  4:   AND NAME NOT IN ('master','model','tempdb','msdb')

  5: OPEN C

  6: FETCH C INTO @DB

  7: WHILE @@FETCH_STATUS=0

  8: BEGIN

  9:   EXEC SP_DBOPTION @DB,'trunc. log on chkpt.','true' 

 10:   DBCC SHRINKDATABASE (@DB)

 11:   FETCH NEXT FROM C INTO @DB

 12: END

 13: CLOSE C

 14: DEALLOCATE C

Enjoy all that extra disk space.