June 29, 2011 at 3:10 pm
I've done a ton of seaching, but I haven't found anyone with the same problem that I have. About two months ago, I had to do a repair on a database we have on a 2005 cluster which seemed to work well. This cluster has an old local MSA which may be failing and probably caused the corruption. Anyway, that was fixed.
Regardless, shortly after the repair the transaction log started growing to where it's nearly the same size as the database (22 Gigs currently). When I run a report of the log contents, it shows 99.1% of the space as unused, but the log file continues to grow. In addition, my backup sizes have increased. The actual database size is about 28 Gigs, but my nightly full backups are about 50 gigs. I've also tried several things but am unable to shrink the log file even though only a small percentage of it is being used.
Does anyone have any ideas on why both the backup and log files are so large? I'm at the point where between the log files and the growing backups, I'm running out of disk space. Thanks in advance.
My hovercraft is full of eels.
June 29, 2011 at 3:48 pm
Just a 'is it plugged in' kind of question first:
The actual database size is about 28 Gigs, but my nightly full backups are about 50 gigs.
What recovery mode is the Database in (simple, bulk, or full) and have you performed transaction log backups as well, or just full backups of the db?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 29, 2011 at 4:04 pm
Will you post the results of this query?
;
with dbsize as
(select db_id() as DatabaseID,df.name, df.size
from sys.database_files df
)
Select df.DatabaseID,df.name,sum(convert(float,df.size))*8/1024 as FileSizeMB
From dbsize df
group by df.DatabaseID,df.name with rollup
;
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
June 30, 2011 at 6:43 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
June 30, 2011 at 3:15 pm
Thank you for the replies.
Craig, recovery mode is full, the logs are backed up hourly and the Db is backed up nightly. SQLRNNR, this is interesting. The results of your query are:
20CWNCM28163.1875
20CWNCM_log1220.875
20NULL29384.0625
NULLNULL29384.0625
I don't see this from the EM view. What's up with the Null entries? Gail, I've reviewed that article and many others, but don't seem to be able to figure this out.
This is a production database that manages some critical network configurations. I'm half tempted to try the detach, attach single file trick to re-create the log after a full backup but I'd still like to know what exactly is going on. Thank again.
My hovercraft is full of eels.
June 30, 2011 at 3:32 pm
What's the log_reuse_wait_desc?
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
June 30, 2011 at 3:40 pm
Results of a log_reuse_wait_desc query:
CWNCMFULLNOTHING
My hovercraft is full of eels.
June 30, 2011 at 3:45 pm
sswords (6/30/2011)
My hovercraft is full of eels.
Apparently so is your log file. Hrm.
Out of curiousity, can you pull the VLF information for us?
DBCC LOGINFO('MyDB')
I'm not sure it'll help but it might point out some inconsistencies.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 30, 2011 at 3:48 pm
DBCC SQLPERF(LogSpace)?
Can you put a job in that monitor both (log_reuse_wait and SQLPerf(LogSpace) regularly (say every 30 min, or as often as you take log backups)?
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
June 30, 2011 at 3:51 pm
Something strange is going on and I may have disk corruption issues. But it looks like I stumbled on something that fixed it finally.
The actual log file is back down to around 1 gigabyte, and the last full backup file was about 27 gigs in size which closely matches the data file size. The previous full backup was over 50 gigs, and due to disk contraints I could only keep one day's worth of backups. Now I can change it back to 2 or 3. We're supposed to be getting a new SAN-attached cluster to replace this rig soon and it' can't be soon enough for me. That 5 year old MSA that's attached to it scares me to death.
My hovercraft is full of eels.
June 30, 2011 at 3:59 pm
DBCC Log Info Query Results (looks funky to me):
22539528192304842640
2253952262144304852640
227033651609629995012835000000013600110
226214478643229990064145000000003500003
2262144104857629991064155000000006600075
2262144131072029994064746000000004900048
226214415728642999201281088000000037000056
226214418350082999301281089000000036700025
226214420971522999601282164000000018400043
226214423592962999701282165000000019600012
226214426214402999801282166000000019700006
232768028835842999901282167000000018500053
232768032112643000001282168000000032000025
239321635389443000101282169000000032700002
239321639321603000201282170000000044300051
245875243253763000301282171000000045100009
225395247841283000401282172000000057000056
227033650380803000501282172000000057000056
225395253084163000601282174000000020200055
227033655623683000701282174000000020200055
225395258327043000801282176000000021000010
233587260866563000901282176000000021000010
225395264225283001001282178000000034100012
240140866764803001101282178000000034100012
225395270778883001201282180000000046500016
246694473318403001301282180000000046500016
225395277987843001401282182000000059000034
225395280527363001501282182000000059000034
227852883066883001601282182000000059000034
225395285852163001701282185000000023000007
225395288391683001801282185000000023000007
234406490931203001901282185000000023000007
225395294371843002001282188000000035500015
225395296911363002101282188000000035500015
247513699450883002201282188000000035500015
2253952104202243002301282191000000059800002
2253952106741763002401282191000000059800002
2253952109281283002501282191000000059800002
2286720111820803002601282191000000059800002
2262144114688003002701282195000000051900002
2262144117309443002801282195000000051900002
2262144119930883002901282195000000051900002
2393216122552323003001282195000000051900002
2327680126484483003101282199000000067400063
2327680129761283003201282199000000067400063
2327680133038083003301282199000000067400063
2327680136314883003401282199000000067400063
2327680139591683003501282203000000053300008
2327680142868483003601282203000000053300008
2327680146145283003701282203000000053300008
2458752149422083003801282203000000053300008
2393216154009603003901282207000000078900011
2393216157941763004001282207000000078900011
2393216161873923004101282207000000078900011
2393216165806083004201282207000000078900011
2393216169738243004301282211000000065200012
2393216173670403004401282211000000065200012
2393216177602563004501282211000000065200012
2524288181534723004601282211000000065200012
2458752186777603004701282215000000090400023
2458752191365123004801282215000000090400023
2458752195952643004901282215000000090400023
2524288200540163005001282215000000090400023
2524288205783043005101282219000000091100003
2524288211025923005201282219000000091100003
2524288216268803005301282219000000091100003
2524288221511683005401282219000000091100003
2524288226754563005501282223000000098600019
2524288231997443005601282223000000098600019
2524288237240323005701282223000000098600019
2720896242483203005801282223000000098600019
2589824249692163005901282227000000134800011
2589824255590403006001282227000000134800011
2589824261488643006101282227000000134800011
2720896267386883006201282227000000134800011
2655360274595843006301282231000000134300028
2655360281149443006401282231000000134300028
2655360287703043006501282231000000134300028
2786432294256643006601282231000000134300028
2720896302120963006701282235000000145500028
2720896309329923006801282235000000145500028
2720896316538883006901282235000000145500028
2851968323747843007001282235000000145500028
2786432332267523007101282239000000161300045
2786432340131843007201282239000000161300045
2786432347996163007301282239000000161300045
2983040355860483007401282239000000161300045
2917504365690883007501282243000000185300031
2917504374865923007601282243000000185300031
2917504384040963007701282243000000185300031
2917504393216003007801282243000000185300031
2983040402391043007901282247000000169800013
2983040412221443008001282247000000169800013
2983040422051843008101282247000000169800013
21114112431882243008201282247000000169800013
21114112443023363008301282251000000213900017
21114112454164483008401282251000000213900017
21114112465305603008501282251000000213900017
21114112476446723008601282251000000213900017
21179648487587843008701282255000000208300010
21179648499384323008801282255000000208300010
21179648511180803008901282255000000208300010
21376256522977283009001282255000000208300010
21310720536739843009101282259000000249100010
21310720549847043009201282259000000249100010
21310720562954243009301282259000000249100010
21441792576061443009401282259000000249100010
21441792590479363009501282263000000270000012
21441792604897283009601282263000000270000012
21441792619315203009701282263000000270000012
21572864633733123009801282263000000270000012
21572864649461763009901282267000000279400005
21572864665190403010001282267000000279400005
21572864680919043010101282267000000279400005
21769472696647683010201282267000000279400005
21769472714342403010301282271000000320900020
21769472732037123010401282271000000320900020
21769472749731843010501282271000000320900020
21835008767426563010601282271000000320900020
21966080785776643010701282275000000349800086
21966080805437443010801282275000000349800086
21966080825098243010901282275000000349800086
21966080844759043011001282275000000349800086
22162688864419843011101282279000000370800008
22162688886046723011201282279000000370800008
22162688907673603011301282279000000370800008
22162688929300483011401282279000000370800008
22359296950927363011501282283000000416800028
22359296974520323011601282283000000416800028
22359296998113283011701282283000000416800028
224248321021706243011801282283000000416800028
226214401045954563011901282287000000467600037
226214401072168963012001282287000000467600037
226214401098383363012101282287000000467600037
226214401124597763012201282287000000467600037
228835841150812163012301282291000000505100017
228835841179648003012401282291000000505100017
228835841208483843012501282291000000505100017
228835841237319683012601282291000000505100017
231457281266155523012701282295000000559500055
231457281297612803012801282295000000559500055
231457281329070083012901282295000000559500055
232768001360527363013001282295000000559500055
234734081393295363013101282300000000015400054
234734081428029443013201282300000000015400054
234734081462763523013301282300000000015400054
235389441497497603013401282300000000015400054
238010881532887043013501282304000000658000005
238010881570897923013601282304000000658000005
238010881608908803013701282304000000658000005
239321601646919683013801282304000000658000005
241943041686241283013901282308000000725500002
241943041728184323014001282308000000725500002
241943041770127363014101282308000000725500002
243253761812070403014201282308000000725500002
245875201855324163014301282312000000802800041
245875201901199363014401282312000000802800041
245875201947074563014501282312000000802800041
247841281992949763014601282312000000802800041
251118082040791043014701282316000000894800012
251118082091909123014801282316000000894800012
251118082143027203014901282316000000894800012
251118082194145283015001282316000000894800012
255705602245263363015101282320000000953600064
255705602300968963015201282320000000953600064
255705602356674563015301282320000000953600064
257671682412380163015401282320000000953600064
261603842470051843015501282324000001085800009
261603842531655683015601282324000001085800009
261603842593259523015701282324000001085800009
262259202654863363015801282324000001085800009
267502082717122563015901282328000001175700007
267502082784624643016001282328000001175700007
267502082852126723016101282328000001175700007
269468162919628803016201282328000001175700007
274711042989096963016301282332000001296700003
274711043063808003016401282332000001296700003
274711043138519043016501282332000001296700003
274711043213230083016601282332000001296700003
281920003287941123016701282336000001411100026
281920003369861123016801282336000001411100026
281920003451781123016901282336000001411100026
283230723533701123017001282336000001411100026
290439683616931843017101282340000001578400011
290439683707371523017201282340000001578400011
290439683797811203017301282340000001578400011
290439683888250883017401282340000001578400011
298959363978690563017501282344000001724300009
298959364077649923017601282344000001724300009
298959364176609283017701282344000001724300009
2100925444275568643017801282344000001724300009
2109445124376494083017901282348000001904900051
2109445124485939203018001282348000001904900051
2109445124595384323018101282348000001904900051
2109445124704829443018201282348000001904900051
2119930884814274563018301282352000002084500031
2119930884934205443018401282352000002084500031
2119930885054136323018501282352000002084500031
2121896965174067203018601282352000002084500031
2132382725295964163018701282356000002333700024
2132382725428346883018801282356000002333700024
2132382725560729603018901282356000002333700024
2132382725693112323019001282356000002333700024
2145489925825495043019101282360000002544800001
2145489925970984963019201282360000002544800001
2145489926116474883019301282360000002544800001
2146145286261964803019401282360000002544800001
2159907846408110083042601282364000002792700110
2159907846568017923042701282364000002792700110
2159907846727925763042801282364000002792700110
2161218566887833603042901282364000002792700110
287818247049052163043001282368000003105700017
287818247136870403043101282368000003105700017
287818247224688643043401282368000003105700017
287818247312506883043501282368000003105700017
287818247400325123043801282368000003105700017
287818247488143363043901282368000003105700017
287818247575961603044201282368000003105700017
290439687663779843044301282368000003105700017
296993287754219523044601282376000001725500002
296993287851212803044701282376000001725500002
296993287948206083044801282376000001725500002
296993288045199363045101282376000001725500002
296993288142192643045401282376000001725500002
296993288239185923045501282376000001725500002
296993288336179203045601282376000001725500002
296993288433172483045901282376000001725500002
2106168328530165763046001282384000001851500055
2106168328636334083046301282384000001851500055
2106168328742502403046401282384000001851500055
2106168328848670723046701282384000001851500055
2106168328954839043047001282384000001851500055
2106168329061007363047101282384000001851500055
2106168329167175683047201282384000001851500055
2110100489273344003047501282384000001851500055
2117309449383444483047601282392000002110300002
2117309449500753923047901282392000002110300002
2117309449618063363048621282392000002110300002
2117309449735372803048721282392000002110300002
2117309449852682243048821282392000002110300002
211730944996999168299710642392000002110300002
2117309441008730112299720642392000002110300002
2117309441020461056299730642392000002110300002
2128450561032192000299740642400000002249900009
2128450561045037056299750642400000002249900009
2128450561057882112299760642400000002249900009
2128450561070727168299770642400000002249900009
2128450561083572224299780642400000002249900009
2128450561096417280299790642400000002249900009
2128450561109262336299800642400000002249900009
2133038081122107392299810642400000002249900009
2141557761135411200299820642408000002558100012
2141557761149566976299830642408000002558100012
2141557761163722752299840642408000002558100012
2141557761177878528299850642408000002558100012
2141557761192034304299860642408000002558100012
2141557761206190080299870642408000002558100012
2141557761220345856299880642408000002558100012
2144834561234501632299890642408000002558100012
21559756812489850883048201287574000002249400034
21559756812645826563048321287574000002249400034
My hovercraft is full of eels.
June 30, 2011 at 4:02 pm
sql perf query now shows this:
CWNCM1220.8672.2688150
Yesterday, it was over 28 gig, with a very small utilization percentage.
My hovercraft is full of eels.
June 30, 2011 at 4:52 pm
Something that happened before those were run cleared the log. You should be able to shrink it to a sensible size now (once-off shrink)
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
July 1, 2011 at 10:45 am
Long running transactions prevent the transaction log from shinking. you can check by running the DBCC command.
DBCC OPENTRAN()
July 1, 2011 at 12:32 pm
That's a nice collection of VLFs you have there! I recommend you find and read Kimberly Tripps blog post on optimizing transaction logs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply