February 5, 2009 at 11:17 am
steveb (2/5/2009)
Ahmad Osama (2/5/2009)
GilaMonster (2/5/2009)
Ahmad Osama (2/5/2009)
GilaMonster (2/4/2009)
Why do you want to truncate and/or shrink your transaction log?I have a job to rebuild indexes..this increases the transaction log size....so i want to truncate it...
I assume you mean shrink, since truncate does not change the size of the log.
Why do you want to shrink the log? Free space in the log causes no problems. The log needs to be that size it it's growing that size so why not leave it?
yes....I do mean shrunk....So...a transaction log backup after rebuild will do the job...
No a transaction log back-up will truncate the log.. have a read of the links earlier in this thread.
Also why are you worried about shrinking the log, give it room to grow otherwise it will end up the same size again
GOT IT...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 5, 2009 at 11:24 am
let me add some more
backup log will truncate the log
that means it will reuse the inactive portion of the transaction (few exceptions) log so it will prevent them growing
However it will never return back the space of the inactive part of the log file to OS when you do a backup.So shrink is the only option...
Changing the recovery model to simple and bring to full will help
and its better than truncate_only as it has been deprecated in sql 2008..so better stop using them
February 5, 2009 at 11:28 am
you don't want to shrink it. You'll just grow it when you reindex again. You want to have enough free space to handle normal data change needs, including rebuilding indexes.
February 5, 2009 at 11:41 am
Krishna (2/5/2009)
I have a job to rebuild indexes..this increases the transaction log size....so i want to truncate it...
How frequent do you rebuild your indexes. There will be heavy logging while rebuilding indexes. You would have to change your Recovery model to Bulk Logged recovery model while rebuilding the indexes.
As adviced, you can set up a job to change the recovery model to bulk logged whenever you do indexes rebuild and after the process is done change ti back to Full Recovery model again.
And on top of it if you can make some time, investigate to see whether you really have to rebuild them or reorganize them..........
February 5, 2009 at 1:37 pm
nilmov (2/5/2009)
Changing the recovery model to simple and bring to full will help
But will break the log chain with all attendant risks and concerns.
and its better than truncate_only as it has been deprecated in sql 2008..so better stop using them
It was deprecated in 2005. It's removed completely in 2008.
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
February 6, 2009 at 3:38 am
yes ..you are right..that was typo ..its sql 2005
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply