January 14, 2009 at 11:41 am
I have found an issue where one log file for one particular DB (TEST_DB) in the primary file group increased more than threshold and there was only 1% free space in the file. I used following command and that worked.
--Michael
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
January 14, 2009 at 11:49 am
Where is the command dude?:D
January 14, 2009 at 1:40 pm
Before you go truncating your transaction log on a regular basis, please note that truncating the log is not a good maintenance practice.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2009 at 1:42 pm
Log truncation is a "BAD" thing. Please, think TWICE before you do it!
* Noel
January 17, 2009 at 1:17 pm
BACKUP LOG TESTDB WITH TRUNCATE_ONLY
I wanted to understand where this backup is taken. I couldn't find the backup of the transaction log.
Gilla you are correct. I found somewhere that the TRUNCATE_ONLY is obsolete now from yukon onwards. What should be done in case the tlogs increase and we have very less space.
-M
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
January 17, 2009 at 1:53 pm
MichaelJasson (1/17/2009)
I wanted to understand where this backup is taken. I couldn't find the backup of the transaction log.
It's not. That's the whole point. TRUNCATE ONLY means don't write out a backup file, just discard the inactive log records. That's why it breaks log chains.
I found somewhere that the TRUNCATE_ONLY is obsolete now from yukon onwards. What should be done in case the tlogs increase and we have very less space.
It's not obsolete from yukon (SQL 2005). It's removed in SQL 2008 (katmai). Either back up the log, or switch to simple recovery, run a checkpoint and switch back to full. Then take a full backup to restart the log chain
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2009 at 4:20 pm
Thanks Gila for nice explaination. One curiousity here. When we make the recovery model = simple and the create the checkpoint and then again change this to full backup. Does this not break the chain. I understand that whenever we would change the model to simple then the committed log files would be lost. I am not very sure though ..
-M
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
January 17, 2009 at 10:33 pm
GilaMonster (1/17/2009)
MichaelJasson (1/17/2009)
I wanted to understand where this backup is taken. I couldn't find the backup of the transaction log.It's not. That's the whole point. TRUNCATE ONLY means don't write out a backup file, just discard the inactive log records. That's why it breaks log chains.
I found somewhere that the TRUNCATE_ONLY is obsolete now from yukon onwards. What should be done in case the tlogs increase and we have very less space.
It's not obsolete from yukon (SQL 2005). It's removed in SQL 2008 (katmai). Either back up the log, or switch to simple recovery, run a checkpoint and switch back to full. Then take a full backup to restart the log chain
Anywany if u want to talt log backup with truncation , Please take full backup of database after that..
January 18, 2009 at 1:58 am
MichaelJasson (1/17/2009)
One curiousity here. When we make the recovery model = simple and the create the checkpoint and then again change this to full backup. Does this not break the chain.
Yes, it does. That's why I said take a full backup immediately thereafter to restart the log chain. The only way to remove the inactive log entries without breaking the log chain is to take a lob backup and keep the backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2009 at 2:09 am
Paresh Prajapati (1/17/2009)
Anywany if u want to talt log backup with truncation , Please take full backup of database after that..
I said that.
GilaMonster (1/17/2009)
It's not obsolete from yukon (SQL 2005). It's removed in SQL 2008 (katmai). Either back up the log, or switch to simple recovery, run a checkpoint and switch back to full. Then take a full backup to restart the log chain
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply