none
Transaction log file was increasting

    Pertanyaan

  • Hi Team,

    Transaction log file bounced around 54 GB Mirroring was good but T-Log file was not going down

    i had mirroring had a dump but unrestore T-logs ?

    Can you any please suggest me what actions we have to take here.

    Tx


    subu

    13 Maret 2012 12:59

Jawaban

  • No loss of data. Ding what you describe can be a perfcetly viable option, if you never took log backups and don't care to break the log backup chain. You set the db to simmple *instead* of taking a log backup. Then back to full again, do a db backup and then log backups (if you want log backups).

    For this thread, this isn't an option, though, since the database is mirrored.


    Tibor Karaszi, SQL Server MVP | web | blog

    14 Maret 2012 17:16

Semua Balasan

  • Hi,

    Are you backing up your transaction log regularly? 

    I don't understand what "i had mirroring had a dump but unrestore T-logs ?" means.  Could you rephrase, please.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    13 Maret 2012 13:07
  • Hi,

    You must backup your T-Log because (Full backup and Differential backup does not truncate your T-Log) and it will go increase with out truncation.

    I hope this is helpful.

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com

    13 Maret 2012 14:12
  • Could you start by telling us what the recovery model of the database is please?
    You can do this by looking at the data in sys.databases or right click on the database, choose properties and I believe it's in options somewhere.

    If you are not in simple mode (which it sounds like you are not) then you need to create a job to backup your transaction log on a regular basis, personally I used to do it every 30 mins. Once this is in place you can look at shrinking the log file to something more manageable.

    To be honest I would probably shrink it right back and regrow because you probably have thousands of what are called VLF's which will affect your performance.

    Google SQLSkills and VLFs and you find a great article on what it is and how to deal with it.


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich

    13 Maret 2012 14:57
  • Hi Subu,

    You need to query sys.databases and look for the field"log_reuse_wait_desc" This field will tell you the reason why your log cannot be reused due to which it is growing. These articles explains in details on what could be the different reasons and how you can tackle it.

    Factors That Can Delay Log Truncation

    A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server

    HTH

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    13 Maret 2012 16:26
  • You must scheduled regular log backups - that should mark inactive portion of log file to re-use other wise your log will grow and causes space issues. 

    http://uk.linkedin.com/in/ramjaddu

    13 Maret 2012 20:01
  • As the other members have suggested, t-log backups are a must. Can you let me know what's the version of SQL installed on the servers participating in mirroring? 
    13 Maret 2012 22:18
  • One question, if database recovery model is "Full" and before taking the backup of Transaction log if we change recovery model to "Simple", will there be any loss of data?

    Personally, I will never suggest above method to anyone, but just for knowledge want to know what will happen.


    Ankit H Peshwaria

    14 Maret 2012 15:47
  • No loss of data. Ding what you describe can be a perfcetly viable option, if you never took log backups and don't care to break the log backup chain. You set the db to simmple *instead* of taking a log backup. Then back to full again, do a db backup and then log backups (if you want log backups).

    For this thread, this isn't an option, though, since the database is mirrored.


    Tibor Karaszi, SQL Server MVP | web | blog

    14 Maret 2012 17:16