SQL Log File Cannot be Truncated or Shrunk Explained

Let's start by saying, this was a HUGE pain in the rear. So a client of mine is running SQL Server 2008 behind their SharePoint 2007 environment (this would also apply to SharePoint 2010), and one of the databases is 420MB's, but the Log File is 103GB's!!!

Wow, great catch, guess I will just set the recovery mode to Simple, and shrink the Log file right?
Wrong, its not that easy...

After about 2 days of research and sql queries, I ran into an MSDN article that explains where to look for the specific issue and resolve. Here are the steps I followed:


--Begin SQL commands--

--This command showed me the database was stuck in ‘REPLICATION’
SELECT name, log_reuse_wait_desc FROM sys.databases

--This command below removed the database from being stuck in ‘REPLICATION’ which should no unlock the database for maintenance/shrinking:
EXEC sp_removedbreplication SharedServices1_DB

--The following command shrinks the log file and sets the recovery mode back to Full.
USE SharedServices1_DB;

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO

-- Reset the database recovery model.
ALTER DATABASE SharedServices1_DB
SET RECOVERY FULL;
GO

The end result, we have a healthy database, that is only 420mb’s and the log file is 1MB!

GUID URL Decoder/Encoder is Great!

I came across a very useful tool which will take an ugly, un-encoded GUID and automatically add the fancy stuff aka put it into a useful format. See the example below:

Original GUID: %7BDHJTY8B%2DJGU5%2D8FE6%2DAY38E%2D5DTU58F9L5DB%7D
Decoded GUID: {DHJTY8B-JGU5-8FE6-AY38E-5DTU58F9L5DB}

Check out the tool here: http://meyerweb.com/eric/tools/dencoder/