Shrink and defrag the ldf file

To shrink the logfile, go into Management Studio, right-click on the DB, Tasks, Shrink Datafile - the remainder should be obvious.
After that, you should not need to run a defrag anymore, but you can always do as long as MSSQL is not running.

Hi,
Big log files caused mainly by database recovery model, in your case I guess it configured "Full", for more information about database recovery models in SQL  Server you can refer to this link:
http://msdn.microsoft.com/en-us/library/ms189275.aspx

For shrinking you can follow instructions in this link:
http://help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs

I recommend to use test server, or take a full backup of your existing database before you try to do anything, this is my personal rule whenever I need to deal with such databases (size wise).

Good Luck

At 5% used I don't think it's a question of the recovery model...

ok now I know how to shrink.
I think it is better, to do this when no one works right ?

Can you also explain the way how to defrag ?

Defrag makes only sense when you have plenty free disk space. Run defrag when you have low load on your server. Cause while running it you will some performance degradation.

But first of all, you should evaluate what is the cause for the large log file. Cause when it is due to regular processing, then it's better to allocate a larger log file after the defrag run.

To defrag, stop the MSSQL instance and use any arbitrary defrag tool,  including the Windows integrated one.

Vitor is correct in parts. MSSQL does its best to prevent / handle fragmentation inside of the datafiles. Whether it uses info about the physical layout of the file I don't know. But to say file defragmentation does mess up the DB is plain wrong. True is that you should always start a defrag inside the DB with the DBMS' own means after a file defragmentation.

And again, after truncating the log file you won't need a defrag of it.

Defragmenting the "physical" file can help. It depends on what storage it is placed and how this defragmentation change the order on that device. Thus when it improves sequential reads, then it is okay to defragment the files.

E.g. consider a SQL Server running a VMWare (ESX) and also the files are in this image. Then defragmentation of the "physical" files (what Windows - the guest) sees, does not help. Cause ESX does its own handling.

But to say file defragmentation does mess up the DB is plain wrong.

I dare you to test a physical defragmentation and after that check the index defragmentation:

-- Check the index fragmentation 
SELECT  [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBName'), OBJECT_ID (N'TableName'), 1, NULL, 'LIMITED');

Select allOpen in new window

And it's better to check the index fragmentation before you run the physical defragmentation so you can compare the values.

True is that you should always start a defrag inside the DB with the DBMS' own means after a file defragmentation.

USE [db_name]
DBCC SHRINKFILE ( 2, <desired_size_of_file_in_mb> )

Sorry, can you explain the command ?
Where to put the database name ?

As I suspect you already know now, you have to already be in the context of the db you want to hrink.  That is, the db name does not go in the command itself, you must already be USEing that db:

USE [db_name_to_be_modified] --<-- set to the db name with the log file that needs shrunk / resized
DBCC SHRINKFILE ( 2, <desired_size_of_file_in_mb> )

and 2 means the ldf file ?

Yes.  You can use the logical file name instead if you prefer.  Usually it's something like db_name_log.  You can get the logical names from the first column of this result:

EXEC db_name.sys.sp_helpfile