March 17, 2011 at 8:01 am
os: Win 2k8 64 bit
SQL: 2008 R2
SP: 2
Db: 41 gig
Log: 74 gigs (used to be around 22-25 gigs)
Db rec model: full
Db backups: every 6 hours, full
Tlog backups: every hour
replication: none
This symptom appeared suddenly last Monday. From one day to the next, the size of the full backup rocketed to 300 %+ what it used to be. Digging into what could be wrong, I found in the tlog a high number of active (status=2) vlf, 713 to be exact. And this number does not go down. Although newly created vlf (after 713) behaves normally. Tlog backups on the other hands remains normal, ranging from 742 k in low activity period to 14562 k in high activity; apart from the one right after the reorganize (at night) or the reindex (on the weekend) which is much higher.
I have tried so far:
- making multiple tlogs backup
- changing to simple rec model and running dbcc shrinkfile
- I have tried both of the above having all users/processes stopped; ie. no active connections on the db
- There was no open transactions and no long running queries at both times of tests
I have not run the checkpoint command yet, since in my opinion this is what a tlog backup run in it's sequence of processes when the db is in rec model full. I have not run it in simple rec model either.
See below for DBCC loginfo result :
FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
2458752819222758782640
245875246694422758792640
245875292569622758802640
2712704138444822758812640
2262144209715222758822642245932000000044800168
2262144235929622758832642245932000000086600167
2262144262144022758842642245933000000050200053
2327680288358422758852642245934000000051000014
2327680321126422758862642245936000000020700316
2393216353894422758872642245937000000018800319
2393216393216022758882642245938000000035700302
2458752432537622758892642245939000000039300287
2253952478412822758902642245940000000045700288
2270336503808022758912642245940000000045700288
22539525308416227589221282245941000000050600118
2270336556236822758932642245941000000050600118
2253952583270422758942642245943000000046200003
2335872608665622758952642245943000000046200003
2253952642252822758962642245945000000047400055
2401408667648022758972642245945000000047400055
22539527077888227589821282245947000000031900255
24669447331840227587721282245947000000031900255
22539527798784227589921282245948000000056000466
22539528052736227590021282245948000000056000466
22785288306688227590121282245948000000056000466
22539528585216227590221282245950000000037600588
22539528839168227590321282245950000000037600588
23440649093120227590421282245950000000037600588
22539529437184227590521282246009000000041300351
22539529691136227590621282246009000000041300351
24751369945088227590721282246009000000041300351
225395210420224227590821282246012000000030600465
225395210674176227590921282246012000000030600465
225395210928128227591021282246012000000030600465
228672011182080227591121282246012000000030600465
226214411468800227591221282246016000000044900259
226214411730944227591321282246016000000044900259
226214411993088227591421282246016000000044900259
239321612255232227591521282246016000000044900259
232768012648448227591621282246020000000031600667
232768012976128227591721282246020000000031600667
232768013303808227591821282246020000000031600667
232768013631488227591921282246020000000031600667
232768013959168227592021282246024000000050800573
232768014286848227592121282246024000000050800573
232768014614528227592221282246024000000050800573
245875214942208227592321282246024000000050800573
23932161540096022759242642246073000000026400276
23932161579417622759252642246073000000026400276
23932161618739222759262642246073000000026400276
23932161658060822759272642246073000000026400276
23932161697382422759282642246077000000040800016
23932161736704022759292642246077000000040800016
23932161776025622759302642246077000000040800016
25242881815347222759312642246077000000040800016
24587521867776022759322642246081000000034100001
24587521913651222759332642246081000000034100001
24587521959526422759342642246081000000034100001
25242882005401622759512642246081000000034100001
25242882057830422758372642246268000000003200067
25242882110259222758382642246268000000003200067
25242882162688022758392642246268000000003200067
25242882215116822758402642246268000000003200067
25242882267545622758412642246272000000013600172
25242882319974422758422642246272000000013600172
25242882372403222758432642246272000000013600172
27208962424832022758442642246272000000013600172
25898242496921622758452642246294000000101200027
25898242555904022758462642246294000000101200027
25898242614886422758472642246294000000101200027
27208962673868822758482642246294000000101200027
26553602745958422758492642246298000000103900002
26553602811494422758502642246298000000103900002
26553602877030422758512642246298000000103900002
27864322942566422758522642246298000000103900002
27208963021209622758532642246301000000073600346
27208963093299222758542642246301000000073600346
27208963165388822758552642246301000000073600346
28519683237478422758562642246301000000073600346
27864323322675222758572642246306000000071500414
27864323401318422758582642246306000000071500414
27864323479961622758592642246306000000071500414
29830403558604822758602642246306000000071500414
29175043656908822758612642246310000000049600411
29175043748659222758622642246310000000049600411
29175043840409622758632642246310000000049600411
29175043932160022758642642246310000000049600411
29830404023910422758652642246314000000037600412
29830404122214422758662642246314000000037600412
29830404220518422758672642246314000000037600412
211141124318822422758682642246314000000037600412
211141124430233622758692642246318000000142000413
211141124541644822758702642246318000000142000413
211141124653056022758712642246318000000142000413
211141124764467222758722642246318000000142000413
211796484875878422758732642246322000000107800412
211796484993843222758742642246322000000107800412
211796485111808022758752642246322000000107800412
213762565229772822758762642246322000000107800412
213107205367398422759352642248318000000013300010
213107205498470422759362642248318000000013300010
213107205629542422759372642248318000000013300010
214417925760614422759382642248318000000013300010
214417925904793622759392642248322000000188700015
214417926048972822759402642248322000000188700015
214417926193152022759412642248322000000188700015
215728646337331222759422642248322000000188700015
215728646494617622759432642248326000000200900012
215728646651904022759442642248326000000200900012
215728646809190422759452642248326000000200900012
217694726966476822759462642248326000000200900012
217694727143424022759472642248330000000235500001
217694727320371222759482642248330000000235500001
217694727497318422759492642248330000000235500001
218350087674265622759502642248330000000235500001
219660807857766422759522642248335000000048400015
219660808054374422759532642248335000000048400015
219660808250982422759542642248335000000048400015
219660808447590422759552642248335000000048400015
221626888644198422759562642248339000000317600002
221626888860467222759572642248339000000317600002
221626889076736022759582642248339000000317600002
221626889293004822759592642248339000000317600002
223592969509273622759602642248343000000329500010
223592969745203222759612642248343000000329500010
223592969981132822759622642248343000000329500010
2242483210217062422759632642248343000000329500010
2262144010459545622759642642248347000000364500002
2262144010721689622759652642248347000000364500002
2262144010983833622759662642248347000000364500002
2262144011245977622759672642248347000000364500002
2288358411508121622759682642248351000000376200002
2288358411796480022759692642248351000000376200002
2288358412084838422759702642248351000000376200002
2288358412373196822759712642248351000000376200002
2314572812661555222759722642248355000000399300011
2314572812976128022759732642248355000000399300011
2314572813290700822759742642248355000000399300011
2327680013605273622759752642248355000000399300011
2347340813932953622759762642248359000000441400014
2347340814280294422759772642248359000000441400014
2347340814627635222759782642248359000000441400014
2353894414974976022759792642248359000000441400014
2380108815328870422759802642248363000000638800014
2380108815708979222759812642248363000000638800014
2380108816089088022759822642248363000000638800014
2393216016469196822759832642248363000000638800014
2419430416862412822759842642248367000000680600010
2419430417281843222759852642248367000000680600010
2419430417701273622759862642248367000000680600010
2432537618120704022759872642248367000000680600010
2458752018553241622759882642248371000000715500016
2458752019011993622759892642248371000000715500016
2458752019470745622759902642248371000000715500016
2478412819929497622759912642248371000000715500016
2511180820407910422759922642248375000000761600010
2511180820919091222759932642248375000000761600010
2511180821430272022759942642248375000000761600010
2511180821941452822759952642248375000000761600010
2557056022452633622759962642248379000000774200013
2557056023009689622759972642248379000000774200013
2557056023566745622759982642248379000000774200013
2576716824123801622759992642248379000000774200013
2616038424700518422760002642248383000001031800007
2616038425316556822760012642248383000001031800007
2616038425932595222760022642248383000001031800007
2622592026548633622760032642248383000001031800007
2675020827171225622760042642248387000001055200012
2675020827846246422760052642248387000001055200012
2675020828521267222760062642248387000001055200012
2694681629196288022760072642248387000001055200012
2747110429890969622760082642248391000001125300016
2747110430638080022760092642248391000001125300016
2747110431385190422760102642248391000001125300016
2747110432132300822760112642248391000001125300016
2819200032879411222760122642248395000001397300009
2819200033698611222760132642248395000001397300009
2819200034517811222760142642248395000001397300009
2832307235337011222760152642248395000001397300009
2904396836169318422760162642248399000001477000012
2904396837073715222760172642248399000001477000012
2904396837978112022760182642248399000001477000012
2904396838882508822760192642248399000001477000012
2989593639786905622760202642248403000001535500001
2989593640776499222760212642248403000001535500001
2989593641766092822760222642248403000001535500001
21009254442755686422760232642248403000001535500001
21094451243764940822760242642248407000001874800010
21094451244859392022760252642248407000001874800010
21094451245953843222760262642248407000001874800010
21094451247048294422760272642248407000001874800010
21199308848142745622760282642248411000001932800002
21199308849342054422760292642248411000001932800002
21199308850541363222760302642248411000001932800002
21218969651740672022760312642248411000001932800002
21323827252959641622760322642248415000002295700009
21323827254283468822760332642248415000002295700009
21323827255607296022760342642248415000002295700009
21323827256931123222760352642248415000002295700009
21454899258254950422760362642248419000002366000007
21454899259709849622760372642248419000002366000007
21454899261164748822760382642248419000002366000007
214614528626196480227547221282248419000002366000007
215990784640811008227547321282248423000002731400003
215990784656801792227547421282248423000002731400003
215990784672792576227547521282248423000002731400003
216121856688783360227547621282248423000002731400003
28781824704905216227547721282248427000002851100014
28781824713687040227547821282248427000002851100014
28781824722468864227547921282248427000002851100014
28781824731250688227548021282248427000002851100014
28781824740032512227548121282248427000002851100014
28781824748814336227548221282248427000002851100014
28781824757596160227548321282248427000002851100014
29043968766377984227548421282248427000002851100014
29699328775421952227548521282248435000001559100007
29699328785121280227548621282248435000001559100007
29699328794820608227548721282248435000001559100007
29699328804519936227548821282248435000001559100007
29699328814219264227548921282248435000001559100007
29699328823918592227549021282248435000001559100007
29699328833617920227549121282248435000001559100007
29699328843317248227549221282248435000001559100007
210616832853016576227549321282248443000001707200009
210616832863633408227549421282248443000001707200009
210616832874250240227549521282248443000001707200009
210616832884867072227549621282248443000001707200009
210616832895483904227549721282248443000001707200009
210616832906100736227549821282248443000001707200009
210616832916717568227549921282248443000001707200009
211010048927334400227550021282248443000001707200009
211730944938344448227550121282248451000001697700006
211730944950075392227550221282248451000001697700006
211730944961806336227550321282248451000001697700006
211730944973537280227550421282248451000001697700006
211730944985268224227550521282248451000001697700006
211730944996999168227550621282248451000001697700006
2117309441008730112227550721282248451000001697700006
2117309441020461056227550821282248451000001697700006
2128450561032192000227550921282248459000002121200013
2128450561045037056227551021282248459000002121200013
2128450561057882112227551121282248459000002121200013
2128450561070727168227551221282248459000002121200013
2128450561083572224227551321282248459000002121200013
2128450561096417280227551421282248459000002121200013
2128450561109262336227551521282248459000002121200013
2133038081122107392227551621282248459000002121200013
2141557761135411200227551721282248467000002414300006
2141557761149566976227551821282248467000002414300006
2141557761163722752227551921282248467000002414300006
2141557761177878528227552021282248467000002414300006
2141557761192034304227552121282248467000002414300006
2141557761206190080227552221282248467000002414300006
2141557761220345856227552321282248467000002414300006
2144834561234501632227552421282248467000002414300006
2155975681248985088227552521282248475000002627400006
2155975681264582656227552621282248475000002627400006
2155975681280180224227552721282248475000002627400006
2155975681295777792227552821282248475000002627400006
2155975681311375360227552921282248475000002627400006
2155975681326972928227553021282248475000002627400006
2155975681342570496227553121282248475000002627400006
2157286401358168064227553221282248475000002627400006
2171704321373896704227553321282248483000002594500012
2171704321391067136227553421282248483000002594500012
2171704321408237568227553521282248483000002594500012
2171704321425408000227553621282248483000002594500012
2171704321442578432227553721282248483000002594500012
2171704321459748864227553821282248483000002594500012
2171704321476919296227553921282248483000002594500012
2172359681494089728227554021282248483000002594500012
2188743681511325696227554121282248491000002597000003
2188743681530200064227554221282248491000002597000003
2188743681549074432227554321282248491000002597000003
2188743681567948800227554421282248491000002597000003
2188743681586823168227554521282248491000002597000003
2188743681605697536227554621282248491000002597000003
2188743681624571904227554721282248491000002597000003
2190054401643446272227554821282248491000002597000003
2207749121662451712227554921282248499000002605300014
2207749121683226624227555021282248499000002605300014
2207749121704001536227555121282248499000002605300014
2207749121724776448227555221282248499000002605300014
2207749121745551360227555321282248499000002605300014
2207749121766326272227555421282248499000002605300014
2207749121787101184227555521282248499000002605300014
2208404481807876096227555621282248499000002605300014
2228065281828716544227555721282248507000003124100772
2228065281851523072227555821282248507000003124100772
2228065281874329600227555921282248507000003124100772
2228065281897136128227556021282248507000003124100772
2228065281919942656227556121282248507000003124100772
2228065281942749184227556221282248507000003124100772
2228065281965555712227556321282248507000003124100772
2232652801988362240227556421282248507000003124100772
2251002882011627520227556521282248515000004448700011
2251002882036727808227556621282248515000004448700011
2251002882061828096227556721282248515000004448700011
2251002882086928384227556821282248515000004448700011
2251002882112028672227556921282248515000004448700011
2251002882137128960227557021282248515000004448700011
2251002882162229248227557121282248515000004448700011
2254935042187329536227557221282248515000004448700011
2276561922212823040227557321282248523000004930400006
2276561922240479232227557421282248523000004930400006
2276561922268135424227557521282248523000004930400006
2276561922295791616227557621282248523000004930400006
2276561922323447808227557721282248523000004930400006
2276561922351104000227557821282248523000004930400006
2276561922378760192227557921282248523000004930400006
2277217282406416384227558021282248523000004930400006
2304087042434138112227558121282248531000004997400006
2304087042464546816227558221282248531000004997400006
2304087042494955520227558321282248531000004997400006
2304087042525364224227558421282248531000004997400006
2304087042555772928227558521282248531000004997400006
2304087042586181632227558621282248531000004997400006
2304087042616590336227558721282248531000004997400006
2306053122646999040227558821282248531000004997400006
2334233602677604352227558921282248539000005480500006
2334233602711027712227559021282248539000005480500006
2334233602744451072227559121282248539000005480500006
2334233602777874432227559221282248539000005480500006
2334233602811297792227559321282248539000005480500006
2334233602844721152227559421282248539000005480500006
2334233602878144512227559521282248539000005480500006
2338165762911567872227559621282248539000005480500006
2367656962945384448227559721282248547000005053200013
2367656962982150144227559821282248547000005053200013
2367656963018915840227559921282248547000005053200013
2367656963055681536227560021282248547000005053200013
2367656963092447232227560121282248547000005053200013
2367656963129212928227560221282248547000005053200013
2367656963165978624227560321282248547000005053200013
2372244483202744320227560421282248547000005053200013
2405012483239968768227560521282248555000006400000003
2405012483280470016227560621282248555000006400000003
2405012483320971264227560721282248555000006400000003
2405012483361472512227560821282248555000006400000003
2405012483401973760227560921282248555000006400000003
2405012483442475008227561021282248555000006400000003
2405012483482976256227561121282248555000006400000003
2405012483523477504227561221282248555000006400000003
2444989443563978752227561321282248563000007836000014
2444989443608477696227561421282248563000007836000014
2444989443652976640227561521282248563000007836000014
2444989443697475584227561621282248563000007836000014
2444989443741974528227561721282248563000007836000014
2444989443786473472227561821282248563000007836000014
2444989443830972416227561921282248563000007836000014
2449576963875471360227562021282248563000007836000014
2489553923920429056227562121282248571000008343600002
2489553923969384448227562221282248571000008343600002
2489553924018339840227562321282248571000008343600002
2489553924067295232227562421282248571000008343600002
2489553924116250624227562521282248571000008343600002
2489553924165206016227562621282248571000008343600002
2489553924214161408227562721282248571000008343600002
2493486084263116800227562821282248571000008343600002
2538705924312465408227562921282248579000008999800013
2538705924366336000227563021282248579000008999800013
2538705924420206592227563121282248579000008999800013
2538705924474077184227563221282248579000008999800013
2538705924527947776227563321282248579000008999800013
2538705924581818368227563421282248579000008999800013
2538705924635688960227563521282248579000008999800013
2541982724689559552227563621282248579000008999800013
2592445444743757824227563721282248587000009953500001
2592445444803002368227563821282248587000009953500001
2592445444862246912227563921282248587000009953500001
2592445444921491456227564021282248587000009953500001
2592445444980736000227564121282248587000009953500001
2592445445039980544227564221282248587000009953500001
2592445445099225088227564321282248587000009953500001
2597032965158469632227564421282248587000009953500001
2652083205218172928227564521282248595000011394100015
2652083205283381248227564621282248595000011394100015
2652083205348589568227564721282248595000011394100015
2652083205413797888227564821282248595000011394100015
2652083205479006208227564921282248595000011394100015
2652083205544214528227565021282248595000011394100015
2652083205609422848227565121282248595000011394100015
2654049285674631168227565221282248595000011394100015
2716963845740036096227565321282248603000011199600009
2716963845811732480227565421282248603000011199600009
2716963845883428864227565521282248603000011199600009
2716963845955125248227565621282248603000011199600009
2716963846026821632227565721282248603000011199600009
2716963846098518016227565821282248603000011199600009
2716963846170214400227565921282248603000011199600009
2721551366241910784227566021282248603000011199600009
2789053446314065920227566121282248611000013533000009
2789053446392971264227566221282248611000013533000009
2789053446471876608227566321282248611000013533000009
2789053446550781952227566421282248611000013533000009
2789053446629687296227566521282248611000013533000009
2789053446708592640227566621282248611000013533000009
2789053446787497984227566721282248611000013533000009
2791019526866403328227566821282248611000013533000009
2867696646945505280227566921282248619000014513200010
2867696647032274944227567021282248619000014513200010
2867696647119044608227567121282248619000014513200010
2867696647205814272227567221282248619000014513200010
2867696647292583936227567321282248619000014513200010
2867696647379353600227567421282248619000014513200010
2867696647466123264227567521282248619000014513200010
2871628807552892928227567621282248619000014513200010
2954859527640055808227567721282248627000014845900013
2954859527735541760227567821282248627000014845900013
2954859527831027712227567921282248627000014845900013
2954859527926513664227568021282248627000014845900013
2954859528021999616227568121282248627000014845900013
2954859528117485568227568221282248627000014845900013
2954859528212971520227568321282248627000014845900013
2956170248308457472227568421282248627000014845900013
21050542088404074496227568521282248635000017770500008
21050542088509128704227568621282248635000017770500008
21050542088614182912227568721282248635000017770500008
21050542088719237120227568821282248635000017770500008
21050542088824291328227568921282248635000017770500008
21050542088929345536227569021282248635000017770500008
21050542089034399744227569121282248635000017770500008
21050542089139453952227569221282248635000017770500008
21155399689244508160227569321282248643000020290100009
21155399689360048128227569421282248643000020290100009
21155399689475588096227569521282248643000020290100009
21155399689591128064227569621282248643000020290100009
21155399689706668032227569721282248643000020290100009
21155399689822208000227569821282248643000020290100009
21155399689937747968227569921282248643000020290100009
211567104010053287936227570021282248643000020290100009
212707430410168958976227570121282248651000021580900002
212707430410296033280227570221282248651000021580900002
212707430410423107584227570321282248651000021580900002
212707430410550181888227570421282248651000021580900002
212707430410677256192227570521282248651000021580900002
212707430410804330496227570621282248651000021580900002
212707430410931404800227570721282248651000021580900002
212740198411058479104227570821282248651000021580900002
26986137611185881088227570921282248659000024800000014
26986137611255742464227571021282248659000024800000014
26986137611325603840227571121282248659000024800000014
26986137611395465216227571221282248659000024800000014
26986137611465326592227571321282248659000024800000014
26986137611535187968227571421282248659000024800000014
26986137611605049344227571521282248659000024800000014
26986137611674910720227571621282248659000024800000014
26986137611744772096227571721282248659000024800000014
26986137611814633472227571821282248659000024800000014
26986137611884494848227571921282248659000024800000014
26986137611954356224227572021282248659000024800000014
26986137612024217600227572121282248659000024800000014
26986137612094078976227572221282248659000024800000014
26986137612163940352227572321282248659000024800000014
27071334412233801728227572421282248659000024800000014
27687372812304515072227572521282248675000013698300017
27687372812381388800227572621282248675000013698300017
27687372812458262528227572721282248675000013698300017
27687372812535136256227572821282248675000013698300017
27687372812612009984227572921282248675000013698300017
27687372812688883712227573021282248675000013698300017
27687372812765757440227573121282248675000013698300017
27687372812842631168227582821282248675000013698300017
27687372812919504896227582921282248675000013698300017
27687372812996378624227583021282248675000013698300017
27687372813073252352227583121282248675000013698300017
27687372813150126080227583221282248675000013698300017
27687372813226999808227583321282248675000013698300017
27687372813303873536227583421282248675000013698300017
27687372813380747264227583521282248675000013698300017
27739801613457620992227583621282248675000013698300017
2845414401353501900822757322642249153000014967400104
2845414401361956044822757332642249153000014967400104
2845414401370410188822757342642249153000014967400104
2845414401378864332822757352642249153000014967400104
2845414401387318476822757362642249153000014967400104
2845414401395772620822757372642249153000014967400104
2845414401404226764822757382642249153000014967400104
2845414401412680908822757392642249153000014967400104
2845414401421135052822757402642249153000014967400104
2845414401429589196822757412642249153000014967400104
2845414401438043340822757422642249153000014967400104
2845414401446497484822757432642249153000014967400104
2845414401454951628822757442642249153000014967400104
2845414401463405772822757452642249153000014967400104
2845414401471859916822757462642249153000014967400104
2853934081480314060822757472642249153000014967400104
2929955841488853401622757482642249169000016639900019
2929955841498152960022757492642249169000016639900019
2929955841507452518422757502642249169000016639900019
2929955841516752076822757512642249169000016639900019
2929955841526051635222757522642249169000016639900019
2929955841535351193622757532642249169000016639900019
2929955841544650752022757542642249169000016639900019
2929955841553950310422757552642249169000016639900019
2929955841563249868822757562642249169000016639900019
2929955841572549427222757572642249169000016639900019
2929955841581848985622757582642249169000016639900019
2929955841591148544022757592642249169000016639900019
2929955841600448102422757602642249169000016639900019
2929955841609747660822757612642249169000016639900019
2929955841619047219222757622642249169000016639900019
2939130881628346777622757632642249169000016639900019
21023016961637738086422757642642249185000018302200026
21023016961647968256022757652642249185000018302200026
21023016961658198425622757662642249185000018302200026
21023016961668428595222757672642249185000018302200026
21023016961678658764822757682642249185000018302200026
21023016961688888934422757692642249185000018302200026
21023016961699119104022757702642249185000018302200026
21023016961709349273622757712642249185000018302200026
21023016961719579443222757722642249185000018302200026
21023016961729809612822757732642249185000018302200026
21023016961740039782422757742642249185000018302200026
21023016961750269952022757752642249185000018302200026
21023016961760500121622757762642249185000018302200026
21023016961770730291222757772642249185000018302200026
21023016961780960460822757782642249185000018302200026
21032192001791190630422757792642249185000018302200026
21125908481801512550422757802642249201000020114000104
21125908481812771635222757812642249201000020114000104
21125908481824030720022757822642249201000020114000104
21125908481835289804822757832642249201000020114000104
21125908481846548889622757842642249201000020114000104
21125908481857807974422757852642249201000020114000104
21125908481869067059222757862642249201000020114000104
21125908481880326144022757872642249201000020114000104
21125908481891585228822757882642249201000020114000104
21125908481902844313622757892642249201000020114000104
21125908481914103398422757902642249201000020114000104
21125908481925362483222757912642249201000020114000104
21125908481936621568022757922642249201000020114000104
21125908481947880652822757932642249201000020114000104
21125908481959139737622757942642249201000020114000104
21126563841970398822422757952642249201000020114000104
21237975041981664460822757962642249217000021958000104
21237975041994044211222757972642249217000021958000104
21237975042006423961622757982642249217000021958000104
21237975042018803712022757992642249217000021958000104
21237975042031183462422758002642249217000021958000104
21237975042043563212822758012642249217000021958000104
21237975042055942963222758022642249217000021958000104
21237975042068322713622758032642249217000021958000104
21237975042080702464022758042642249217000021958000104
21237975042093082214422758052642249217000021958000104
21237975042105461964822758062642249217000021958000104
21237975042117841715222758072642249217000021958000104
21237975042130221465622758082642249217000021958000104
21237975042142601216022758092642249217000021958000104
21237975042154980966422758102642249217000021958000104
21247150082167360716822758112642249217000021958000104
21361838082179832217622758122642249233000024319000062
21361838082193450598422758132642249233000024319000062
21361838082207068979222758142642249233000024319000062
21361838082220687360022758152642249233000024319000062
21361838082234305740822758162642249233000024319000062
21361838082247924121622758172642249233000024319000062
21361838082261542502422758182642249233000024319000062
21361838082275160883222758192642249233000024319000062
21361838082288779264022758202642249233000024319000062
21361838082302397644822758212642249233000024319000062
21361838082316016025622758222642249233000024319000062
21361838082329634406422758232642249233000024319000062
21361838082343252787222758242642249233000024319000062
21361838082356871168022758252642249233000024319000062
21361838082370489548822758262642249233000024319000062
21371013122384107929622758272642249233000024319000062
21498152962397818060822754562642257965000002802200062
21498152962412799590422754572642257965000002802200062
21498152962427781120022754582642257965000002802200062
21498152962442762649622754592642257965000002802200062
21498152962457744179222754602642257965000002802200062
21498152962472725708822754612642257965000002802200062
21498152962487707238422754622642257965000002802200062
21498152962502688768022754632642257965000002802200062
21498152962517670297622754642642257965000002802200062
21498152962532651827222754652642257965000002802200062
21498152962547633356822754662642257965000002802200062
21498152962562614886422754672642257965000002802200062
21498152962577596416022754682642257965000002802200062
21498152962592577945622754692642257965000002802200062
21498152962607559475222754702642257965000002802200062
21506017282622541004822754712642257965000002802200062
21648230402637601177622760392642276038000000295800002
21648230402654083481622760402642276038000000295800002
21648230402670565785622760412642276038000000295800002
21648230402687048089622760422642276038000000295800002
21648230402703530393622760432642276038000000295800002
21648230402720012697622760442642276038000000295800002
21648230402736495001622760452642276038000000295800002
21648230402752977305622760462642276038000000295800002
21648230402769459609622760472642276038000000295800002
21648230402785941913622760482642276038000000295800002
21648230402802424217622760492642276038000000295800002
21648230402818906521622760502642276038000000295800002
21648230402835388825622760512642276038000000295800002
21648230402851871129622760522642276038000000295800002
21648230402868353433622760532642276038000000295800002
21652817922884835737622760542642276038000000295800002
21813381122901363916822760552642276054000032177800009
21813381122919497728022760562642276054000032177800009
21813381122937631539222760572642276054000032177800009
21813381122955765350422760582642276054000032177800009
21813381122973899161622760592642276054000032177800009
21813381122992032972822760602642276054000032177800009
21813381123010166784022760612642276054000032177800009
21813381123028300595222760622642276054000032177800009
21813381123046434406422760632642276054000032177800009
21813381123064568217622760642642276054000032177800009
21813381123082702028822760652642276054000032177800009
21813381123100835840022760662642276054000032177800009
21813381123118969651222760672642276054000032177800009
21813381123137103462422760682642276054000032177800009
21813381123155237273622760692642276054000032177800009
21813381123173371084822760702642276054000032177800009
21994260483191504896022760712642276070000034722400004
21994260483211447500822760722642276070000034722400004
21994260483231390105622760732642276070000034722400004
21994260483251332710422760742642276070000034722400004
21994260483271275315222760752642276070000034722400004
21994260483291217920022760762642276070000034722400004
21994260483311160524822760772642276070000034722400004
21994260483331103129622760782642276070000034722400004
21994260483351045734422760792642276070000034722400004
21994260483370988339222760802642276070000034722400004
21994260483390930944022760812642276070000034722400004
21994260483410873548822760822642276070000034722400004
21994260483430816153622760832642276070000034722400004
21994260483450758758422760842642276070000034722400004
21994260483470701363222760852642276070000034722400004
22001469443490643968022760862642276070000034722400004
22194145283510658662422760872642276086000038704000679
22194145283532600115222760882642276086000038704000679
22194145283554541568022760892642276086000038704000679
22194145283576483020822760902642276086000038704000679
22194145283598424473622760912642276086000038704000679
22194145283620365926422760922642276086000038704000679
22194145283642307379222760932642276086000038704000679
22194145283664248832022760942642276086000038704000679
22194145283686190284822760952642276086000038704000679
22194145283708131737622760962642276086000038704000679
22194145283730073190422760972642276086000038704000679
22194145283752014643222760982642276086000038704000679
22194145283773956096022760992642276086000038704000679
22194145283795897548822761002642276086000038704000679
22194145283817839001622761012642276086000038704000679
22194800643839780454422761022642276086000038704000679
22413035523861728460822761032642276102000042474100009
22413035523885858816022761042642276102000042474100009
22413035523909989171222761052642276102000042474100009
22413035523934119526422761062642276102000042474100009
22413035523958249881622761072642276102000042474100009
22413035523982380236822761082642276102000042474100009
22413035524006510592022761092642276102000042474100009
22413035524030640947222761102642276102000042474100009
22413035524054771302422761112642276102000042474100009
22413035524078901657622761122642276102000042474100009
22413035524103032012822761132642276102000042474100009
22413035524127162368022761142642276102000042474100009
22413035524151292723222761152642276102000042474100009
22413035524175423078422761162642276102000042474100009
22413035524199553433622761172642276102000042474100009
22422210564223683788822761182642276102000042474100009
22654863364247905894422761192642276118000047203200001
22654863364274454528022761202642276118000047203200001
22654863364301003161622761212642276118000047203200001
22654863364327551795222761222642276118000047203200001
22654863364354100428822761232642276118000047203200001
22654863364380649062422761242642276118000047203200001
22654863364407197696022761252642276118000047203200001
22654863364433746329622761262642276118000047203200001
22654863364460294963222761272642276118000047203200001
22654863364486843596822761282642276118000047203200001
22654863364513392230422761292642276118000047203200001
22654863364539940864022761302642276118000047203200001
22654863364566489497622761312642276118000047203200001
22654863364593038131222761322642276118000047203200001
22654863364619586764822761332642276118000047203200001
22656174084646135398422761342642276118000047203200001
22920284164672697139222761352642276134000051368000008
22920284164701899980822761362642276134000051368000008
22920284164731102822422761372642276134000051368000008
22920284164760305664022761382642276134000051368000008
22920284164789508505622761392642276134000051368000008
22920284164818711347222761402642276134000051368000008
22920284164847914188822761412642276134000051368000008
22920284164877117030422761422642276134000051368000008
22920284164906319872022761432642276134000051368000008
22920284164935522713622761442642276134000051368000008
22920284164964725555222761452642276134000051368000008
22920284164993928396822761462642276134000051368000008
22920284165023131238422761472642276134000051368000008
22920284165052334080022761482642276134000051368000008
22920284165081536921622761492642276134000051368000008
22922905605110739763222761502642276134000051368000008
23211919365139968819222761512642276150000055771100009
23211919365172088012822761522642276150000055771100009
23211919365204207206422761532642276150000055771100009
23211919365236326400022761542642276150000055771100009
23211919365268445593622761552642276150000055771100009
23211919365300564787222761562642276150000055771100009
23211919365332683980822761572642276150000055771100009
23211919365364803174422761582642276150000055771100009
23211919365396922368022761592642276150000055771100009
23211919365429041561622761602642276150000055771100009
23211919365461160755222761612642276150000055771100009
23211919365493279948822761622642276150000055771100009
23211919365525399142422761632642276150000055771100009
23211919365557518336022761642642276150000055771100009
23211919365589637529622761652642276150000055771100009
23221094405621756723222761662642276150000055771100009
23533701125653967667222761672642276166000062872100001
23555328005689304678422761682642276167000068975400002
2355532800572485795840002276167000068975400002
2355532800576041123840002276167000068975400002
2355532800579596451840002276167000068975400002
2355532800583151779840002276167000068975400002
2355532800586707107840002276167000068975400002
2355532800590262435840002276167000068975400002
2355532800593817763840002276167000068975400002
2355532800597373091840002276167000068975400002
2355532800600928419840002276167000068975400002
2355532800604483747840002276167000068975400002
2355532800608039075840002276167000068975400002
2355532800611594403840002276167000068975400002
2355532800615149731840002276167000068975400002
2355532800618705059840002276167000068975400002
2356319232622260387840002276167000068975400002
March 17, 2011 at 8:15 am
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
March 17, 2011 at 8:37 am
Hey Gila,
Tx for the quick response. Amongst the zillion hit that my web searches retuned, I missed that one. The results are :
namerecovery_model_desclog_reuse_wait_desc
LPFULLREPLICATION
Which would be very peculiar since :
- I got no replication on that db (but yes on other db on same server)
- I have tried making tlog backup/changing rec model with no activity in the server activity window for that db
- I see no sa connection for repl-logreader-0-LP
Based on those findings how can I remove the active vlfs?
It would be no use asking why would replication be preventing log truncation (I'll find out why after, now is more for the how to fix it ;-)),
Do you think it could be "dirty vlf" ?
I am to the point where I am seriously thinking of shutting down the SQL services and restarting them. Imo this should force any "ghost" replication or other process to release any "ghost handles" handles on the db. As you can see I have been on this problem for the last 2 days and I am stomped, now thinking of ghosts.
Btw I greatly appreciate the help/suggestions.
March 17, 2011 at 8:47 am
Did you read the portion of that article that covered the replication wait?
No such thing as a 'dirty' vlf. SQL thinks it has replication in place, hence the log cannot be reused.
Are you using any form of replication? Do you have CDC enabled? Don't assume, double check. Was the DB perhaps restored from a database that is replicated?
What does DBCC OPENTRAN return when run in that database?
A shut down will do one thing and one thing only and that is to make the DBs unavailable for a period of time. It will have absolutely no effect on 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
March 17, 2011 at 9:44 am
Hello agan,
yes I did read the replication subsection. And I just re-read it 2 more times, in case I missed an important point. I was actually following the link inside that paragraph to a SQL 2005 snapshot replication bug. I am looking down that path, thanks to you, for something I may have missed.
Dirty vlfs. I have never heard of this before, and I made a parallel to dirty mem pages. Tx for confirming this does not apply. (Although dirty vlf web searches results in some hits). Disregarding those.
Any for of replication ? Yes but not on this particular db. All other db do not show any problems, with or without replication.
Do you have CDC ? No.
Don't assume. Yep, been screwed too often in my newbie years, double and triple checking is amongst my habits now. Along with not always trusting what the interfaces says 😉
Was the db restored from a repl enabled db? Yes, that could very well be. It was originally based on another db onto which we added functionnalities. Although it's been like that for years and I never had this problem before.
DBCC opentran results :
Transaction information for database LP.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (2275456:237794:1)
DBCC execution completed.
Shut down. Ya I did not think it would do anything else then make sure no connections to the db was active.
March 17, 2011 at 11:40 am
SQL sure thinks it's replicated. If you are 100% sure it's not, try the following.
Create a transactional replication publication.
Publish a single table (a small one)
Stop the log reader
Run sp_repldone in that database
Drop the publication
Restart the log reader (iirc there's only one for an instance, not one for a DB. If there's one per DB, make sure that the log readers for other DBs are still running)
Check DBCC OpenTran again and ensure that the reference to distributed and non-distributed LSNs are gone
checkpoint
Shrink the log to a reasonable 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
March 18, 2011 at 8:51 am
Hello again all,
Did Gail's suggestion in the middle of the night. Worked perfectly. All active statuses in log file have gone, DBCC Loginfo is clean, db file, log files and backups are back to normal.
A big, big thank you to Gail.
March 18, 2011 at 8:58 am
GilaMonster (3/17/2011)
SQL sure thinks it's replicated. If you are 100% sure it's not, try the following.Create a transactional replication publication.
Publish a single table (a small one)
Stop the log reader
Run sp_repldone in that database
Drop the publication
Restart the log reader (iirc there's only one for an instance, not one for a DB. If there's one per DB, make sure that the log readers for other DBs are still running)
Check DBCC OpenTran again and ensure that the reference to distributed and non-distributed LSNs are gone
checkpoint
Shrink the log to a reasonable size.
Brilliant suggestions Gail, I have saved this link for my reference.
M&M
March 18, 2011 at 9:04 am
amajor (3/18/2011)
Worked perfectly. All active statuses in log file have gone, DBCC Loginfo is clean, db file, log files and backups are back to normal.
Great. Now monitor it for a few days, make sure it stays that way.
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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply