Shrinking the Database transaction log file in Microsoft SQL server 2008 and 2008 R2

To shrink the transaction log file we need to follow these simple steps below

1) It is good to take log file backup before we Shrink transaction log file, to back up the log file execute the below command

BACKUP LOG [SharePoint_Config] TO DISK=’D:\configLogBackup.bak’

2) You need to change the recovery model to simple

Change the DB model to simple (Right click on the SharePoint_Config and click on properties àOptionsàRecovery model: change it to SIMPLE

(OR)

ALTER DATABSE [<databasename>] SET RECOVERY SIMPLE

In the process of making the database recovery model to “Simple”, it truncates the transaction log file, as it is not used with the simple recovery model.

3) Shrink the SharePoint_Config_Log DB

DBCC SHRINKFILE(‘SharePoint_Config_Log’)

(OR)

If you want to shrink it to certain MB then you can use command like below

DBCC SHRINKFILE(‘SharePoint_Config_Log’,50)

4) Revert back recovery model to FULL

Change the DB recovery model to full (Right click on the SharePoint_Config DB and click on properties àOptionsà Recovery Model: Change it to FULL

(OR)

ALTER DATABSE [<databasename>] SET RECOVERY FULL