SharePoint 2010 - Drives are running out of free space.

Posted on 2/29/2012 @ 2:11 AM in #SharePoint by | Feedback | 16669 views

You might have seen the following dreadful message -

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.

Sound off but keep it civil:

Older comments..


On 2/29/2012 3:58:19 AM Thomas Vochten said ..
Be sure not to shrink databases on a production system periodically. Databases grow, that's what they do. Shrinking databases generally causes more problems than it tries to solve, so be very aware of what you are doing. Of course, there are some situations in which you might consider it, like after you deleted or moved a very large site collection. In any case, never autoshrink a database - certainly not with SharePoint as this will lead to horrible fragmentation. Nice overview of some basic tasks at http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx


On 2/29/2012 6:58:35 AM Fred Morrison said ..
Double-check me on this one, but unless the Recovery Model of a SQL Server database is set to Simple, your script won't really shrink the SharePoint-related databases. On your development SharePoint VM's, use SSMS to first change the Recovery Model of each SharePoint-related database from Full to Simple. You can then use the Maintenance Plan wizard to define a Maintenance Plan that includes one activity, ShrinkDatabase, that is further narrowed to "All User Databases" that are "Online". Either manually run that SSMS maintenance plan on a regular basis or, if SQL Agent runs automatically, take advantage of that situation by setting up a schedule to run your maintenance plan on a regular basis, maybe once a week or less if you only use the VM occasionally. I do this for my SharePoint 2007 VM's and also with my SQL Server VM that is part of my CloudShare 4-VM SharePoint 2010 VM "set".


On 2/29/2012 2:04:44 PM Sahil Malik said ..
Thomas, Fred, totally agreed and thanks for your comments. Which is why I wrote,

"..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."

But on the other hand, just letting the DBs grow infinitely is also not a good idea :). Even if they only grow, grow and grow, they will fragment anyway.

But, I feel totally okay putting this compaction script on a dev. vm where space premium trumps issues such as long term fragmentation.