locked
How to decrease size of the .ldf file RRS feed

  • Question

  • Hi, 

    I am facing one problem with sql server database. We are using Sql server 2008 database in our production system. We created one database for our application and it is working fine. The problem with database is the Log(.ldf) file increases day by day(min 10 GB ). Now the ldf file takes 360 GB and .mdf file takes 2.57 GB in our hard disk. This database contains 90 tables and 3 of them contain min 8 lakhs of data and rest of tables contains thousands and hundreds of data only. I want to know why the .ldf file increases min 10 GB day by day and How to decrease this .ldf file. The following are the properties we maintain for our database.

    Collation: SQL_Latin1_General_CP1_CI_AS

    Recovery Model: Full

    Compatibility Level: Sql Server 2008(100)

    My felling it is problem with Recovery Model of type Full. But our client wants Full recovery model.

    How to solve this problem. My ultimate goal is I have to stop the increasing of .ldf file day by day.

    This is very important and urgent for me.

    Please help me as early as possible.

    Thanks in advance………..

    • Moved by richbrownesq Monday, July 26, 2010 10:49 AM (From:Transact-SQL)
    Monday, July 26, 2010 10:28 AM

Answers

  • You need to backup log file in order to control physical size of the log. To reduce the size I would suggest

    1) backup log file

    2) dbcc shrinkfile (logfile,truncateonly)

    3) modify size of the log by using ALTER DATABASE command to the appopriate size for no often growing


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Tom Li - MSFT Wednesday, July 28, 2010 11:07 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:38 AM
    Monday, July 26, 2010 10:40 AM
    Answerer
  • I would guess you aren't taking any transaction log backups. If you have the FULL recovery model, and you are seeing large growth of the LDF, look to schedule frequent (say hourly) log backups which will truncate the inactive portion of the log. This will help you manage the size of the log file and also provide point in time recovery (which i'm sure the client would like!!).

    As a one off, you can use DBCC SHRINKFILE to reduce the size of the transaction log but using this in isolation won't solve the issue.

     


    every day is a school day
    • Proposed as answer by Tom Li - MSFT Wednesday, July 28, 2010 11:08 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:38 AM
    Monday, July 26, 2010 10:48 AM
  • With your database in FULL recovery mode, rebuilding indexes will be fully logged. You can reduce this to be a minimally logged operation by changing to BULK LOGGED recovery but if your clients want FULL recovery then i'd suggest leaving this.

    Do you have a real need to rebuild indexes every hour? That seems quite an aggressive strategy to me. Consider going to daily reindexes during quiet time which should help alleviate the issue.

    But as already stated, regular transaction log backups through the day will also help.

     


    every day is a school day
    • Proposed as answer by Tom Li - MSFT Wednesday, July 28, 2010 11:08 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:39 AM
    Monday, July 26, 2010 12:20 PM
  • Hello

    Yes; rebuilding the Indexes is data activity.  Apart from increasing your log size it also impacts on performance.

    I would suggest that at the earliest OOH opportunity you do the following: -

    Take a full Backup of the Database.

    Set the Database to Simple and then shrink the Log File.

    Set the Database to Full and schedule Regular Transaction Log Backups and Nightly Full Backups.

    Take a full backup of the Database (this is so that you have a Full Backup so that Transaction Log Backups can be taken and used).

    Limit your Index Rebuilds to once a day.


    Tony C
    • Proposed as answer by Tom Li - MSFT Wednesday, July 28, 2010 11:08 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:39 AM
    Monday, July 26, 2010 12:20 PM
  • Have you asked your client WHY they want FULL recovery? If they actually want it to be able to restore as much data as possible after a failure, then you have to do more planning to make sure this is possible.

    Please read about backup and restore, and about their relationship to recovery models. An infrequent log back schedule, along with your index rebuilds that are happening MUCH TOO OFTEN are going to give you a database that is hard to manage.

    Make sure you have a full restore plan and that you've tested it. Otherwise, the fact that the database is in FULL recovery will be meaningless. There is also a forum here just for Disaster Recovery. You might want to take a look there and even look for posts on general management strategies.


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:39 AM
    Monday, July 26, 2010 1:28 PM

All replies

  • Hi,

      The log growth can happen for multiple reasons ... For example,

    1. If lengthy transactions are used in the code and the transactions are not handled/closed properly, the log file will go unexpectedly

    2. Are you taking any database/log  backups ?

    3. Are you doing any BULK operations ?

     

    -- Reddy Balaji C.

    Monday, July 26, 2010 10:34 AM
  • You need to backup log file in order to control physical size of the log. To reduce the size I would suggest

    1) backup log file

    2) dbcc shrinkfile (logfile,truncateonly)

    3) modify size of the log by using ALTER DATABASE command to the appopriate size for no often growing


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Tom Li - MSFT Wednesday, July 28, 2010 11:07 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:38 AM
    Monday, July 26, 2010 10:40 AM
    Answerer
  • I would guess you aren't taking any transaction log backups. If you have the FULL recovery model, and you are seeing large growth of the LDF, look to schedule frequent (say hourly) log backups which will truncate the inactive portion of the log. This will help you manage the size of the log file and also provide point in time recovery (which i'm sure the client would like!!).

    As a one off, you can use DBCC SHRINKFILE to reduce the size of the transaction log but using this in isolation won't solve the issue.

     


    every day is a school day
    • Proposed as answer by Tom Li - MSFT Wednesday, July 28, 2010 11:08 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:38 AM
    Monday, July 26, 2010 10:48 AM
  • Hi Balaji,

    Thanks for your replay.

    In some stored procedures we used Begin transaction and End transaction. But I am sure we closed all the transactions properly. we don't have that much lengthy transactions.

    Our client takes back up for every 5 days in Full back up mode.(both database and log)

    Till now we don't use any Bulk operations.

    But we wrote a one job that rebuilds the all indexes for every one hour.

    This job causes any issue?

    Monday, July 26, 2010 12:03 PM
  • one more thing i forgot to notice, we wrote one job, that rebuilds the all indexes for every one hour.

    This job causes any issue?

    Monday, July 26, 2010 12:05 PM
  • Hi,

     Yes Pavan . Please find this statement from the specified URL ...

    " If you find that your transaction log grows to an unacceptable size when you run DBCC REINDEX, you can minimize this growth by switching from the Full Recovery mode to the Bulk-Logged mode before you reindex, and when done, switch back. " --

    http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx

    -- Reddy Balaji

    ## Mark as ANSWER if it answers your question

    Monday, July 26, 2010 12:18 PM
  • With your database in FULL recovery mode, rebuilding indexes will be fully logged. You can reduce this to be a minimally logged operation by changing to BULK LOGGED recovery but if your clients want FULL recovery then i'd suggest leaving this.

    Do you have a real need to rebuild indexes every hour? That seems quite an aggressive strategy to me. Consider going to daily reindexes during quiet time which should help alleviate the issue.

    But as already stated, regular transaction log backups through the day will also help.

     


    every day is a school day
    • Proposed as answer by Tom Li - MSFT Wednesday, July 28, 2010 11:08 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:39 AM
    Monday, July 26, 2010 12:20 PM
  • Hello

    Yes; rebuilding the Indexes is data activity.  Apart from increasing your log size it also impacts on performance.

    I would suggest that at the earliest OOH opportunity you do the following: -

    Take a full Backup of the Database.

    Set the Database to Simple and then shrink the Log File.

    Set the Database to Full and schedule Regular Transaction Log Backups and Nightly Full Backups.

    Take a full backup of the Database (this is so that you have a Full Backup so that Transaction Log Backups can be taken and used).

    Limit your Index Rebuilds to once a day.


    Tony C
    • Proposed as answer by Tom Li - MSFT Wednesday, July 28, 2010 11:08 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:39 AM
    Monday, July 26, 2010 12:20 PM
  • Pavan

    Please do not rebuild all indexes blindly , see the below great link

    http://ola.hallengren.com/


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 26, 2010 12:20 PM
    Answerer
  • Pavan

    Please do not rebuild all indexes blindly , see the below great link

    http://ola.hallengren.com/


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 26, 2010 12:20 PM
    Answerer
  • Have you asked your client WHY they want FULL recovery? If they actually want it to be able to restore as much data as possible after a failure, then you have to do more planning to make sure this is possible.

    Please read about backup and restore, and about their relationship to recovery models. An infrequent log back schedule, along with your index rebuilds that are happening MUCH TOO OFTEN are going to give you a database that is hard to manage.

    Make sure you have a full restore plan and that you've tested it. Otherwise, the fact that the database is in FULL recovery will be meaningless. There is also a forum here just for Disaster Recovery. You might want to take a look there and even look for posts on general management strategies.


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:39 AM
    Monday, July 26, 2010 1:28 PM
  • First, when using full recovery model - you have to perform frequent (every 15 minutes, 30 minutes - or hour) transaction log backups.  Full backups do not truncate the log and allow the space to be reused.
     
    Second, there is no reason to schedule a rebuild of all indexes every hour.  That is just wasting time and resources on the server.  You should only rebuild indexes that need to be rebuilt/reorganized - and that should only be scheduled to run at most once a day.  Google SQL Fool - she has a very good script for performing smart re-indexing.
     
    And finally, I would recommend daily full backups and transaction log backups at least every hour for any system where users are adding or updating data on a daily basis.
     
    Jeff
    "Pavan Kumar Chebrolu" wrote in message news:df4a9d27-aaf1-4f91-91d5-409954769a6b...

    Hi Balaji,

    Thanks for your replay.

    In some stored procedures we used Begin transaction and End transaction. But I am sure we closed all the transactions properly. we don't have that much lengthy transactions.

    Our client takes back up for every 5 days in Full back up mode.(both database and log)

    Till now we don't use any Bulk operations.

    But we wrote a one job that rebuilds the all indexes for every one hour.

    This job causes any issue?

    Tuesday, July 27, 2010 1:44 AM
  • Hello

    Yes; rebuilding the Indexes is data activity.  Apart from increasing your log size it also impacts on performance.

    I would suggest that at the earliest OOH opportunity you do the following: -

    Take a full Backup of the Database.

    Set the Database to Simple and then shrink the Log File.

    Set the Database to Full and schedule Regular Transaction Log Backups and Nightly Full Backups.

    Take a full backup of the Database (this is so that you have a Full Backup so that Transaction Log Backups can be taken and used).

    Limit your Index Rebuilds to once a day.


    Tony C

    Thanks, it works.
    Wednesday, October 19, 2011 2:30 PM