Monday, April 25, 2011

How to Compact or Shrink a Windows SharePoint Services 3.0 Database

After a lot of searching on this particular topic there are a couple of things to note first:
  • Windows SharePoint Services (WSS)) 3.0 uses the Windows Internal Database. It installs on a Windows machine as SQL Server (folder name) but doesn't appear in Add/ Remove programs. 
  • The Windows Internal Database can be accessed using Microsoft SQL Server Management Studio Express 2005. Download it here.
  • It's usually not the database that grows too large (WSS_Content.mdf or any .mdf) it's usually one of the log files (like SharePoint_AdminContent_xxxxxx_log.ldf). Mine grew to 7GB.
  • In order to fix this problem we want to shrink or truncate the SQL Server Transaction Log and we can do it without using any separate queries or commands like DBCC SHRINKFILE.

In order to shrink the SharePoint Services database (aka Windows Internal Database) log file we need to do a few things. Here are the steps that worked for me:
  1. Install SQL Server Management Studio Express 2005 on the machine with SharePoint Services 3.0.
  2. Open Management Studio
  3. Connect to the Windows Internal Database by using this as your server name: \\.\pipe\mssql$microsoft##ssee\sql\query 
  4. Windows Authentication is fine. Click connect.
  5. Expand the Databases tree and you should see several SharePoint and WSS_Content databases.
  6. It's recommended you backup all your databases before continuing on. For more information go here.
  7. Right click on the database you want to shrink the log file of and go to Properties > Options.
  8. Change the recovery mode to Simple and Click OK to apply the changes. (This is the only way SQL Server will be able to dramatically shrink the SQL Server Log files)
  9. Right click on the database again and go to Tasks > Shrink > Files
  10. For file type select Log and click OK to begin shrinking the log file.
  11. Repeat this step for whatever log files you need to shrink.
  12. To compact the actual database instead of the log files, keep the file type (step #10) to data.
  13. Be sure to reset the Recovery Mode back to Full when you're done.
That's all there is too it. You've now accessed the Windows Internal Database for Windows SharePoint Services 3.0 and shrunk both the databases and log files. I performed this task on my Windows Home Server which was running out of room thanks to a few log files that totaled 8GB. 

11 comments:

Anonymous said...

Brilliant, thanks for that. I am attempting to migrate Sharepoint 3.0 to Sharepoint 2010 and failed the pre-upgrade check with a database size of 34 gb. By using your advice my database is now 1.4gb.

Chris Kenst said...

Glad it helped.

Anonymous said...

Thank you! This helped me get passed a drive-full problem.

Anonymous said...

You shouldn't shrink the database file - that will lead to fragmentation.

dotNetFollower said...

Hello!
Here is a way to shrink sharepoint database transaction log for MS SQL Server 2008.
Thanks!

Train Geek said...

This was super helpful - clear and correct - thanks so much.

Doug said...

Helped me out too. Thanks for taking the time to write this up. We decided to leave the recovery as simple for our config log DB however, rather than setting it back to full.

Sonny said...

Very very helpful thank you!

Guest said...

Thanks. I've been looking for an easy solution.

Marlon Anjos said...

From 9GB to 540KB. TKS A LOT

Lino said...

Thanks!! Easy to follow, Thanks for sharing it.