title: How to shrink log file in SQL Server after unknown period of not being backed up?
body: <p>I recently took over management of a database that has been in use for 2-3 years, and it had no transaction log maintenance plan in place. The DB file is 8 GB, but the transaction log file is a whopping 54 GB.  I started backing up the log file, and I need to reclaim that drive space.  I have compared my DB to other sites within my company that had proper maintenance plans built, and their transaction logs are roughly 4 GB, which is what I would expect.  This is the first time I've run into this problem.</p>
  
  <p>I performed a full backup of the DB, and set up an initial transaction log maintenance plan, but I need to shrink this *.ldf file, because it is so grossly out of proportion. I searched the Stack Overflow message boards in the hope of finding a similar situation.  Based on that research, I tried the DBCC SHRINKFILE but that did not yield the results I expected. I restored the DB to original (oversized log file in place) and tried the Full-Simple-Full recovery technique to truncate the log, but was still unable to reclaim the space. I even tried deleting the .ldf and going through the process of clearing the (Recovery Pending) status. I went back to the DBCC CHECKDB repair function, but after clearing the (Recovery Pending) status, I was unable to backup the transaction log at all. I started receiving a msg 42000 error 50000, which also referenced error 3013. In the end, I deleted the whole mess and restored it back to it's original state. I;ve tried to be as detailed as possible, and will be happy to clarify or expound if necessary.  As I said, this is the first time I've run into something like this, but I have always started my projects from the beginning.  This is the first time I've jumped into the middle of something that was built by someone else and broken when I got it.</p>
  
  <pre><code>ALTER DATABASE [DBName] SET EMERGENCY;
  
  GO
  
  ALTER DATABASE [DBName] set single_user
  
  GO
  
  DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
  
  GO 
  
  ALTER DATABASE [DBName] set multi_user 
  
  GO
  </code></pre>
  
  <p>My expected result is an uncorrupted transaction log that is appropriately sized for my database.  Let this be a cautionary tale and reminder to ask all the questions before accepting management of someone else's screw-up.</p>