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://qa.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:
20 CWNCM 28163.1875
20 CWNCM_log 1220.875
20 NULL 29384.0625
NULL NULL 29384.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:
CWNCM FULL NOTHING
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):
2 253952 8192 30484 2 64 0
2 253952 262144 30485 2 64 0
2 270336 516096 29995 0 128 35000000013600110
2 262144 786432 29990 0 64 145000000003500003
2 262144 1048576 29991 0 64 155000000006600075
2 262144 1310720 29994 0 64 746000000004900048
2 262144 1572864 29992 0 128 1088000000037000056
2 262144 1835008 29993 0 128 1089000000036700025
2 262144 2097152 29996 0 128 2164000000018400043
2 262144 2359296 29997 0 128 2165000000019600012
2 262144 2621440 29998 0 128 2166000000019700006
2 327680 2883584 29999 0 128 2167000000018500053
2 327680 3211264 30000 0 128 2168000000032000025
2 393216 3538944 30001 0 128 2169000000032700002
2 393216 3932160 30002 0 128 2170000000044300051
2 458752 4325376 30003 0 128 2171000000045100009
2 253952 4784128 30004 0 128 2172000000057000056
2 270336 5038080 30005 0 128 2172000000057000056
2 253952 5308416 30006 0 128 2174000000020200055
2 270336 5562368 30007 0 128 2174000000020200055
2 253952 5832704 30008 0 128 2176000000021000010
2 335872 6086656 30009 0 128 2176000000021000010
2 253952 6422528 30010 0 128 2178000000034100012
2 401408 6676480 30011 0 128 2178000000034100012
2 253952 7077888 30012 0 128 2180000000046500016
2 466944 7331840 30013 0 128 2180000000046500016
2 253952 7798784 30014 0 128 2182000000059000034
2 253952 8052736 30015 0 128 2182000000059000034
2 278528 8306688 30016 0 128 2182000000059000034
2 253952 8585216 30017 0 128 2185000000023000007
2 253952 8839168 30018 0 128 2185000000023000007
2 344064 9093120 30019 0 128 2185000000023000007
2 253952 9437184 30020 0 128 2188000000035500015
2 253952 9691136 30021 0 128 2188000000035500015
2 475136 9945088 30022 0 128 2188000000035500015
2 253952 10420224 30023 0 128 2191000000059800002
2 253952 10674176 30024 0 128 2191000000059800002
2 253952 10928128 30025 0 128 2191000000059800002
2 286720 11182080 30026 0 128 2191000000059800002
2 262144 11468800 30027 0 128 2195000000051900002
2 262144 11730944 30028 0 128 2195000000051900002
2 262144 11993088 30029 0 128 2195000000051900002
2 393216 12255232 30030 0 128 2195000000051900002
2 327680 12648448 30031 0 128 2199000000067400063
2 327680 12976128 30032 0 128 2199000000067400063
2 327680 13303808 30033 0 128 2199000000067400063
2 327680 13631488 30034 0 128 2199000000067400063
2 327680 13959168 30035 0 128 2203000000053300008
2 327680 14286848 30036 0 128 2203000000053300008
2 327680 14614528 30037 0 128 2203000000053300008
2 458752 14942208 30038 0 128 2203000000053300008
2 393216 15400960 30039 0 128 2207000000078900011
2 393216 15794176 30040 0 128 2207000000078900011
2 393216 16187392 30041 0 128 2207000000078900011
2 393216 16580608 30042 0 128 2207000000078900011
2 393216 16973824 30043 0 128 2211000000065200012
2 393216 17367040 30044 0 128 2211000000065200012
2 393216 17760256 30045 0 128 2211000000065200012
2 524288 18153472 30046 0 128 2211000000065200012
2 458752 18677760 30047 0 128 2215000000090400023
2 458752 19136512 30048 0 128 2215000000090400023
2 458752 19595264 30049 0 128 2215000000090400023
2 524288 20054016 30050 0 128 2215000000090400023
2 524288 20578304 30051 0 128 2219000000091100003
2 524288 21102592 30052 0 128 2219000000091100003
2 524288 21626880 30053 0 128 2219000000091100003
2 524288 22151168 30054 0 128 2219000000091100003
2 524288 22675456 30055 0 128 2223000000098600019
2 524288 23199744 30056 0 128 2223000000098600019
2 524288 23724032 30057 0 128 2223000000098600019
2 720896 24248320 30058 0 128 2223000000098600019
2 589824 24969216 30059 0 128 2227000000134800011
2 589824 25559040 30060 0 128 2227000000134800011
2 589824 26148864 30061 0 128 2227000000134800011
2 720896 26738688 30062 0 128 2227000000134800011
2 655360 27459584 30063 0 128 2231000000134300028
2 655360 28114944 30064 0 128 2231000000134300028
2 655360 28770304 30065 0 128 2231000000134300028
2 786432 29425664 30066 0 128 2231000000134300028
2 720896 30212096 30067 0 128 2235000000145500028
2 720896 30932992 30068 0 128 2235000000145500028
2 720896 31653888 30069 0 128 2235000000145500028
2 851968 32374784 30070 0 128 2235000000145500028
2 786432 33226752 30071 0 128 2239000000161300045
2 786432 34013184 30072 0 128 2239000000161300045
2 786432 34799616 30073 0 128 2239000000161300045
2 983040 35586048 30074 0 128 2239000000161300045
2 917504 36569088 30075 0 128 2243000000185300031
2 917504 37486592 30076 0 128 2243000000185300031
2 917504 38404096 30077 0 128 2243000000185300031
2 917504 39321600 30078 0 128 2243000000185300031
2 983040 40239104 30079 0 128 2247000000169800013
2 983040 41222144 30080 0 128 2247000000169800013
2 983040 42205184 30081 0 128 2247000000169800013
2 1114112 43188224 30082 0 128 2247000000169800013
2 1114112 44302336 30083 0 128 2251000000213900017
2 1114112 45416448 30084 0 128 2251000000213900017
2 1114112 46530560 30085 0 128 2251000000213900017
2 1114112 47644672 30086 0 128 2251000000213900017
2 1179648 48758784 30087 0 128 2255000000208300010
2 1179648 49938432 30088 0 128 2255000000208300010
2 1179648 51118080 30089 0 128 2255000000208300010
2 1376256 52297728 30090 0 128 2255000000208300010
2 1310720 53673984 30091 0 128 2259000000249100010
2 1310720 54984704 30092 0 128 2259000000249100010
2 1310720 56295424 30093 0 128 2259000000249100010
2 1441792 57606144 30094 0 128 2259000000249100010
2 1441792 59047936 30095 0 128 2263000000270000012
2 1441792 60489728 30096 0 128 2263000000270000012
2 1441792 61931520 30097 0 128 2263000000270000012
2 1572864 63373312 30098 0 128 2263000000270000012
2 1572864 64946176 30099 0 128 2267000000279400005
2 1572864 66519040 30100 0 128 2267000000279400005
2 1572864 68091904 30101 0 128 2267000000279400005
2 1769472 69664768 30102 0 128 2267000000279400005
2 1769472 71434240 30103 0 128 2271000000320900020
2 1769472 73203712 30104 0 128 2271000000320900020
2 1769472 74973184 30105 0 128 2271000000320900020
2 1835008 76742656 30106 0 128 2271000000320900020
2 1966080 78577664 30107 0 128 2275000000349800086
2 1966080 80543744 30108 0 128 2275000000349800086
2 1966080 82509824 30109 0 128 2275000000349800086
2 1966080 84475904 30110 0 128 2275000000349800086
2 2162688 86441984 30111 0 128 2279000000370800008
2 2162688 88604672 30112 0 128 2279000000370800008
2 2162688 90767360 30113 0 128 2279000000370800008
2 2162688 92930048 30114 0 128 2279000000370800008
2 2359296 95092736 30115 0 128 2283000000416800028
2 2359296 97452032 30116 0 128 2283000000416800028
2 2359296 99811328 30117 0 128 2283000000416800028
2 2424832 102170624 30118 0 128 2283000000416800028
2 2621440 104595456 30119 0 128 2287000000467600037
2 2621440 107216896 30120 0 128 2287000000467600037
2 2621440 109838336 30121 0 128 2287000000467600037
2 2621440 112459776 30122 0 128 2287000000467600037
2 2883584 115081216 30123 0 128 2291000000505100017
2 2883584 117964800 30124 0 128 2291000000505100017
2 2883584 120848384 30125 0 128 2291000000505100017
2 2883584 123731968 30126 0 128 2291000000505100017
2 3145728 126615552 30127 0 128 2295000000559500055
2 3145728 129761280 30128 0 128 2295000000559500055
2 3145728 132907008 30129 0 128 2295000000559500055
2 3276800 136052736 30130 0 128 2295000000559500055
2 3473408 139329536 30131 0 128 2300000000015400054
2 3473408 142802944 30132 0 128 2300000000015400054
2 3473408 146276352 30133 0 128 2300000000015400054
2 3538944 149749760 30134 0 128 2300000000015400054
2 3801088 153288704 30135 0 128 2304000000658000005
2 3801088 157089792 30136 0 128 2304000000658000005
2 3801088 160890880 30137 0 128 2304000000658000005
2 3932160 164691968 30138 0 128 2304000000658000005
2 4194304 168624128 30139 0 128 2308000000725500002
2 4194304 172818432 30140 0 128 2308000000725500002
2 4194304 177012736 30141 0 128 2308000000725500002
2 4325376 181207040 30142 0 128 2308000000725500002
2 4587520 185532416 30143 0 128 2312000000802800041
2 4587520 190119936 30144 0 128 2312000000802800041
2 4587520 194707456 30145 0 128 2312000000802800041
2 4784128 199294976 30146 0 128 2312000000802800041
2 5111808 204079104 30147 0 128 2316000000894800012
2 5111808 209190912 30148 0 128 2316000000894800012
2 5111808 214302720 30149 0 128 2316000000894800012
2 5111808 219414528 30150 0 128 2316000000894800012
2 5570560 224526336 30151 0 128 2320000000953600064
2 5570560 230096896 30152 0 128 2320000000953600064
2 5570560 235667456 30153 0 128 2320000000953600064
2 5767168 241238016 30154 0 128 2320000000953600064
2 6160384 247005184 30155 0 128 2324000001085800009
2 6160384 253165568 30156 0 128 2324000001085800009
2 6160384 259325952 30157 0 128 2324000001085800009
2 6225920 265486336 30158 0 128 2324000001085800009
2 6750208 271712256 30159 0 128 2328000001175700007
2 6750208 278462464 30160 0 128 2328000001175700007
2 6750208 285212672 30161 0 128 2328000001175700007
2 6946816 291962880 30162 0 128 2328000001175700007
2 7471104 298909696 30163 0 128 2332000001296700003
2 7471104 306380800 30164 0 128 2332000001296700003
2 7471104 313851904 30165 0 128 2332000001296700003
2 7471104 321323008 30166 0 128 2332000001296700003
2 8192000 328794112 30167 0 128 2336000001411100026
2 8192000 336986112 30168 0 128 2336000001411100026
2 8192000 345178112 30169 0 128 2336000001411100026
2 8323072 353370112 30170 0 128 2336000001411100026
2 9043968 361693184 30171 0 128 2340000001578400011
2 9043968 370737152 30172 0 128 2340000001578400011
2 9043968 379781120 30173 0 128 2340000001578400011
2 9043968 388825088 30174 0 128 2340000001578400011
2 9895936 397869056 30175 0 128 2344000001724300009
2 9895936 407764992 30176 0 128 2344000001724300009
2 9895936 417660928 30177 0 128 2344000001724300009
2 10092544 427556864 30178 0 128 2344000001724300009
2 10944512 437649408 30179 0 128 2348000001904900051
2 10944512 448593920 30180 0 128 2348000001904900051
2 10944512 459538432 30181 0 128 2348000001904900051
2 10944512 470482944 30182 0 128 2348000001904900051
2 11993088 481427456 30183 0 128 2352000002084500031
2 11993088 493420544 30184 0 128 2352000002084500031
2 11993088 505413632 30185 0 128 2352000002084500031
2 12189696 517406720 30186 0 128 2352000002084500031
2 13238272 529596416 30187 0 128 2356000002333700024
2 13238272 542834688 30188 0 128 2356000002333700024
2 13238272 556072960 30189 0 128 2356000002333700024
2 13238272 569311232 30190 0 128 2356000002333700024
2 14548992 582549504 30191 0 128 2360000002544800001
2 14548992 597098496 30192 0 128 2360000002544800001
2 14548992 611647488 30193 0 128 2360000002544800001
2 14614528 626196480 30194 0 128 2360000002544800001
2 15990784 640811008 30426 0 128 2364000002792700110
2 15990784 656801792 30427 0 128 2364000002792700110
2 15990784 672792576 30428 0 128 2364000002792700110
2 16121856 688783360 30429 0 128 2364000002792700110
2 8781824 704905216 30430 0 128 2368000003105700017
2 8781824 713687040 30431 0 128 2368000003105700017
2 8781824 722468864 30434 0 128 2368000003105700017
2 8781824 731250688 30435 0 128 2368000003105700017
2 8781824 740032512 30438 0 128 2368000003105700017
2 8781824 748814336 30439 0 128 2368000003105700017
2 8781824 757596160 30442 0 128 2368000003105700017
2 9043968 766377984 30443 0 128 2368000003105700017
2 9699328 775421952 30446 0 128 2376000001725500002
2 9699328 785121280 30447 0 128 2376000001725500002
2 9699328 794820608 30448 0 128 2376000001725500002
2 9699328 804519936 30451 0 128 2376000001725500002
2 9699328 814219264 30454 0 128 2376000001725500002
2 9699328 823918592 30455 0 128 2376000001725500002
2 9699328 833617920 30456 0 128 2376000001725500002
2 9699328 843317248 30459 0 128 2376000001725500002
2 10616832 853016576 30460 0 128 2384000001851500055
2 10616832 863633408 30463 0 128 2384000001851500055
2 10616832 874250240 30464 0 128 2384000001851500055
2 10616832 884867072 30467 0 128 2384000001851500055
2 10616832 895483904 30470 0 128 2384000001851500055
2 10616832 906100736 30471 0 128 2384000001851500055
2 10616832 916717568 30472 0 128 2384000001851500055
2 11010048 927334400 30475 0 128 2384000001851500055
2 11730944 938344448 30476 0 128 2392000002110300002
2 11730944 950075392 30479 0 128 2392000002110300002
2 11730944 961806336 30486 2 128 2392000002110300002
2 11730944 973537280 30487 2 128 2392000002110300002
2 11730944 985268224 30488 2 128 2392000002110300002
2 11730944 996999168 29971 0 64 2392000002110300002
2 11730944 1008730112 29972 0 64 2392000002110300002
2 11730944 1020461056 29973 0 64 2392000002110300002
2 12845056 1032192000 29974 0 64 2400000002249900009
2 12845056 1045037056 29975 0 64 2400000002249900009
2 12845056 1057882112 29976 0 64 2400000002249900009
2 12845056 1070727168 29977 0 64 2400000002249900009
2 12845056 1083572224 29978 0 64 2400000002249900009
2 12845056 1096417280 29979 0 64 2400000002249900009
2 12845056 1109262336 29980 0 64 2400000002249900009
2 13303808 1122107392 29981 0 64 2400000002249900009
2 14155776 1135411200 29982 0 64 2408000002558100012
2 14155776 1149566976 29983 0 64 2408000002558100012
2 14155776 1163722752 29984 0 64 2408000002558100012
2 14155776 1177878528 29985 0 64 2408000002558100012
2 14155776 1192034304 29986 0 64 2408000002558100012
2 14155776 1206190080 29987 0 64 2408000002558100012
2 14155776 1220345856 29988 0 64 2408000002558100012
2 14483456 1234501632 29989 0 64 2408000002558100012
2 15597568 1248985088 30482 0 128 7574000002249400034
2 15597568 1264582656 30483 2 128 7574000002249400034
My hovercraft is full of eels.
June 30, 2011 at 4:02 pm
sql perf query now shows this:
CWNCM 1220.867 2.268815 0
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