Shrinking WSS (Sharepoint) SQL Server Log Files

Yesterday, while migrating a source repository from StarTeam to TFS, I received the following error:

"TF30042: The database is full. Contact your Team Foundation Server administrator."

Excuse you? Sure enough, my 100+ GB drive was full on the server. But I'd only migrated around 1000 items. Surely SQL wasn't consuming 100MB per file.

Turns out (yes, there was a lot of crud on the drive but...) the majority of the space, almost 40GB was being consumed by the Windows Sharepoint Services WSS Content data and log SQL Server files. Huh? I still need to investigate and understand why this portal, which is 100% unused, grew so large. Regardless, here's what I did to resolve:

1. Since this is not yet a production database, I flipped the SQL recovery option from Full to Simple for WSS Content and several other databases. Detail here and here.
   
2. Executed the maintenance plan for all the databases to get backups and clear out some of these files. That didn't help much. The Auto_Shrink == true didn't assist us in trimming the file sizes.
   
3. Executed the following SQL as described here to understand the space available.
USE WSS_Content<br />GO<br />SELECT name ,size/128.0 -<br />CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0<br />AS AvailableSpaceInMB<br />FROM sys.database_files;<br />GO
4. Executed the following SQL as described here to shrink the log file down to 1GB (1000MB)
   
DBCC SHRINKFILE (WSS_Content_LOG, 1000);<br />GO
5. Checked the data
6. Performed a second backup for safe keeping

In the end, I recovered more than 80% of the previously consumed hard drive. Check with your DBA and / or your Sharepoint admin should you encounter this issue in a production environment. I had the luxury of being a bit cavalier because I was in a near-empty, non-production environment.