February 4, 2009 at 11:21 am
Hi,
What is the correct way to truncate trucate transaction Log..truncate_only or dbcc shrinklog after transaction log backup???
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 4, 2009 at 11:25 am
The correct way to truncate the transaction log is run a log backup.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2009 at 11:45 am
It sounds like you're talking about two different things. Like GSquared said, truncating occurs automatically after you backup the log file. Shrinking the size of the log file can be done through DBCC SHRINKFILE or DBCC SHRINKDATABASE.
Greg
February 4, 2009 at 11:46 am
GSquared (2/4/2009)
The correct way to truncate the transaction log is run a log backup.
okies...however some time it just didn't work..in those cases I hv used dbcc shrinklog to truncate log.....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 4, 2009 at 11:48 am
Why do you want to truncate and/or shrink your transaction log?
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 4, 2009 at 11:51 am
Ahmad Osama (2/4/2009)
in those cases I hv used dbcc shrinklog to truncate log.....
Shrink file will never truncate the log. It shrinks the file. Truncate refers to discarding old log records and only a log backup, a log truncation or (in simple recovery only) a checkpoint will truncate the log.
That said, a log backup will never shrink the log file.
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 4, 2009 at 12:05 pm
February 4, 2009 at 2:57 pm
Another way to truncate logfile without taking backup is,
DBCC sqlperf ('logspace')
check for the %used column for your database
do the following,
Step1: Change your Recovery Model from Full to Simple
Step2 : Change your Recovery Model from Simple to Full.
DBCC sqlperf ('logspace')
check for the %used column for your database
Thats it, But I don;t know if this is a good practice to truncate log ?
Experts please comment.
Thanks,
IM.
February 4, 2009 at 3:03 pm
sayfrend (2/4/2009)
Another way to truncate logfile without taking backup is,DBCC sqlperf ('logspace')
check for the %used column for your database
do the following,
Step1: Change your Recovery Model from Full to Simple
Step2 : Change your Recovery Model from Simple to Full.
DBCC sqlperf ('logspace')
check for the %used column for your database
Thats it, But I don;t know if this is a good practice to truncate log ?
Experts please comment.
Thanks,
IM.
Basically, do this kind of thing only if you don't care about the data in your database. If you do any of these methods for truncating the log other than backing it up, you risk losing data, because the log is part of what you can use to restore a crashed database.
If losing what's in the transaction log is fine for a given database (sometimes it is, and point-in-time restore is unimportant), then the database should be in simple recovery mode, and you won't have to truncate the log because SQL Server will do it for you.
Shrinking the log is, usually, a waste of time. Most times, it grew because it needed the space, and shrinking it just means it's going to grow again. Not always, but most of the time. Same goes for shrinking the data files. Usually a bad idea.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2009 at 4:54 pm
GSquared (2/4/2009)
Shrinking the log is, usually, a waste of time. Most times, it grew because it needed the space, and shrinking it just means it's going to grow again. Not always, but most of the time. Same goes for shrinking the data files. Usually a bad idea.
I have read a lot about the transaction log lately and I have noticed a few reoccuring themes.
Size your transaction log as best as you can. It will continue to grow to what it needs.
Your log backups truncate the log, not shrink it. Big difference.
Only shrink transaction log when you have to. Otherwise, it will grow right back to the size it needs. 🙂
I did read one article where it mentioned trying to size the transaction log accordingly. This article in particular I found interesting:
http://sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Hopefully it helps the OP.
Cheers,
Jason
February 5, 2009 at 12:06 am
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...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 5, 2009 at 1:28 am
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.
Did you check the fragmentation levels before re building your indexes, may be you can reorganize them?
And Truncating transaction logs is a very bad practice as advised before. It will break the log chain and you would never be able to achieve point-in-time recovery. On top of it if you truncate the tlog you would have to take a full backup and then transaction logs to maintain the log chain.
February 5, 2009 at 8:30 am
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?
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 5, 2009 at 10:41 am
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...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 5, 2009 at 10:50 am
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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply