Trimming the SQL log file

If your Microsoft SQL database transaction log file is big in size and you want to truncate it then use the following methods :

Step 1:

Run This command in Query Window

backup log [DATABASE NAME] with truncate_only

(This will makes the oldest parts of the log file redundant, the size doesnt change at this point though)


Step 2:


dbcc shrinkfile([LOGICAL NAME OF LOG FILE IN DATABASE],X)

(X is the size in megabytes that you wish to shrink the size of the log file to)

EXAMPLE:
Assume you had a database called "xyz" .

You must have to know the exact location of your .MDF and .LDF files.


backup log XYZ with truncate_only

dbcc shrinkfile(XYZ_log,20)

This will create your .LDF file around 20MB.

I have check it and it works for me.