Translate

Tuesday, July 20, 2010

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.

No comments:

Post a Comment

About Me

My photo
Greetings Friend! I am Linux scholar, trying to learn as much I can and share it with you. I am in mid of my Professional Career. Doing Good. :)

Followers