SharePoint Connect to DB

I am using the lite sharepoint version 2008 and the trickiest thing for me was figuring out how to connect to the database.  Figured out that Windows Sharepoint Services (WSS) 3.0 ship with SQL Server 2005 Embedded Edition. Now called the Windows Internal Database.

Steps for connecting to the internal SSEE database:

  • Open us Microsoft SQL Server Management Studio 2008
  • Server Name:   \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
  • Use Windows Authentication

 

More information can be found here.

Microsoft SQL Server Express & WSS_Content

I had setup a SharePoint site about two years ago.  It been so easy to manage.  Every once in a while I will need to fix a thing or two.  That’s been about it, until now!  I should mention that managing this sharepoint server is not really part of my job and thus I end up putting very little time against managing it on a regular basis.  For my particular installation, I had WSS 3.0 and SSEE installed with all the defaults.

The only reason I looked at it this time was another web server on the box stopped working.  Come to find out, I was running out of hard drive space on that box.  Looking a little bit closer and became quickly aware that the issues was in the WSS_Content_log.  I am not as familiar with SQL server as I am Oracle, Postgres or MySQL.  The issue was obvious.  It wasn’t until doing some quick google searches that I came to realize that SQL was storing every transaction in this file so that you could go back to any point in time if there were db issues.

First things first, I need to connect to the db using SQL Server Management Studio.  It was strange because I could easily connect to <machine>\MICROSOFT##SSEE but the SharePoint database didn’t exist there.  I was so confused and eventually I talked to a SharePoint expert in our company. He gave me the following to log into the SharePoint database instance:

1
\\.\pipe\MSSQL$MICROSOFT##SSEE\SQL\query

Truncate and Shrink

Next thing I needed to do was update figure out how to truncate and shrink the WSS_Content_log file since it has become out of control.

  • Open Microsoft SQL Server Management Studio. Expand Databases. Right click on the WSS_Content database, go to PropertiesOptions, and change Recovery model: to Simple. Click OK.
  • This will truncate the log file. We must do this step first before we can shrink the file.

1
BACKUP LOG WSS_Content WITH TRUNCATE_ONLY

  • Confirm the name of the log file that you will be shrinking

1
SELECT * FROM sys.sysfiles

  • Shrink the log file

1
DBCC SHRINKFILE(WSS_Content_log, 1)
1
DBCC SHRINKFILE(&lt;NAME OF LOG FILE&gt;, 1)

That took my 16gig log file back down to a reasonable size.  Remember that doing this process will mean that you cannot revert your database to any point in time.  You either need to do backups on your own.  In my case, doing regular backups of the database is sufficient and that is what I have outlined above.