SQL Server Performance

Truncate Transaction Log

Discussion in 'Performance Tuning for DBAs' started by durgesh, Sep 6, 2005.

  1. durgesh New Member

    Hi,
    I create one Database each with one subscription. The problem I am facing is that the respective transaction logs keep on growing. I want to truncate all the overgrown transaction logs.
    How do I do that ? Is the any specific way of doing so? Also is there any command(DBCC command) through which I can know the over all space that is being used by all the transaction logs?



    Thanks in advance.

    Durgesh.
  2. Akthar New Member

    Hi,
    Which recovery model you are using?

    AKTHAR DILMOHAMUD
    65 BENARES ST
    PORT LOUIS
    MAURITIUS
  3. ranjitjain New Member

    Hi Durgesh,
    The query has been answered in this fora alot times.
    Simply search for transaction in search option and you will find many useful dbcc commands to do that, many useful links to achive what you need and many useful tips and messages.
  4. durgesh New Member

    Thanks!
    Well I can get the DBCC Command but what about my first qustion?

    Durgesh.
  5. ranjitjain New Member

    Hi Durgesh,
    What you can do is create SP & decide the threshold, then compare it with TX log file size and then if it exceeds backup the log file to a dynamic location and then truncate.
  6. dineshasanka Moderator

  7. satya Moderator

    Whatever RECOVERY model you deploy one thing keep in mind in order to keep the size of Transaction log you must truncate the log using BACKUP LOG or TRUNCATE method that minimized the virtual size of Transaction log. Make sure you perform full backup once the log truncation method is deployed in the case of SIMPLE or BULK_LOGGED recovery model.

    The problem I am facing is that the respective transaction logs keep on growing. I want to truncate all the overgrown transaction logs.
    The problem is you may not have any scheduled job to address transaction log backup, so you must deploy a backup job or intermittent BACKUP LOG .. WITH TRUNCATE_ONLY to truncate the log. If you're performing huge inserts then ensure to keep it batches.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. durgesh New Member

    Thanks! dineshasanka.
    I was actually confused between shrink a log file and truncate a log file and I still wonder is it the same thing or different.
    I mean to truncate a log file we need to shrink it right?
    moreover i did not find the DBCC command to know the amount of space used by all the log files. could anybody please guide me to a link?

    Thanks in advance.

    Durgesh.
  9. dineshasanka Moderator

  10. durgesh New Member

    Thanks! satya.
    One more question, Is it neccesary to write USE %DBName% before we truncate the log of that database
    for example.....


    USE TTEST1
    BACKUP LOG TTEST1 WITH TRUNCATE_ONLY
    DBCC SHRINKFILE ('TTEST1_log')

    Will this work?

    (Default database selected is master)

    BACKUP LOG TTEST1 WITH TRUNCATE_ONLY
    DBCC SHRINKFILE ('TTEST1_log')

    This doesnt work actually .........we need to select the database.....

    Durgesh.
  11. ghemant Moderator

    hi,

    quote:Originally posted by durgesh

    Thanks! dineshasanka.
    I was actually confused between shrink a log file and truncate a log file and I still wonder is it the same thing or different.
    I mean to truncate a log file we need to shrink it right?
    moreover i did not find the DBCC command to know the amount of space used by all the log files. could anybody please guide me to a link?

    Thanks in advance.

    Durgesh.

    its dbcc sqlperf(logspace) , it will show you log space information for all databases.



    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
  12. durgesh New Member

    Thanks! a lot Hemantgiri.

    Durgesh.
  13. ghemant Moderator

    Hi Durgesh, <br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by durgesh</i><br /><br />Thanks! a lot Hemantgiri.<br /><br />Durgesh.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Most welcome [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
  14. satya Moderator

    DBCC SHRINKFILE applies to the files in the current database. Switch context to the database to issue a DBCC SHRINKFILE statement referencing a file in that particular database.


    quote:Originally posted by durgesh

    Thanks! satya.
    One more question, Is it neccesary to write USE %DBName% before we truncate the log of that database
    for example.....


    USE TTEST1
    BACKUP LOG TTEST1 WITH TRUNCATE_ONLY
    DBCC SHRINKFILE ('TTEST1_log')

    Will this work?

    (Default database selected is master)

    BACKUP LOG TTEST1 WITH TRUNCATE_ONLY
    DBCC SHRINKFILE ('TTEST1_log')

    This doesnt work actually .........we need to select the database.....

    Durgesh.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. amer_md_jamal New Member

    Hi Durgesh

    I am not pretty clear about your 1st question, but yes there is a command to know as to how much space the log files are using in your server. Try this:

    DBCC SQLPERF (LOGSPACE)

    It will display a result as a table in which it will display the database, its log size in mb and also its logspace used in %.

    Hope this solves part of your problem.


    Thanks & Regards

    Amer Mohammed Jamal
    SQL DBA,
    Wipro Technologies,
    Hyderabad, India
    email: amer.jamal@wipro.com
  16. durgesh New Member

    Thanks All.

    Durgesh.

Share This Page