Shrink a Transaction log file

I came across a very important issue with one of my customers they had a SharePoint site with a transaction log file that grew aggressively and filled up the partition that it was stored on. So they contacted me to assist them in shrinking the transaction log database after researching I didn’t find anyone that actually explains how this can be accomplished in a comprehensive manner without breaking the chain. So I decided to provide the steps myself and after that I provide an internal look in part to of this series.

Please Run the Following Script under the
SharePoint Configuration database in management studio

 

DECLARE
   @DBName VARCHAR(255)
SET
   @DBName =  'AdventureWorks2008R2'              ---Replace with Database Name
DECLARE
   @TransactionLogName VARCHAR(255)
SET
   @TransactionLogName =  'AdventureWorks2008R2_Log'  ---Replace with Transaction log file Name
-------------------STEPS TO GET TRANSACTION LOG FILE NAME-----------------------------
--RIGHT CLICK ON YOUR DATABASE-->Properties--> From the left panel choose files-->Under File Type search for log-->select Logical Name from that row
--------------------------------------------------------------------------------------
---------------------------------------------------------------
--My good friend/Mentor Rafid suggested another way to get the Log File Name (Rafid’s Blog: http://blogs.msdn.com/b/rafidl/)
-- select name from sys.database_files
-- where type_desc='log'
-- should also work
---------------------------------------------------------------------

DECLARE @SetMode NVARCHAR(MAX)
SET @SetMode  = 'ALTER DATABASE {DataBaseName} set recovery {RecoveryMode}'

DECLARE @ShrinkLog NVARCHAR(MAX)
SET @ShrinkLog = 'DBCC SHRINKFILE ({TransactionLogName},1)'

DECLARE @SCRIPT NVARCHAR(MAX)
SET @SCRIPT = REPLACE(@SetMode, '{DataBaseName}', @DBName)
SET @SCRIPT = REPLACE (@SCRIPT, '{RecoveryMode}','Simple')
EXECUTE (@SCRIPT)

SET @SCRIPT = REPLACE(@ShrinkLog, '{TransactionLogName}', @TransactionLogName)
EXECUTE (@SCRIPT)

SET @SCRIPT = REPLACE(@SetMode, '{DataBaseName}', @DBName)
SET @SCRIPT = REPLACE (@SCRIPT, '{RecoveryMode}','Full')
EXECUTE (@SCRIPT)

Choose Management from management studio and then right click on Maintenance Plans

  

Select Maintenance plan wizard

 

In the SQL Server Maintenance Plan Wizard click on Next

 

After adding a name and a description click on  Change

 

In the Job Schedule properties Make sure that the frequency is set to daily

Daily Frequency is set to 1 hour

And Duration is set to no end date

 

In the Select Maintenance Task select Back up Database (Transaction Log)

 

In the Select Maintenance Task Order Click on Next

 

In the Define Database Backup (Transaction Log) Tasks

Select the Database you want to shrink

  

In Select Reporting Action Click on Next

  

In Complete Wizard Click on Finish

The way this works is a set of prerequisites that are required to run the SHRINK Command

The problem is the structure of the Log file as illustrated in the link below

  
  
  

Source: http://msdn.microsoft.com/en-us/library/ms179355(SQL.105).aspx

Log files store a step by step on each transaction that occurs on your MDF or NDF files since the last time you have taken a full backup of the database

So every time you have taken a backup the transactions are deleted from the log file (they are not actually deleted they are just marked as dirty) this process is called truncating the log file

Transaction log file are divided into Virtual Log File if a virtual Log File does contain transactions marked as active then this VLF cannot be truncated and hence to overcome this issue we change the
recovery mode from Full to Simple this cases a checkpoint to run every min and hence continuously marks VLF as in active. The last part of the script is to make sure that the Transaction logs will be continuously marked as inactive through backing up and this is the schedule wizard that we followed