none
Sql 2005 DB Log - Too big? RRS feed

  • Question

  • My log file is larger than my db file and I beleive it should not be. I run daily FULL DB backups and Transaction log backups every 20 minutes.
    The sizes are:
    DBNAME_Data.mdf - 1,146,624 KB
    dsi_db_Log.ldf - 1,785,856 KB

    I was under the impression that my T-log backups every 20 minutes would keep the .ldf file small, but I gues I am incorrect.

    Any tips or ideas?
    Thursday, August 13, 2009 5:37 PM

Answers

  • Hi SSurance

    if you would like to reduce the size of the log file, you just need to backup the database and run shrink command(not need to backup log with truncate_only); I agree with TiborK that if you backup log file with trancate_only, it will ruin your regular log backup.

    Besides, about problem --the log file growing bigger unexpectedly,there is an article to share with you:http://support.microsoft.com/default.aspx/kb/317375
    Based on my experience, after we taking the troubleshooting steps described in the article, 99% problems of this kind will be resolved.

    Regards
    Mark

    Tuesday, August 18, 2009 4:05 AM

All replies

  • Try to run the below query and post the results back.

    select name, log_reuse_wait_desc from sys.databases  


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Thursday, August 13, 2009 5:42 PM
    Moderator
  • u need to shrink the log file.

    DBCC SHRINKFILE
    ('file_name' or file_id ,target_size , TRUNCATEONLY )
       

    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Thursday, August 13, 2009 7:44 PM
  • Hi SSurance,

    1. Run DBCC OPENTRAN on that database and post the result.
    2. Run select name , log_reuse_wait_desc from sys.databases on that database and post the result.
    3. Have you configured replication with the option "REPLICATE SCHEMA CHANGES" set to true.?


    Thanks, Leks
    Friday, August 14, 2009 2:02 AM
    Answerer
  • USE DatabaseName
    GO
    DBCC SHRINKFILE(<TransactionLogName>, 1)
    BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(<TransactionLogName>, 1)
    GO 
    Hope that helps.
    Friday, August 14, 2009 2:33 AM
  • Please consider before using TRUNCATE_ONLY option as it curtails the tail of the log without commiting them.

    However, like cutting your hand off, that has some really nasty implications – you lose your ability to do a point-in-time restore once you do that.  Do a full backup as soon as possible afterwards, and then resume normal transaction log backups.
    Thanks, Leks
    Friday, August 14, 2009 2:41 AM
    Answerer
  • My guess is that you are doing some regular index rebuild or similar operation blowing up the transaction log. You can shrink the log (DBCC SHRINKFILE), but possibly the log file will only just blow up against the next time you do that batch operation. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Friday, August 14, 2009 4:22 PM
    Moderator
  • Tibork

    I run a weekly "Reorganize index task". Is that a problem?
    Monday, August 17, 2009 3:41 AM
  • Sankar,

    Result:

    master    ACTIVE_TRANSACTION
    tempdb    NOTHING
    model    NOTHING
    msdb    NOTHING
    ReportServer    NOTHING
    ReportServerTempDB    NOTHING
    AdventureWorksDW    NOTHING
    AdventureWorks    NOTHING
    DBNAME    NOTHING
    absolutefm    NOTHING
    Monday, August 17, 2009 3:42 AM
  • Lekss,

    1. No active open transactions.
    2. See post, above.
    3. no replication
    Monday, August 17, 2009 3:43 AM
  • That will cause lots of log records. So, you might want to only rebuild only indexes that are fragmented in the forst place, for instance. See for instance http://ola.hallengren.com/.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, August 17, 2009 8:12 AM
    Moderator
  • USE
     DatabaseName
    GO
    DBCC SHRINKFILE(<TransactionLogName>, 1)
    BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(<TransactionLogName>, 1)
    GO
    Hope that helps.
    If I do this, will it ruin my automatic backup routine? Also, will it lead to fragmenting?
    Monday, August 17, 2009 7:34 PM
  • It will break your log backup sequence. Doing BACKU LOG using TRUNCATE_ONLY isn't neccesary since you are already doing regular log backup (I assume). Shrinking log file do not fragment data.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, August 17, 2009 8:11 PM
    Moderator
  • I have a script that i use in my prod environment. I am not recommending this for your problem . Juts have a look at it,
    SQL SCRIPT:
    ==========
    repeat:
    -- Backup log in to a temp file
     backup log @dbname to disk ='C:\windows\temp\temp.trn' with INIT
     Checkpoint 
     set @SQL = 'use ' + rtrim(@dbname) + char(13)+ ' DBCC shrinkfile('+ rtrim(@var) + ',' + convert(varchar(10),@targetsizeMB) + ')'
     execute(@Sql)
     if (select (size*8/1024) from sys.master_files where database_id=db_id(@dbname) and type=1)< 100
     Begin
     print 'Shrinking completed for ' + @dbname + ' database'
     end
     else 
     Begin
     GOTO repeat
     end
    Thanks, Leks
    Monday, August 17, 2009 10:00 PM
    Answerer
  • Hi SSurance

    if you would like to reduce the size of the log file, you just need to backup the database and run shrink command(not need to backup log with truncate_only); I agree with TiborK that if you backup log file with trancate_only, it will ruin your regular log backup.

    Besides, about problem --the log file growing bigger unexpectedly,there is an article to share with you:http://support.microsoft.com/default.aspx/kb/317375
    Based on my experience, after we taking the troubleshooting steps described in the article, 99% problems of this kind will be resolved.

    Regards
    Mark

    Tuesday, August 18, 2009 4:05 AM
  • Thanks for the tips.
    Saturday, August 29, 2009 5:56 PM