# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  How to reduce the log file(.ldf) file?

## Seenu

Hi all,

In my server .mdf is 370 MB and .ldf is 3739 MB. I took the full backup. I want to reduce the size of the .ldf file. Can anyone suggest me how to reduce the log file size.

Thanks in advance,

Seenu. S

----------


## Kristine Greenlee

This doesn&#39;t always work, but try running DBCC SHRINKDATABASE(dbname).  This function will not actually shrink the log file but mark it to shrink.  Then truncate log or backup log, depending on your log management strategy for the database.


------------
Seenu at 9/26/00 12:24:13 PM


Hi all,

In my server .mdf is 370 MB and .ldf is 3739 MB. I took the full backup. I want to reduce the size of the .ldf file. Can anyone suggest me how to reduce the log file size.

Thanks in advance,

Seenu. S

----------


## Seenu

Hi Kristine,
Thanks for answering. Also one question, How to truncate a transaction log after database backup, removing all log records for a SQL Server database and reinitializing the transaction log. ?



------------
Kristine Greenlee at 9/26/00 12:46:53 PM

This doesn&#39;t always work, but try running DBCC SHRINKDATABASE(dbname).  This function will not actually shrink the log file but mark it to shrink.  Then truncate log or backup log, depending on your log management strategy for the database.


------------
Seenu at 9/26/00 12:24:13 PM


Hi all,

In my server .mdf is 370 MB and .ldf is 3739 MB. I took the full backup. I want to reduce the size of the .ldf file. Can anyone suggest me how to reduce the log file size.

Thanks in advance,

Seenu. S

----------


## sethu

As Kristine said you can try with shrink database ,if does not
work then try with dbcc shrinkfile (more info below)

This is what happened in my case, one of my development database,
  the transaction log has grown upto 13 GB, because I forget to check the
  truncate log on checkpoint option while creating the database, when the 
 developers run a long script against this database, the transaction log has grown upto 13 gb, 
  Now after turning on the truncate log on checkpoint , I tried to shrink the database
  using the enterprise manger ,all task,shrink database,it runs successful
  but my both (,mdf & ldf) file size remains the same , no free space is
  released to operating system. so I put both my hand on my head and
  dreaming which honey is going to shrink the database for me and release
  by 13 gb of space.

  so I used dbcc shrinkfile to shrink the database. the following steps will
  explain how to shrink the log (.ldf) and data (.mdf) file.

 To Shrink the LOG file (.ldf)  follow the procedure.

step 1 :-run the following command

dbcc shrinkfile ( logical file name,target size,truncateonly)

note :- you can get the logical file name for the log by running the
           following select statement (select * from sysfiles)

step 2 :- if the database has the truncate log checkpoint turned off, then the
             transaction log file will be shrinked to the target size only when the
             transaction log is backed up. or if the truncate log on checkpoint is
             turned on the go to enterprise manager - all tasks - and click truncate
             log this will shrink the file upto the target size and will release all the 
             unused space to the os

             (note :- you cannot shrink the log file (.ldf) below certain size ,for more
              information , refer on line manual.)

To Shrink the DATA  file (.mdf)  follow the procedure.


Step 1 :- dbcc shrinkfile ( logical file name,target size)


Example (before shrinking the .mdf file)



1)	database name is ebdata  

               used			free

data --    180 mb                   1819.5 mb

log --       56 mb                       443    mb

(now we know that dba made mistake in the sizing
 of the database. )

to get the logical name of the (ebdata - data file , run the following
select statement (select * from sysfiles)

the following are the query results

fileid groupid size        maxsize     growth      status      perf        name                                                                                                                             filename                                                                                                                                                                                                                                                         
1      1       256000      -1          10          1081346     0           EBData_Data                                                                                                                      F:CVDatabaseEBData_Data.MDF                                                                                                                                                                                                                                   
2      0       64000       -1          10          1081410     0           EBData_Log                                                                                                                       F:CVDatabaseEBData_Log.LDF   

Run the dbcc shrinkfile to shrink the datafule (.mdf)

Dbcc shrinkfile (ebdata_data,400)

This will reduce the size (EBData_Data.MDF) to 400 mb and 1.6 gb free space to
 the os.)

Sethu SR
Database Analyst
Tan Tock Seng Hospital
Singapore


------------
Kristine Greenlee at 9/26/00 12:46:53 PM

This doesn&#39;t always work, but try running DBCC SHRINKDATABASE(dbname).  This function will not actually shrink the log file but mark it to shrink.  Then truncate log or backup log, depending on your log management strategy for the database.


------------
Seenu at 9/26/00 12:24:13 PM


Hi all,

In my server .mdf is 370 MB and .ldf is 3739 MB. I took the full backup. I want to reduce the size of the .ldf file. Can anyone suggest me how to reduce the log file size.

Thanks in advance,

Seenu. S

----------

