Results 1 to 4 of 4

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

  1. #1
    Seenu Guest

    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

  2. #2
    Kristine Greenlee Guest

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

    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

  3. #3
    Seenu Guest

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


    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

  4. #4
    sethu Guest

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •