This is one of those things that I recently learned the hard way.  Boy, I sure love it when that happens!  As I’ve said before, I’m no SQL guru.  I know just enough to do a whole lot of damage, but that’s beside the point.  Let’s get into what happened and how it was fixed.

Scenario

First of all, let me point out that this server was built a long time ago by someone who clearly didn’t know what he was doing.  In other words, I didn’t build this server (you’ll see why I wanted to clarify that next).

• This is a virtual server

  • 126 GB of allocated disk space

  • Between 0 and 10 MB available disk space (yep, you read it right!) – any idea why the apps hosted on this server kept crashing?

• SharePoint 2007 (WSS 3.0 only)

• SQL Server Embedded Edition (SSEE) for the following databases (the names have been adjusted):

  • SharePoint_AdminContent_GUID

  • SharePoint_Config_GUID

  • WSS_Content_ClientApplication1

  • WSS_Search_ServerName_GUID

• SQL Server 2008

  • aspnetdb (storage of FBA credentials for a client)

  • aspnetdb_ClientName (storage of FBA credentials for a client)

  • WSS_Content_PortNumber

  • WSS_Content_ClientApplication2

I’m a SharePoint nerd, not a SQL guy.  The first thing I started to do was investigate everything on the SharePoint side of things.  Of course, this was a huge waste of valuable sleeping time as the problem ended up being SQL-related.  I’d like to mention that I completely forgot about the existence of SQL Server Embedded Edition and of course never imagined that someone would install SharePoint in it…especially when SQL Server is already available on the same box!  Nevertheless, it was and it took me a day just to figure out how to get connected to SQL Server Embedded Edition so that I could see those databases and investigate this problem.  I wrote about how I got connected to SQL Server Embedded Edition in this previous post.

Once connected, I pulled out the ol’ right-click, properties action on each database and found that the database that I have highlighted above was showing a size of about 109 GB.  My reaction:  “uhhh, I must be reading this wrong.”  Nope.

I then looked at the properties of every single table and everything else in that database.  Nothing was even close to 109 GB in size.  After a few minutes of pondering, I remembered someone somewhere saying something about SQL logs.  Without any other ideas, I started to investigate and found that the log files for SQL Server Embedded Edition are on the file system at C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data.  In this directory I found that the log (.LDF) file for the WSS_Content_ClientApplication1 database was 109 GB in size!

At this point I was just happy to have figured out where all my disk space went and called it a night.  The next morning I tried everything I could to free up disk space.  After uninstalling everything I could without uninstalling SharePoint itself, I attempted to truncate the database log using the point-and-click method in SQL Server Management Studio.  It still kept failing due to a lack of disk space.  Out of time, out of luck, and after exhausting all feasible avenues, I decided to reach out for help (this is one of my lesser qualities…I could have saved three days of stress if I had reached out sooner).  In almost no time at all, one of my fellow instructors at Directions Training sent me an email that solved the problem.  It contained one of the most simplistic SQL scripts I’ve ever seen, yet this is what worked.  After running the script, the log was truncated and I now have a fully functioning SharePoint 2007 (WSS 3.0 only, remember) environment that’s hosting a couple of client training registration portals.  Whew!

USE WSS_Content_ClientApplication1
DBCC SHRINKFILE (WSS_Content_ClientApplication1_log,1)
BACKUP LOG WSS_Content_ClientApplication1 WITH TRUNCATE_ONLY
DBCC SHRINKFILE (WSS_Content_ClientApplication1_log,1)
GO

Scripts triumph over point-and-click yet again.  Learn your scripts, people!