Shrinking the SharePoint Content Database (Part 1) by Gus Collazo

When you first setup your SharePoint site, the SQL Server database that is created for that site is about 10MB by default. As time passes though and you add and remove files to your site, you will notice that the database will grow very large. This can be an issue for your nightly backups and can hurt overall SharePoint performance. In this blog, we'll take a two-part look at shrinking the SharePoint content database. In this blog entry, we'll focus primarily on the traditional method of shrinking a SQL Server database. Sometimes though, it's not enough to simply shrink the existing contents of your SharePoint database. There are cases when you'll want to remove some of the binary data representing large documents and their multiple versions from your database. This is a little trickier and we'll save that for the next blog entry.

So let's get to it. If you manage only one SharePoint site, then there should be only one WSS_Content database to shrink. If however you manage several sites, you'll first need to determine which database to focus on. This can be done by going to the SharePoint 3.0 Central Administration page and clicking on the Application Management tab. Click the Site Collection List link in the SharePoint Site Management section. Click the URL on the left for the site you wish to focus on. The database for that site will be listed on the right.

Now run SQL Server Management Studio and connect to this content database via a query window. Run the command "sp_helpdb <db_name>" where <db_name> is your database name. Note the value in the "db_size" column of the top recordset and the values in the "size" column of the second recordset.

The steps below will focus on decreasing these sizes as close as possible to the initial size of the database. In most cases, this will be about 10-20MB. If a custom database was used for the content database for your SharePoint site, the smallest size you can shrink your database to will be the initial size that you set for that custom database.

First, you need to produce a full backup of the database. This can be done in the query window using this command, substituting your database name for "dbname".

BACKUP DATABASE dbname TO DISK = 'C:\Backups\dbname.bak' WITH INIT, STATS = 5

Next, run this script to prepare the log file of the database to be shrunk. Replace the 'dbname' with your database name. This code moves the active VLF of the log file to an earlier physical location and will allow the log file to shrink later. You can read more about the VLF here.

DECLARE @dbname varchar(100)
SET @dbname = 'todo'

exec ('Backup Log '+@dbname+' with Truncate_Only')
exec ('create table '+@dbname+'..Temp1 (Col1 int)')
exec ('insert into '+@dbname+'..Temp1 values (0)')
exec ('update '+@dbname+'..Temp1 set Col1 = 1')
exec ('update '+@dbname+'..Temp1 set Col1 = 2')
exec ('update '+@dbname+'..Temp1 set Col1 = 3')
exec ('update '+@dbname+'..Temp1 set Col1 = 4')
exec ('update '+@dbname+'..Temp1 set Col1 = 5')
exec ('update '+@dbname+'..Temp1 set Col1 = 6')
exec ('update '+@dbname+'..Temp1 set Col1 = 7')
exec ('update '+@dbname+'..Temp1 set Col1 = 8')
exec ('update '+@dbname+'..Temp1 set Col1 = 9')
exec ('update '+@dbname+'..Temp1 set Col1 = 10')
exec ('update '+@dbname+'..Temp1 set Col1 = 11')
exec ('update '+@dbname+'..Temp1 set Col1 = 12')
exec ('update '+@dbname+'..Temp1 set Col1 = 13')
exec ('update '+@dbname+'..Temp1 set Col1 = 14')
exec ('update '+@dbname+'..Temp1 set Col1 = 15')
exec ('update '+@dbname+'..Temp1 set Col1 = 16')
exec ('update '+@dbname+'..Temp1 set Col1 = 17')
exec ('update '+@dbname+'..Temp1 set Col1 = 18')
exec ('update '+@dbname+'..Temp1 set Col1 = 19')

Finally, run these commands to actually shrink the data and log files for your database. Note that these statements may run a very long time depending on your database.

DECLARE @dbname varchar(100)
SET @dbname = 'todo'

DBCC SHRINKDATABASE (@dbname, 25)
exec ('DROP TABLE '+@dbname+'..Temp1')
exec ('BACKUP LOG '+@dbname+' WITH TRUNCATE_ONLY')
exec ('BACKUP DATABASE '+@dbname+' TO DISK = ''C:\Backups\'+@dbname+'.bak'' with INIT, STATS = 5 ')

Now if you run the "sp_helpdb <db_name>" command that we ran earlier, you should find smaller sizes for your database files!

This will take care of database sizing issues for most SharePoint deployments. Backups should run much quicker now and overall SharePoint performance should improve. There are cases though where the steps above are not enough. In one deployment I found, the database had grown to over 0.5 TB (yikes!) due to a large number of very large files being uploaded to SharePoint. This might be so bad except that versioning was also enabled so small changes to very large files over time led to lots of wasted database space maintaining old copies of very large files. The next blog entry I'll make will focus on this case. Until then, post a comment and let me know how much database space you save!