Skip to content
/

Keeping your SharePoint 2010 development databases small

With SharePoint 2010 the amount of databases on your SQL Server has grown quite a bit. By default most of these databases have their recovery model set to FULL. After some time you will discover you’re running out of space.

The problem

Most likely the problem lies with the transaction logs of your databases. With the recovery model set to FULL they will keep storing every transaction until you make a backup. Chances are you don’t configure a backup plan for you development environment as most development databases don’t need a backup as your sources will be stored in a source control system.

The (manual) solution

To solve this problem you can change the recovery model of each database by hand. For this you can open SMSS (SQL Server Management Studio), open the properties screen for a database and navigate to the options tab. There you will find the recovery model option.

Database Properties screen with the Recovery model set to 'Simple'
Database Properties screen with the Recovery model set to Simple.

Saving this change will empty your transaction log. But it will not shrink the physical file on disk. To shrink this file you can look at the Shrink task.

The context menu's to shrink the size of the log files
The context menu's to shrink the size of the log files.

The (automated) solution

Executing this step for every database manually is quite some work. So you want the easy solution.

The following TSQL script will change the recovery model for every database to Simple and shrinks the database.

USE [master]
GO

DECLARE @dbname SYSNAME
DECLARE @altercmd NVARCHAR(1000)
DECLARE @shrinkcmd NVARCHAR(1000)

DECLARE [dbcursor] CURSOR FOR SELECT [name] FROM sysdatabases

OPEN [dbcursor]
FETCH NEXT FROM [dbcursor] INTO @dbname

WHILE
  @@FETCH_STATUS = 0
BEGIN
  IF
    (SELECT DATABASEPROPERTYEX(@dbname, 'RECOVERY')) != 'SIMPLE'
    AND
    @dbname != 'tempdb'
  BEGIN
    SET @altercmd = 'ALTER DATABASE "' + @dbname + '" SET RECOVERY SIMPLE'
      EXEC (@altercmd)

      SET @shrinkcmd = 'DBCC SHRINKDATABASE ("' + @dbname + '")'
      EXEC (@shrinkcmd)

      PRINT @dbname
  END

  FETCH NEXT FROM [dbcursor] INTO @dbname
END

CLOSE [dbcursor]
DEALLOCATE [dbcursor]

5 Comments

  1. /

    I am trying to do something similar to shrink all my SharePoint content database log files in one sweep rather than shrinking them manually. I currently use the following to shrink my databases one-by-one but would like to shrink all logs in one fell sweep. Could your script be modified to do this for all content databases?

    use [Site1-Content-DB]

    BACKUP LOG [Site1-Content-DB] WITH TRUNCATE_ONLY

    DBCC SHRINKFILE ([Site1-Content-DB_Log],2)

  2. /

    Hello!
    Here is the almost same way to shrink sharepoint database transaction log.
    Thanks!

  3. /

    IMHO, one should restore the recovery model after shrinking. Also, shouldn't one stop the SharePoint timer Service before and restart after?

Trackbacks and Pingbacks

  1. Tweets die vermelden Keeping your SharePoint 2010 development databases small | Michaël's coding thoughts -- Topsy.com
  2. SharePoint 2010 Database « All About SharePoint

Leave a comment