Shrink log file

Question

Shrink log file

ramyours2003

SSChampion

Points: 12268
More actions
March 2, 2017 at 10:42 am

#319301

We have too many VLFs on the log file which is configured in log shipping in primary server. For this we need to shrink the log file to reduce the VLF's , can any one suggest the approach not to break the log shipping ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You should be able to shrink the tlogs without changing the recovery model. It is the change of the recovery model that would break log shipping.

To fix the VLFs, it will require a shrink of the tlog and then regrow the tlog in appropriate increments. The shrink is what will remove the excess of log fragments.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramyours2003 SSChampion Points: 12268 More actions · Answer 2

thanks for the reply , I need a steps to perform the task ?

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

This is the sequence I use:

-- 0 = db file info
-- 1 = backup log
-- 2 = Shrink log file
-- 3 = extend log file to appropriate size
-- 4 = Diff backup
-- 5 = backup log after diff

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ramyours2003 - Thursday, March 2, 2017 10:13 PM
thanks for the reply , I need a steps to perform the task ?

Jason gave you the steps

To fix the VLFs, it will require a shrink of the tlog and then regrow the tlog in appropriate increments

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

GilaMonster - Friday, March 3, 2017 12:52 AM
ramyours2003 - Thursday, March 2, 2017 10:13 PM
thanks for the reply , I need a steps to perform the task ?
Jason gave you the steps
To fix the VLFs, it will require a shrink of the tlog and then regrow the tlog in appropriate increments

I guess I could have also provided a link to Kimberly's article 😉

Or maybe recommend hiring a consultant to do it.:Whistling:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

ALZDBA - Friday, March 3, 2017 12:52 AM
This is the sequence I use:
-- 0 = db file info
-- 1 = backup log
-- 2 = backup log again!
-- 3 = Shrink log file
-- 4 = extend log file to appropriate size
-- 5 = Diff backup
-- 6 = backup log after diff

These are the correct steps

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 7

Perry Whittle - Friday, March 3, 2017 4:44 AM
ALZDBA - Friday, March 3, 2017 12:52 AM
This is the sequence I use:
-- 0 = db file info
-- 1 = backup log
-- 2 = backup log again!
-- 3 = Shrink log file
-- 4 = extend log file to appropriate size
-- 5 = Diff backup
-- 6 = backup log after diff
These are the correct steps

😉