Transaction log file was increasting

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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? 

Answer 7

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

Answer 8

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