shrink database logs in MSSQL

• This article requires the use of Microsoft SQL Server Management Studio. If you do not have this installed, Microsoft SQL Server Management Studio Express is available for free from Microsoft and can be downloaded through these links:

Always back up the website before making any changes to the database

How I shrinked the log file for database:

      1 ) Backup the DB
      2) Set the Recovery Model to simple , Right-click on the DB that's causing the issue (db_name) > properties > Options > Under Recover Model , select Simple and save

Note: Recovery model will be set as Full , which is the suggested
The need for FULL recovery model is that the db can be restored to the point of failure.

    3) Right-click on the DB that's causing the issue
    Select Tasks->Shrink->Files
    Select 'Log' for File Type
    With 'Shrink Action'='release unused space', execute the operation or choose Reorganize pages before releasing unused space
   Shrink file to

Note: Also here I could see the file growth size for log was set as 7MB , I went ahead and raised it to 1024M
This can be done under Right click DB > Properties > Files > under 'Auto growth ' section for log file  , click browse which will open the setting page for autogrowth , there under Restricted File growth size set the desired value


Following steps can be used to keep log file small.

 Note:You have to take a manual backup of your database for disaster recovery.


Code Snippet

USE db_name;

GO

ALTER DATABASE  db_name SET RECOVERY SIMPLE;

GO

DBCC SHRINKFILE ( db_name_log, 10); --Here 10 indicates 10MB

GO

ALTER DATABASE  db_name SET RECOVERY FULL;

GO

NOTE: Don't leave your database Recovery in "SIMPLE" mode. This will cause some problems when u try to delete Site Collection from SharePoint Central Administration. Always keep it in "FULL" mode.