We have several staging SharePoint environments running on MSFT Hyper-V servers, this gives our BA's the environment to build up PoC sites for clients. I was doing the usual maintenance round and found that space was becoming an issue on one of the staging environments.

So... time to truncate and shrink some SQL Log files I think, and I was correct! A couple of the .LDF files has swelled to in excess of 500MB. First of all, back up the database, fully... I wouldn't be too popular if I lost all that work! Next I ran the the code below on the database (highlight one row at a time and run it): 

   1: BACKUP LOG <Database Name> WITH TRUNCATE_ONLY 

   2: DBCC SHRINKDATABASE (<Database Name>)

After running the code, the LOG file went from >500MB to around 500KB!

Finally, I would suggest changing the autogrowth settings for the log file. To do this:

  1. Open MSFT SQL Server Manager
  2. Log into the SharePoint SQL server
  3. Right-click the database and select Properties
  4. In the Database Properties window, select Files from the left-hand pane
  5. In the Database Files box (on the right-hand side), click the "..." button in the Autogrowth column for the LOG file row (see the image below)

    image
  6. In the File Growth area, enter either the percentage or megabytes you want to limit the file growth to (I have set the one above to 2 percent)
  7. In the Maximum File Size area enter the max size you want from the file OR leave it Unrestricted (I have restricted the one above to 200MB)

Alternativly, if you don;t care about keeping your log files (if you have a sandbox or something?) you can change the Recovery Model of the database to simple... Follow steps 1, 2, 3 above and then select Options from the left-hand pane, in the Recovery Model drop-down list, select "Simple".

And that should be that!