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.

 

 

 

Connect To HSQLDB Grails Database

This is how you can connect to use the DBVisualize to connect to grails using the HSQL database. I wanted to learn more about GORM and to understand how it was generating the tables.

Datasource.groovy

1
2
3
4
5
6
 development {
dataSource {
dbCreate = "create-drop" // one of 'create', 'create-drop','update'
url = "jdbc:hsqldb:file:devDB;shutdown=true"
}
}
  • Choose HSQLDB embedded as your Driver using the hsqldb.jar located under your Grails home directory ($GRAILS_HOME/lib)
  • Update the Database URL to include your grails app base directory (jdbc:hsqldb:file:<grails app base>/devDB). Window users don’t have to specify the drive (ie. “C:”).