How to reduce the log file(.ldf) file?

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

This doesn'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

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'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

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'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