Post reply

Truncate Log

  • October 3, 2005 at 11:18 am

    #110998

    I have Hr_database

    Hr_database_data.mdf 1235MB

    Hr_database_log.LDF  25623502MB

    The LDF is growing how to truncate this log file, any scripts??

     

    Thanks NITa

  • October 3, 2005 at 11:28 am

    #594652

    checkpoint



    A.J.
    DBA with an attitude

  • October 3, 2005 at 11:36 am

    #594654

    Thanks but I am a new DBA, how don't know what you mean by checkpoint. I want to truncate the log file I mean the LDF file....how to write the complete script.

  • October 3, 2005 at 11:45 am

    #594657

    Lookup "Backup Log" in Books Online and you should be able to figure it out from there.



    A.J.
    DBA with an attitude

  • October 3, 2005 at 12:22 pm

    #594661

    Thanks for Inspiring me

    I did

    Backup log Hr_database with Truncate_only

    But when I see the size still remain same???

     

  • October 3, 2005 at 1:06 pm

    #594666

    There is a diffference between spaced used and space allocated. When you truncate the trans log, you are mainly reducing the amound of space used on the transaction log.

    To reduce the actual allocated space, look for: DBCC SHRINKFILE

    Be sure to understand the implications that this commands has.

    The truth is just a point of view accepted by majority

    David Stohlmann

  • October 4, 2005 at 12:58 am

    #594734

    That must be a typo? Please tell me you don't really have a 25 TERABYTE log file for a 1 GB database?

  • October 4, 2005 at 2:21 am

    #594744

    In order to reduce your LDF, you must back it up. Running checkpoint will write data from the ldf to disk. SQL Server itself issues checkpoints at predefined intervals. However to progress your issue, and implement good practice to your environment, create a maintenance plan to back up your ldf on a regular basis. Also decide on a full and differential backup strategy. You never mentioned what version of SQL you have. ( I assume the DB is in FULL Recovery ). I have in the past experienced issues reducing LDF's for SQL 7. Don't want to get into logical logs files with an LDF, but it can help to understand how these are structured. Anyway try the following.

    Backup the database. Backup the tansaction log. If log file still not shrunk run dbcc shrinkfile. If still not shrunk try to clarify the structure of your ldf by using Enterprise, right click on the database, all tasks, shrink database, files, select ldf from drop down menu and see how much space is available. DBCC SQLPERF (logspace) will also return this info.

    If you don't make any progress, let me know and I'll try to help further.

    Rgds, Derek

  • October 4, 2005 at 11:30 am

    #594935

    something like that:

    Backup LOG Hr_database WITH NO_LOG

    GO

    DBCC SHRINKFILE ('Hr_database_log',TRUNCATEONLY)

    GO

     

  • October 4, 2005 at 11:56 am

    #594949

    Thanks for you valueble help.

    MS_SQL ver 2000

    DB Name: HR_database

    HR_database_data.mdf  size is 403072 KB

    HR_database_log.LDF size is 40125675 KB

    I took Complete backup then I ran

    Backup LOG HR_database WITH NO_LOG

    GO

    dbcc checkdb

    BUT Still the size remains same????

  • October 4, 2005 at 12:31 pm

    #594958

    You didn't run the dbcc shrinkfile, run that and your file will shrink.

    Question for the experts here, is dbcc shrinkfile('log_file', truncateonly) the same as the option "Compress pages and then truncate free space from the file" in EM > Shrink File? And if yes, does running this option also remove the inactive transactions (and so requiring an immediate full backup)?

  • October 4, 2005 at 12:50 pm

    #594961

    I have found this to be useful:

    dump tran <database> with no_log

  • October 4, 2005 at 12:57 pm

    #594963

    I should mention that you should backup the database prior to running the code above.

  • October 4, 2005 at 1:31 pm

    #594970

    Is your database recovery mode in Simple or Full? check the database properties using EM. If simple, changed to Full, otherwise the truncate might not work. When using the script

    Backup LOG Hr_database WITH NO_LOG

    GO

    DBCC SHRINKFILE ('Hr_database_log',TRUNCATEONLY)

    GO

    set the size you want the log to be:

    Backup LOG Hr_database WITH NO_LOG

    GO

    DBCC SHRINKFILE ('Hr_database_log',TRUNCATEONLY, 20)

    GO

    Which will take the log to 20 MB or any size you want to have, just as a reminder the log should be at least 25% of your MDF file.

    There is another way to recreate the log, not very used but you can just detach the database, rename the LDF file and then reattach, because the DB is not going to find the old log, the system creates a new one. Make sure that you backup your database first (full backup) to have a plan B. The question ypou have to ask is: do I really need the log as is? or can I get rid of the old log (remane it will keep the old log handy in case you need to go back to that one)? In any event, PLEASE BACKUP first!! Good luck

  • October 4, 2005 at 1:34 pm

    #594971

    It worked through shrink via EM,

    When I did

    DBCC SHRINKFILE ('Hr_database_log',TRUNCATEONLY)

    GO

    Could not locate file 'Quanta_repl_log' in sysfiles.

     

    Thanks

    Nita

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply