SQL Server Performance

Transaction Log Full

Discussion in 'General DBA Questions' started by greenant, Jun 12, 2009.

  1. greenant New Member

    I have a SQL 2000 database where the transaction log usage continues to grow over time until the log becomes full. Results of DBCC SQLPERF(logspace) approx. 60 min apart show that the log space used is continuing to grow. Log Size (MB) Log Space Used(%)
    7129.617 31.00442
    7129.617 51.38907
    I verified that the database is using the Simple recovery model and I don’t see any long running, uncommitted transactions. Also, replication appears to be keeping up without any latency issues.
    DBCC OPENTRAN('utarepl_0')Transaction information for database 'utarepl_0'. Replicated Transaction Information: Oldest distributed LSN : (132663:1512:3) Oldest non-distributed LSN : (132663:1514:1)DBCC execution completed. If DBCC printed error messages, contact your system administrator. I think that the system should automatically issue Checkpoints that will truncate the log if you are using the Simple recovery model, so I don’t understand why my log usage continues to increase. The recovery interval for the server is the default value of 0. If I manually issue a checkpoint then the log space usage is reduced and the dbcc loginfo shows that most of the virtual log files have a status of 0.
    Does anyone have any idea why the log isn’t being truncated automatically? I’ve included the ouput from the DBCC LOGINFO below (before issuing a checkpoint). Thanks, Tim FileID FileSize StartOffset FSeqNo Status Parity CreateLSN2 253952 8192 132315 2 128 02 253952 262144 132314 2 64 02 270336 516096 132338 2 64 50000000487000452 262144 786432 132347 2 64 80000000478000012 262144 1048576 132348 2 64 90000000494000012 262144 1310720 132349 2 64 100000000495000012 262144 1572864 132421 2 64 110000000494000012 262144 1835008 132422 2 64 250000000511000012 262144 2097152 132333 2 128 270000000152000032 262144 2359296 132423 2 64 270000000510000012 262144 2621440 132335 2 128 290000000140000012 327680 2883584 132334 2 128 290000000510000052 327680 3211264 132336 2 128 310000000152000032 393216 3538944 132337 2 128 320000000270000012 393216 3932160 132424 2 128 17220000000152000032 458752 4325376 132324 2 64 17230000000399000012 253952 4784128 132396 2 128 630630000000016000012 270336 5038080 132350 2 128 630630000000016000012 253952 5308416 132420 2 128 630640000000527000012 270336 5562368 132407 2 128 630640000000527000012 253952 5832704 132362 2 128 1304240000000240000432 335872 6086656 132351 2 128 1304240000000240000432 253952 6422528 132361 2 128 1304250000000528000052 401408 6676480 132360 2 128 1304250000000528000052 253952 7077888 132364 2 128 1304280000000135003012 466944 7331840 132363 2 128 1304280000000135003012 253952 7798784 132395 2 128 1304290000000895000652 253952 8052736 132374 2 128 1304290000000895000652 278528 8306688 132365 2 128 1304290000000895000652 253952 8585216 132402 2 128 1304340000000491000012 253952 8839168 132398 2 128 1304340000000491000012 344064 9093120 132397 2 128 1304340000000491000012 253952 9437184 132401 2 128 1304360000000174001202 253952 9691136 132400 2 128 1304360000000174001202 475136 9945088 132399 2 128 1304360000000174001202 253952 10420224 132406 2 128 1304400000000289000012 253952 10674176 132405 2 128 1304400000000289000012 253952 10928128 132404 2 128 1304400000000289000012 286720 11182080 132403 2 128 1304400000000289000012 262144 11468800 132411 2 128 1304450000000231000962 262144 11730944 132410 2 128 1304450000000231000962 262144 11993088 132409 2 128 1304450000000231000962 393216 12255232 132408 2 128 1304450000000231000962 327680 12648448 132415 2 128 1304490000000101000782 327680 12976128 132414 2 128 1304490000000101000782 327680 13303808 132413 2 128 1304490000000101000782 327680 13631488 132412 2 128 1304490000000101000782 327680 13959168 132419 2 128 1304530000000513000022 327680 14286848 132418 2 128 1304530000000513000022 327680 14614528 132417 2 128 1304530000000513000022 458752 14942208 132416 2 128 1304530000000513000022 393216 15400960 132328 2 64 1304630000000528003192 393216 15794176 132327 2 64 1304630000000528003192 393216 16187392 132326 2 64 1304630000000528003192 393216 16580608 132325 2 64 1304630000000528003192 393216 16973824 132332 2 64 1304670000000605000012 393216 17367040 132331 2 64 1304670000000605000012 393216 17760256 132330 2 64 1304670000000605000012 524288 18153472 132329 2 64 1304670000000605000012 458752 18677760 132342 2 64 1304790000000016002962 458752 19136512 132341 2 64 1304790000000016002962 458752 19595264 132340 2 64 1304790000000016002962 524288 20054016 132339 2 64 1304790000000016002962 524288 20578304 132346 2 64 1304830000000673000012 524288 21102592 132345 2 64 1304830000000673000012 524288 21626880 132344 2 64 1304830000000673000012 524288 22151168 132343 2 64 1304830000000673000012 524288 22675456 132355 2 64 1304920000000459002742 524288 23199744 132354 2 64 1304920000000459002742 524288 23724032 132353 2 64 1304920000000459002742 720896 24248320 132352 2 64 1304920000000459002742 589824 24969216 132359 2 64 1304960000000136000012 589824 25559040 132358 2 64 1304960000000136000012 589824 26148864 132357 2 64 1304960000000136000012 720896 26738688 132356 2 64 1304960000000136000012 655360 27459584 132369 2 64 1305060000000125000692 655360 28114944 132368 2 64 1305060000000125000692 655360 28770304 132367 2 64 1305060000000125000692 786432 29425664 132366 2 64 1305060000000125000692 720896 30212096 132373 2 64 1305100000000981001372 720896 30932992 132372 2 64 1305100000000981001372 720896 31653888 132371 2 64 1305100000000981001372 851968 32374784 132370 2 64 1305100000000981001372 786432 33226752 132378 2 64 1305140000001358001242 786432 34013184 132377 2 64 1305140000001358001242 786432 34799616 132376 2 64 1305140000001358001242 983040 35586048 132375 2 64 1305140000001358001242 917504 36569088 132382 2 64 1305190000000846001262 917504 37486592 132381 2 64 1305190000000846001262 917504 38404096 132380 2 64 1305190000000846001262 917504 39321600 132379 2 64 1305190000000846001262 983040 40239104 132386 2 64 1305230000001431003072 983040 41222144 132385 2 64 1305230000001431003072 983040 42205184 132384 2 64 1305230000001431003072 1114112 43188224 132383 2 64 1305230000001431003072 1114112 44302336 132390 2 64 1305270000001134001612 1114112 45416448 132389 2 64 1305270000001134001612 1114112 46530560 132388 2 64 1305270000001134001612 1114112 47644672 132387 2 64 1305270000001134001612 1179648 48758784 132394 2 64 1305310000001941003152 1179648 49938432 132393 2 64 1305310000001941003152 1179648 51118080 132392 2 64 1305310000001941003152 1376256 52297728 132391 2 64 1305310000001941003152 1310720 53673984 132428 2 128 1314300000000408000032 1310720 54984704 132427 2 128 1314300000000408000032 1310720 56295424 132426 2 128 1314300000000408000032 1441792 57606144 132425 2 128 1314300000000408000032 1441792 59047936 132432 2 128 1314340000002200000032 1441792 60489728 132431 2 128 1314340000002200000032 1441792 61931520 132430 2 128 1314340000002200000032 1572864 63373312 132429 2 128 1314340000002200000032 1572864 64946176 132436 2 128 1314380000002456000032 1572864 66519040 132435 2 128 1314380000002456000032 1572864 68091904 132434 2 128 1314380000002456000032 1769472 69664768 132433 2 128 1314380000002456000032 1769472 71434240 132440 2 128 1314420000002712000032 1769472 73203712 132439 2 128 1314420000002712000032 1769472 74973184 132438 2 128 1314420000002712000032 1835008 76742656 132437 2 128 1314420000002712000032 1966080 78577664 132444 2 128 1314460000003096000032 1966080 80543744 132443 2 128 1314460000003096000032 1966080 82509824 132442 2 128 1314460000003096000032 1966080 84475904 132441 2 128 1314460000003096000032 2162688 86441984 132448 2 128 1314500000003470000132 2162688 88604672 132447 2 128 1314500000003470000132 2162688 90767360 132446 2 128 1314500000003470000132 2162688 92930048 132445 2 128 1314500000003470000132 2359296 95092736 132452 2 128 1314540000003864000032 2359296 97452032 132451 2 128 1314540000003864000032 2359296 99811328 132450 2 128 1314540000003864000032 2424832 102170624 132449 2 128 1314540000003864000032 2621440 104595456 132456 2 128 1314580000004248000032 2621440 107216896 132455 2 128 1314580000004248000032 2621440 109838336 132454 2 128 1314580000004248000032 2621440 112459776 132453 2 128 1314580000004248000032 2883584 115081216 132460 2 128 1314620000004759000032 2883584 117964800 132459 2 128 1314620000004759000032 2883584 120848384 132458 2 128 1314620000004759000032 2883584 123731968 132457 2 128 1314620000004759000032 3145728 126615552 132464 2 128 1314660000005272000032 3145728 129761280 132463 2 128 1314660000005272000032 3145728 132907008 132462 2 128 1314660000005272000032 3276800 136052736 132461 2 128 1314660000005272000032 3473408 139329536 132468 2 128 1314700000005784000032 3473408 142802944 132467 2 128 1314700000005784000032 3473408 146276352 132466 2 128 1314700000005784000032 3538944 149749760 132465 2 128 1314700000005784000032 3801088 153288704 132472 2 128 1314740000006424000032 3801088 157089792 132471 2 128 1314740000006424000032 3801088 160890880 132470 2 128 1314740000006424000032 3932160 164691968 132469 2 128 1314740000006424000032 4194304 168624128 132476 2 128 1314780000007064000032 4194304 172818432 132475 2 128 1314780000007064000032 4194304 177012736 132474 2 128 1314780000007064000032 4325376 181207040 132473 2 128 1314780000007064000032 4587520 185532416 132480 2 128 1314820000007823000012 4587520 190119936 132479 2 128 1314820000007823000012 4587520 194707456 132478 2 128 1314820000007823000012 4784128 199294976 132477 2 128 1314820000007823000012 5111808 204079104 132484 2 128 1314860000008583000012 5111808 209190912 132483 2 128 1314860000008583000012 5111808 214302720 132482 2 128 1314860000008583000012 5111808 219414528 132481 2 128 1314860000008583000012 5570560 224526336 132323 2 64 1314900000009623000092 5570560 230096896 132322 2 64 1314900000009623000092 5570560 235667456 132321 2 64 1314900000009623000092 5767168 241238016 132485 2 128 1314900000009623000092 6160384 247005184 132489 2 64 1316580000010209002692 6160384 253165568 132488 2 64 1316580000010209002692 6160384 259325952 132487 2 64 1316580000010209002692 6225920 265486336 132486 2 64 1316580000010209002692 6750208 271712256 132493 2 64 1316620000010923000882 6750208 278462464 132492 2 64 1316620000010923000882 6750208 285212672 132491 2 64 1316620000010923000882 6946816 291962880 132490 2 64 1316620000010923000882 7471104 298909696 132497 2 64 1316660000012149000542 7471104 306380800 132496 2 64 1316660000012149000542 7471104 313851904 132495 2 64 1316660000012149000542 7471104 321323008 132494 2 64 1316660000012149000542 8192000 328794112 132501 2 64 1316700000013571001222 8192000 336986112 132500 2 64 1316700000013571001222 8192000 345178112 132499 2 64 1316700000013571001222 8323072 353370112 132498 2 64 1316700000013571001222 9043968 361693184 132505 2 64 1316740000014516000582 9043968 370737152 132504 2 64 1316740000014516000582 9043968 379781120 132503 2 64 1316740000014516000582 9043968 388825088 132502 2 64 1316740000014516000582 9895936 397869056 132509 2 64 1316780000016523003122 9895936 407764992 132508 2 64 1316780000016523003122 9895936 417660928 132507 2 64 1316780000016523003122 10092544 427556864 132506 2 64 1316780000016523003122 10944512 437649408 132513 2 64 1316820000018130000922 10944512 448593920 132512 2 64 1316820000018130000922 10944512 459538432 132511 2 64 1316820000018130000922 10944512 470482944 132510 2 64 1316820000018130000922 11993088 481427456 132517 2 64 1316860000019613000762 11993088 493420544 132516 2 64 1316860000019613000762 11993088 505413632 132515 2 64 1316860000019613000762 12189696 517406720 132514 2 64 1316860000019613000762 13238272 529596416 132521 2 64 1316900000022006000382 13238272 542834688 132520 2 64 1316900000022006000382 13238272 556072960 132519 2 64 1316900000022006000382 13238272 569311232 132518 2 64 1316900000022006000382 14548992 582549504 132525 2 64 1316940000025366006542 14548992 597098496 132524 2 64 1316940000025366006542 14548992 611647488 132523 2 64 1316940000025366006542 14614528 626196480 132522 2 64 1316940000025366006542 15990784 640811008 132529 2 64 1316980000027526004882 15990784 656801792 132528 2 64 1316980000027526004882 15990784 672792576 132527 2 64 1316980000027526004882 16121856 688783360 132526 2 64 1316980000027526004882 8781824 704905216 132537 2 64 1317020000030872000032 8781824 713687040 132536 2 64 1317020000030872000032 8781824 722468864 132535 2 64 1317020000030872000032 8781824 731250688 132534 2 64 1317020000030872000032 8781824 740032512 132533 2 64 1317020000030872000032 8781824 748814336 132532 2 64 1317020000030872000032 8781824 757596160 132531 2 64 1317020000030872000032 9043968 766377984 132530 2 64 1317020000030872000032 9699328 775421952 132545 2 64 1317100000016776000042 9699328 785121280 132544 2 64 1317100000016776000042 9699328 794820608 132543 2 64 1317100000016776000042 9699328 804519936 132542 2 64 1317100000016776000042 9699328 814219264 132541 2 64 1317100000016776000042 9699328 823918592 132540 2 64 1317100000016776000042 9699328 833617920 132539 2 64 1317100000016776000042 9699328 843317248 132538 2 64 1317100000016776000042 10616832 853016576 132553 2 64 1317180000018579000112 10616832 863633408 132552 2 64 1317180000018579000112 10616832 874250240 132551 2 64 1317180000018579000112 10616832 884867072 132550 2 64 1317180000018579000112 10616832 895483904 132549 2 64 1317180000018579000112 10616832 906100736 132548 2 64 1317180000018579000112 10616832 916717568 132547 2 64 1317180000018579000112 11010048 927334400 132546 2 64 1317180000018579000112 11730944 938344448 132561 2 64 1317260000020376000032 11730944 950075392 132560 2 64 1317260000020376000032 11730944 961806336 132559 2 64 1317260000020376000032 11730944 973537280 132558 2 64 1317260000020376000032 11730944 985268224 132557 2 64 1317260000020376000032 11730944 996999168 132556 2 64 1317260000020376000032 11730944 1008730112 132555 2 64 1317260000020376000032 11730944 1020461056 132554 2 64 1317260000020376000032 12845056 1032192000 132569 2 64 1317340000022552000032 12845056 1045037056 132568 2 64 1317340000022552000032 12845056 1057882112 132567 2 64 1317340000022552000032 12845056 1070727168 132566 2 64 1317340000022552000032 12845056 1083572224 132565 2 64 1317340000022552000032 12845056 1096417280 132564 2 64 1317340000022552000032 12845056 1109262336 132563 2 64 1317340000022552000032 13303808 1122107392 132562 2 64 1317340000022552000032 14155776 1135411200 132577 2 64 1317420000024722000122 14155776 1149566976 132576 2 64 1317420000024722000122 14155776 1163722752 132575 2 64 1317420000024722000122 14155776 1177878528 132574 2 64 1317420000024722000122 14155776 1192034304 132573 2 64 1317420000024722000122 14155776 1206190080 132572 2 64 1317420000024722000122 14155776 1220345856 132571 2 64 1317420000024722000122 14483456 1234501632 132570 2 64 1317420000024722000122 15597568 1248985088 132585 2 64 1317500000027288000032 15597568 1264582656 132584 2 64 1317500000027288000032 15597568 1280180224 132583 2 64 1317500000027288000032 15597568 1295777792 132582 2 64 1317500000027288000032 15597568 1311375360 132581 2 64 1317500000027288000032 15597568 1326972928 132580 2 64 1317500000027288000032 15597568 1342570496 132579 2 64 1317500000027288000032 15728640 1358168064 132578 2 64 1317500000027288000032 17170432 1373896704 132593 2 64 1317580000030104000032 17170432 1391067136 132592 2 64 1317580000030104000032 17170432 1408237568 132591 2 64 1317580000030104000032 17170432 1425408000 132590 2 64 1317580000030104000032 17170432 1442578432 132589 2 64 1317580000030104000032 17170432 1459748864 132588 2 64 1317580000030104000032 17170432 1476919296 132587 2 64 1317580000030104000032 17235968 1494089728 132586 2 64 1317580000030104000032 18874368 1511325696 132601 2 64 1317660000032259000892 18874368 1530200064 132600 2 64 1317660000032259000892 18874368 1549074432 132599 2 64 1317660000032259000892 18874368 1567948800 132598 2 64 1317660000032259000892 18874368 1586823168 132597 2 64 1317660000032259000892 18874368 1605697536 132596 2 64 1317660000032259000892 18874368 1624571904 132595 2 64 1317660000032259000892 19005440 1643446272 132594 2 64 1317660000032259000892 20774912 1662451712 132609 2 64 1317740000035260000662 20774912 1683226624 132608 2 64 1317740000035260000662 20774912 1704001536 132607 2 64 1317740000035260000662 20774912 1724776448 132606 2 64 1317740000035260000662 20774912 1745551360 132605 2 64 1317740000035260000662 20774912 1766326272 132604 2 64 1317740000035260000662 20774912 1787101184 132603 2 64 1317740000035260000662 20840448 1807876096 132602 2 64 1317740000035260000662 22806528 1828716544 132617 2 64 1317820000040216000032 22806528 1851523072 132616 2 64 1317820000040216000032 22806528 1874329600 132615 2 64 1317820000040216000032 22806528 1897136128 132614 2 64 1317820000040216000032 22806528 1919942656 132613 2 64 1317820000040216000032 22806528 1942749184 132612 2 64 1317820000040216000032 22806528 1965555712 132611 2 64 1317820000040216000032 23265280 1988362240 132610 2 64 1317820000040216000032 25100288 2011627520 132625 2 64 1317900000044184000032 25100288 2036727808 132624 2 64 1317900000044184000032 25100288 2061828096 132623 2 64 1317900000044184000032 25100288 2086928384 132622 2 64 1317900000044184000032 25100288 2112028672 132621 2 64 1317900000044184000032 25100288 2137128960 132620 2 64 1317900000044184000032 25100288 2162229248 132619 2 64 1317900000044184000032 25493504 2187329536 132618 2 64 1317900000044184000032 27656192 2212823040 132633 2 64 1317980000048653000012 27656192 2240479232 132632 2 64 1317980000048653000012 27656192 2268135424 132631 2 64 1317980000048653000012 27656192 2295791616 132630 2 64 1317980000048653000012 27656192 2323447808 132629 2 64 1317980000048653000012 27656192 2351104000 132628 2 64 1317980000048653000012 27656192 2378760192 132627 2 64 1317980000048653000012 27721728 2406416384 132626 2 64 1317980000048653000012 30408704 2434138112 132641 2 64 1318060000053656000032 30408704 2464546816 132640 2 64 1318060000053656000032 30408704 2494955520 132639 2 64 1318060000053656000032 30408704 2525364224 132638 2 64 1318060000053656000032 30408704 2555772928 132637 2 64 1318060000053656000032 30408704 2586181632 132636 2 64 1318060000053656000032 30408704 2616590336 132635 2 64 1318060000053656000032 30605312 2646999040 132634 2 64 1318060000053656000032 33423360 2677604352 132649 2 64 1318140000057978000032 33423360 2711027712 132648 2 64 1318140000057978000032 33423360 2744451072 132647 2 64 1318140000057978000032 33423360 2777874432 132646 2 64 1318140000057978000032 33423360 2811297792 132645 2 64 1318140000057978000032 33423360 2844721152 132644 2 64 1318140000057978000032 33423360 2878144512 132643 2 64 1318140000057978000032 33816576 2911567872 132642 2 64 1318140000057978000032 36765696 2945384448 132657 2 64 1318220000064920000032 36765696 2982150144 132656 2 64 1318220000064920000032 36765696 3018915840 132655 2 64 1318220000064920000032 36765696 3055681536 132654 2 64 1318220000064920000032 36765696 3092447232 132653 2 64 1318220000064920000032 36765696 3129212928 132652 2 64 1318220000064920000032 36765696 3165978624 132651 2 64 1318220000064920000032 37224448 3202744320 132650 2 64 1318220000064920000032 40501248 3239968768 132251 0 128 1318300000071448000032 40501248 3280470016 132250 0 128 1318300000071448000032 40501248 3320971264 132663 2 64 1318300000071448000032 40501248 3361472512 132662 2 64 1318300000071448000032 40501248 3401973760 132661 2 64 1318300000071448000032 40501248 3442475008 132660 2 64 1318300000071448000032 40501248 3482976256 132659 2 64 1318300000071448000032 40501248 3523477504 132658 2 64 1318300000071448000032 44498944 3563978752 132259 0 128 1318380000078736000042 44498944 3608477696 132258 0 128 1318380000078736000042 44498944 3652976640 132257 0 128 1318380000078736000042 44498944 3697475584 132256 0 128 1318380000078736000042 44498944 3741974528 132255 0 128 1318380000078736000042 44498944 3786473472 132254 0 128 1318380000078736000042 44498944 3830972416 132253 0 128 1318380000078736000042 44957696 3875471360 132252 0 128 1318380000078736000042 48955392 3920429056 132267 0 128 1318460000086116004292 48955392 3969384448 132266 0 128 1318460000086116004292 48955392 4018339840 132265 0 128 1318460000086116004292 48955392 4067295232 132264 0 128 1318460000086116004292 48955392 4116250624 132263 0 128 1318460000086116004292 48955392 4165206016 132262 0 128 1318460000086116004292 48955392 4214161408 132261 0 128 1318460000086116004292 49348608 4263116800 132260 0 128 1318460000086116004292 53870592 4312465408 132275 0 128 1318540000095256000032 53870592 4366336000 132274 0 128 1318540000095256000032 53870592 4420206592 132273 0 128 1318540000095256000032 53870592 4474077184 132272 0 128 1318540000095256000032 53870592 4527947776 132271 0 128 1318540000095256000032 53870592 4581818368 132270 0 128 1318540000095256000032 53870592 4635688960 132269 0 128 1318540000095256000032 54198272 4689559552 132268 0 128 1318540000095256000032 59244544 4743757824 132283 0 128 1318620000104248000642 59244544 4803002368 132282 0 128 1318620000104248000642 59244544 4862246912 132281 0 128 1318620000104248000642 59244544 4921491456 132280 0 128 1318620000104248000642 59244544 4980736000 132279 0 128 1318620000104248000642 59244544 5039980544 132278 0 128 1318620000104248000642 59244544 5099225088 132277 0 128 1318620000104248000642 59703296 5158469632 132276 0 128 1318620000104248000642 65208320 5218172928 132291 0 128 1318700000115352000022 65208320 5283381248 132290 0 128 1318700000115352000022 65208320 5348589568 132289 0 128 1318700000115352000022 65208320 5413797888 132288 0 128 1318700000115352000022 65208320 5479006208 132287 0 128 1318700000115352000022 65208320 5544214528 132286 0 128 1318700000115352000022 65208320 5609422848 132285 0 128 1318700000115352000022 65404928 5674631168 132284 0 128 1318700000115352000022 71696384 5740036096 132299 0 128 1318780000125857000552 71696384 5811732480 132298 0 128 1318780000125857000552 71696384 5883428864 132297 0 128 1318780000125857000552 71696384 5955125248 132296 0 128 1318780000125857000552 71696384 6026821632 132295 0 128 1318780000125857000552 71696384 6098518016 132294 0 128 1318780000125857000552 71696384 6170214400 132293 0 128 1318780000125857000552 72155136 6241910784 132292 0 128 1318780000125857000552 78905344 6314065920 132307 0 128 1318860000139671000032 78905344 6392971264 132306 0 128 1318860000139671000032 78905344 6471876608 132305 0 128 1318860000139671000032 78905344 6550781952 132304 0 128 1318860000139671000032 78905344 6629687296 132303 0 128 1318860000139671000032 78905344 6708592640 132302 0 128 1318860000139671000032 78905344 6787497984 132301 0 128 1318860000139671000032 79101952 6866403328 132300 0 128 1318860000139671000032 86769664 6945505280 132320 2 128 1318940000153109002972 86769664 7032274944 132319 2 128 1318940000153109002972 86769664 7119044608 132318 2 128 1318940000153109002972 90046464 7205814272 132317 2 64 1323070000153739000042 90046464 7295860736 132316 2 64 1323070000153739000042 90046464 7385907200 132313 2 64 132307000015373900004
  2. Luis Martin Moderator

    What is your recovery model and your backup routine?
  3. greenant New Member

    The recovery model is Simple. There is no backup routine - this is one of more than 100 servers that collects data and forwards it to a central data warehouse using transactional replication.
  4. deepakontheweb New Member

    recovery interval controls when SQL Server issues a checkpoint in each database. Checkpoints are done on a per database basis. Keep recovery interval set at 0 (self-configuring). Use Sp_configure or EM to check as detailed:- 1) How to set the recovery interval (Enterprise Manager)2) To set the recovery interval 3) Expand a server group.4) Right-click a server, and then click Properties.5) Click the Database Settings tab.6) Under Recovery, in the Recovery interval (min) box, type or select a value from 0 through 32767 to set the maximum amount of time, in minutes, that Microsoft® SQL Server™ should spend recovering each database at startup.
  5. greenant New Member

    Thanks for your reply. I verified that the recovery interval is set to 0. The database is relatively active ~ 350 transactions per second so I would expect a checkpoint to happen quite frequently. If I don't issue a manual checkpoint, the log will continue to grow until it fills up the drive. It seems like the server is not issuing an automatic checkpoint.
  6. deepakontheweb New Member

    Ok, then its time find whats going on.... 1) check the database property for "IsTruncLog" using databaseproperty: DATABASEPROPERTY( database , property) 2) start a perfmon trace on the system for below counter; every 1 second and see the data.[SQL: Buffer Manager: checkpoint pages/sec](explanation: Number of pages flushed by checkpoint or other operations that require all dirty pages to be flushed.)
  7. greenant New Member

    I checked the IsTruncLog preoperty and it is set to 1.
    I started a trace that included the checkpoint pages/sec and the SQLServer:Databases Log Files(s) Used Size (KB). The trace ran for over 4 hours and it showed two spikes in the checkpoint pages/sec approx 3.5 hours apart. For the problem database, the log file used size continued to grow. For the other active database on that server, the log file used size was reduced 600KB each time there was a spike in the checkpoint pages.
  8. deepakontheweb New Member

    just for information, do you have a some very long running transactions on this database? or some loop based continuing running batches?
  9. greenant New Member

    No, I used DBCC OPENTRAN and it didn't show any open transactions.

Share This Page