October 1, 2009 at 9:50 am
In a Full Recovery model, after every transaction log is backed up, is it worth shrinking the Log File?
If yes, what are the benefits?
Alternatively, are there side(ill)-effects to shrinking TL after backing it up?
I am thinking not only in terms of disk size it occupies, but also for overall performance.
Dan
October 1, 2009 at 9:54 am
repent_kog_is_near (10/1/2009)
In a Full Recovery model, after every transaction log is backed up, is it worth shrinking the Log File?
Only if you want to slow the DB down as it re-grows the log as necessary.
The log should never be shrunk on a regular basis. Size it for the activity in the DB and the frequency of the log backups and then LEAVE IT ALONE.
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
October 1, 2009 at 11:48 am
Gail
Thanks for the useful article.
Other than slowing down the DB, does it also cause lot of fragmentation?
Also what is the best way to estimate the ideal size of the log file.?
For TempDB, I monitor the size for a period of time, and then I fix the size of TempDB to the size where it held steady. Should I use the same technique for Database log also?
-Dan.
October 1, 2009 at 11:49 am
repent_kog_is_near (10/1/2009)
For TempDB, I monitor the size for a period of time, and then I fix the size of TempDB to the size where it held steady. Should I use the same technique for Database log also?-Dan.
Simply put, yes.
- 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
October 1, 2009 at 12:00 pm
repent_kog_is_near (10/1/2009)
GailThanks for the useful article.
Other than slowing down the DB, does it also cause lot of fragmentation?
Also what is the best way to estimate the ideal size of the log file.?
For TempDB, I monitor the size for a period of time, and then I fix the size of TempDB to the size where it held steady. Should I use the same technique for Database log also?
-Dan.
What period of time do you monitor the TempDB database for and how? And whats your deciding factor of when it's "steady" ? Sorry if these sound like interview questions,:w00t: lol... but just trying to gain some knowledge from this.
--
:hehe:
October 1, 2009 at 12:10 pm
Honestly, I set tempdb on autogrow, set the growth increment to half a gig or so, and leave it that way. Some systems, I set it to a full gig for the growth, depending on how much it grows and how fast. It'll settle out on its own that way.
For log and data files on other databases, I have a monitoring job that tells me when they look like they're getting ready to need to grow, and I get alerts from that. (I'm working on getting that and bunch of other auto-maintenance posted here. Should be ready to submit in a couple of weeks.)
- 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
October 1, 2009 at 7:06 pm
Slick
I monitor it throughout the day for every hour using a job.
So I have record of what it is, 24 times a day for 3 months. So I can easily decide what is the 'steady' value.
Dan
October 1, 2009 at 7:08 pm
GSquared
When you post scripts like that, do you put it in the Articles section in SSC?
Thanks
Dan
October 2, 2009 at 7:26 am
repent_kog_is_near (10/1/2009)
GSquaredWhen you post scripts like that, do you put it in the Articles section in SSC?
Thanks
Dan
That's where it will probably end up. It's going to be considerably more than a simple script, however.
- 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
October 3, 2009 at 3:41 pm
GSquared
Thanks.
Back to the main question, and in relation to fragmentation, we have a process where for a while we have had a truncation of log right after backup. What does it mean other than the effect on the performance (Slowness)?
I have removed the truncation process now from the Maintenance task.
Is the log file terribly fragmented by now? Any ideas to fix it now? What other specific things need to be fixed now, because we had the truncation of log going for a few months, for everyday, after backing up the log several times a day. Just trying to understand what all needs to be cleaned up now.
Dan
October 4, 2009 at 5:35 am
repent_kog_is_near (10/3/2009)
Back to the main question, and in relation to fragmentation, we have a process where for a while we have had a truncation of log right after backup. What does it mean other than the effect on the performance (Slowness)?
Truncate as in BACKUP LOG... TRUNCATE ONLY?
If you're running that they you're breaking the log chain and ensuring that you will not be able to restore to a point-in-time.
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
October 4, 2009 at 5:52 am
BACKUP LOG TO DISK. We keep all the backup of log files in case of need to restore to point in time.
October 4, 2009 at 6:02 am
Than what do you mean by 'truncation of log ... after backing up the 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
October 4, 2009 at 6:20 am
Yes Sir.
October 4, 2009 at 3:42 pm
repent_kog_is_near (10/4/2009)
Yes Sir.
(btw - the correct salutation in this case would be "Ma'am". don't let Quaigon Jin fool you)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply