SQL Server Performance

IPCC environment_Issue with SQL 2005

Discussion in 'ALL SQL SERVER QUESTIONS' started by pyarajan, Oct 30, 2012.

  1. pyarajan New Member

    Need your kind help in resolving an issue at our customer site

    My Rogger (Router+ Logger) server is not getting the half hour data from my PG server sometimes. Below are our findings on a DB related error.

    ü As analysed , we identified the below DB error exactly at the time of the data issue

    The transaction log for database is full, to find why space in the log is not reused see the log_reuse_wait_desc column in the sys databases

    ü As mentioned in the error , we checked the sys.databases and the fields where log_reuse_wait_desc is ‘NOTHING’
    ü There are two ways to handle the transaction log in SQL DB
    o Either by defining a permanent size say 250 MB and allow the log to get purged once it reaches the limit (OR)
    o By specifying auto growth where the size will be incremented each time it reaches the maximum size
    ü In our environment, we are following the 1st method and as analysed, the transaction log purging got failed & thrown the above error(concluded this based on the info in the below link)
    http://support.microsoft.com/kb/317375
    ü

    Below are my queries:

    ü Why my Transaction log purging gets failed all of a sudden though I have a sufficient DB size and no changes in environment?
    ü If it is so, will it block the data insertion sometimes?
    ü Is there any other simple way to handle this transaction logs and purging?
    ü What is the impact if I go with option 2 ie.. enabling auto growth of the transaction log size? Is this a recommended one?

    Your intervention would really help us to narrow down this issue
  2. Shehap MVP, MCTS, MCITP SQL Server

    First , welcome to Forums

    To make things more clear about transactions log file growth and how to manage them , there are mainly 2 approaches:

    More Granular Approach : Configuring DB with Full mode option and schedule properly transactions log backup using truncate option to recycle it each time log backup is taken

    Less Granular Approach: Configuring DB with simple mode where no transaction log backup is applicable .

    So simply , you can go with any of these approaches instead of setting the maximum file size to 250 GB which I am surely it will comes up with lots of troubles ending up with a standstill case for all OLTP transactions ( Insert +Update +Delete + Merge) of your APP such this error message appeared for you

    But bear in your mind that both approaches above may sometimes fail and transaction log file can get out of control then due to some long OLTP transaction so what you can do then …?

    Just you can run the below DBCC query :

    Use DBName

    DBCC OPENTRAN

    Which will figure out that transactions holding your transactions log files and preventing it from takign checkpoitnt and trucnate as well

    Kindly let me know if any further help is needed

Share This Page