September 10, 2014 at 2:47 pm
How much of a performance hit (a little, moderate, A LOT, don't do it, etc.) is there from shrinking the log file? The reason I ask is I'm faced with a situation where a production database has a much larger log file than is necessary. The database in question is a production database and it is pretty much used 24/7, so I'm attempting to find out if it is 'okay' or feasible to perform the action of shrinking a 100 GB .ldf during production hours.
The following was suggested to me in order to fix the matter:
1. Backup the TLOG
2. Change DB from Full to Simple
3. Change the initial size of the TLOG to something much smaller
4. Perform the log shrink
5. Change from Simple to Full
6. Perform full backup of DB to start log chain
Is this proper? If not, what would your process be to fix this matter?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
September 10, 2014 at 2:52 pm
Steps:
1. Backup the TLOG
2. Change DB from Full to Simple
3. Change the initial size of the TLOG to something much smaller4. Perform the log shrink
5. Change from Simple to Full
6. Perform full backup of DB to start log chain
That'll do it.
You only need to change the initial size when the log was created larger than what you want it. Not if it grew that size.
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
September 10, 2014 at 2:54 pm
GilaMonster (9/10/2014)
Steps:
1. Backup the TLOG
2. Change DB from Full to Simple
3. Change the initial size of the TLOG to something much smaller4. Perform the log shrink
5. Change from Simple to Full
6. Perform full backup of DB to start log chainThat'll do it.
You only need to change the initial size when the log was created larger than what you want it. Not if it grew that size.
So, the process of shrinking log files (or data files) won't cause a ridiculous performance hit during production hours?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
September 10, 2014 at 3:03 pm
Sean Perkins (9/10/2014)
So, the process of shrinking log files (or data files) won't cause a ridiculous performance hit during production hours?
Shrinking data files certainly will.
Shrinking log shouldn't, but do it in the quietest time you have,
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
September 10, 2014 at 3:17 pm
Thank you for your time Gail, I appreciate it!
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply