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.
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.
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
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.
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
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
My Blog: Http://SQL.RichardDouglas.co.uk
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.
Regards, Ashwin Menon My Blog - http:\\sqllearnings.com
2012年3月13日 20:01You 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.
2012年3月13日 22:18As 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?
- 已编辑 Rocky_SQLDBA 2012年3月13日 22:19
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
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.
- 已标记为答案 amber zhangModerator 2012年3月20日 6:26