SQL Server Performance

Reading DBCC Loginfo

Discussion in 'General DBA Questions' started by rerichards, Dec 8, 2006.

  1. rerichards New Member

    So, if I run DBCC LOGINFO(TempDB) and return the following, is that telling
    me all the Status 2 rows are waiting to be written to TempDB, have been
    written to TempDB, or what?

    FILEID FILESIZE STARTOFFSET FSEQNO STATUS
    PARITY CREATELSN
    2 98238464 8192 4977 2 128 0
    2 98238464 98246656 5017 2 128 0
    2 98238464 196485120 5016 2 128 0
    2 98238464 294723584 5015 2 128 0
    2 98238464 392962048 5014 2 128 0
    2 98238464 491200512 5013 2 128 0
    2 98238464 589438976 4987 2 128 0
    2 98238464 687677440 4986 2 128 0
    2 98238464 785915904 4985 2 128 0
    2 98238464 884154368 4984 2 128 0
    2 98238464 982392832 4983 2 128 0
    2 98238464 1080631296 4982 2 128 0
    2 98238464 1178869760 4981 2 128 0
    2 98238464 1277108224 4980 2 128 0
    2 98238464 1375346688 4979 2 128 0
    2 99278848 1473585152 4978 2 128 0
    2 19660800 1572864000 4995 2 128 3727000019124700327
    2 19660800 1592524800 4994 2 128 3727000019124700327
    2 19660800 1612185600 4993 2 128 3727000019124700327
    2 19660800 1631846400 4992 2 128 3727000019124700327
    2 19660800 1651507200 4991 2 128 3727000019124700327
    2 19660800 1671168000 4990 2 128 3727000019124700327
    2 19660800 1690828800 4989 2 128 3727000019124700327
    2 19660800 1710489600 4988 2 128 3727000019124700327
    2 21626880 1730150400 5003 2 128 3735000003768400352
    2 21626880 1751777280 5002 2 128 3735000003768400352
    2 21626880 1773404160 5001 2 128 3735000003768400352
    2 21626880 1795031040 5000 2 128 3735000003768400352
    2 21626880 1816657920 4999 2 128 3735000003768400352
    2 21626880 1838284800 4998 2 128 3735000003768400352
    2 21626880 1859911680 4997 2 128 3735000003768400352
    2 21626880 1881538560 4996 2 128 3735000003768400352
    2 23789568 1903165440 5011 2 128 3743000003552900353
    2 23789568 1926955008 5010 2 128 3743000003552900353
    2 23789568 1950744576 5009 2 128 3743000003552900353
    2 23789568 1974534144 5008 2 128 3743000003552900353
    2 23789568 1998323712 5007 2 128 3743000003552900353
    2 23789568 2022113280 5006 2 128 3743000003552900353
    2 23789568 2045902848 5005 2 128 3743000003552900353
    2 23789568 2069692416 5004 2 128 3743000003552900353
    2 26148864 2093481984 5012 2 128 3751000004596700594
    2 26476544 2119630848 5025 2 64 5017000019143700476
    2 26476544 2146107392 5024 2 64 5017000019143700476
    2 26476544 2172583936 5023 2 64 5017000019143700476
    2 26476544 2199060480 5022 2 64 5017000019143700476
    2 26476544 2225537024 5021 2 64 5017000019143700476
    2 26476544 2252013568 5020 2 64 5017000019143700476
    2 26476544 2278490112 5019 2 64 5017000019143700476
    2 26673152 2304966656 5018 2 64 5017000019143700476
    2 29097984 2331639808 5033 2 64 5025000005094400786
    2 29097984 2360737792 5032 2 64 5025000005094400786
    2 29097984 2389835776 5031 2 64 5025000005094400786
    2 29097984 2418933760 5030 2 64 5025000005094400786
    2 29097984 2448031744 5029 2 64 5025000005094400786
    2 29097984 2477129728 5028 2 64 5025000005094400786
    2 29097984 2506227712 5027 2 64 5025000005094400786
    2 29491200 2535325696 5026 2 64 5025000005094400786
    2 32047104 2564816896 5041 2 64 5033000005643500488
    2 32047104 2596864000 5040 2 64 5033000005643500488
    2 32047104 2628911104 5039 2 64 5033000005643500488
    2 32047104 2660958208 5038 2 64 5033000005643500488
    2 32047104 2693005312 5037 2 64 5033000005643500488
    2 32047104 2725052416 5036 2 64 5033000005643500488
    2 32047104 2757099520 5035 2 64 5033000005643500488
    2 32178176 2789146624 5034 2 64 5033000005643500488
    2 35258368 2821324800 5049 2 64 5041000003779800256
    2 35258368 2856583168 5048 2 64 5041000003779800256
    2 35258368 2891841536 5047 2 64 5041000003779800256
    2 35258368 2927099904 5046 2 64 5041000003779800256
    2 35258368 2962358272 5045 2 64 5041000003779800256
    2 35258368 2997616640 5044 2 64 5041000003779800256
    2 35258368 3032875008 5043 2 64 5041000003779800256
    2 35323904 3068133376 5042 2 64 5041000003779800256
    2 38797312 3103457280 5057 2 64 5049000006835600298
    2 38797312 3142254592 5056 2 64 5049000006835600298
    2 38797312 3181051904 5055 2 64 5049000006835600298
    2 38797312 3219849216 5054 2 64 5049000006835600298
    2 38797312 3258646528 5053 2 64 5049000006835600298
    2 38797312 3297443840 5052 2 64 5049000006835600298
    2 38797312 3336241152 5051 2 64 5049000006835600298
    2 38797312 3375038464 5050 2 64 5049000006835600298
    2 42663936 3413835776 5065 2 64 5057000007537400445
    2 42663936 3456499712 5064 2 64 5057000007537400445
    2 42663936 3499163648 5063 2 64 5057000007537400445
    2 42663936 3541827584 5062 2 64 5057000007537400445
    2 42663936 3584491520 5061 2 64 5057000007537400445
    2 42663936 3627155456 5060 2 64 5057000007537400445
    2 42663936 3669819392 5059 2 64 5057000007537400445
    2 42729472 3712483328 5058 2 64 5057000007537400445
    2 46923776 3755212800 0 0 0 5065000008293000544
    2 46923776 3802136576 5072 2 64 5065000008293000544
    2 46923776 3849060352 5071 2 64 5065000008293000544
    2 46923776 3895984128 5070 2 64 5065000008293000544
    2 46923776 3942907904 5069 2 64 5065000008293000544
    2 46923776 3989831680 5068 2 64 5065000008293000544
    2 46923776 4036755456 5067 2 64 5065000008293000544
    2 47054848 4083679232 5066 2 64 5065000008293000544
  2. MohammedU New Member

  3. rerichards New Member

    What is the definition of active?


    quote:Originally posted by MohammedU

    It means Virtual log file is active...
  4. satya Moderator

    BOL states in Transaction architecture that "the end of the log grows through each virtual log file in each physical file before circling back to the first virtual log file in the first physical file" and you will see this last virtual log file as an active.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. rerichards New Member

    Thanks Satya. We are getting somewhere but I am still not clear.

    Consider the output of DBCC LogInfo(TempDb) that was in my initial post on this thread. Nearly all the rows returned have a Status = 2 [Active]. Are these returns from DBCC LogInfo telling me that all the virtual log files with a Status = 2, are active, which I take it to mean, that all these virtual log files are being "actively" used by TempDB?



    quote:Originally posted by satya

    BOL states in Transaction architecture that "the end of the log grows through each virtual log file in each physical file before circling back to the first virtual log file in the first physical file" and you will see this last virtual log file as an active.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  6. satya Moderator

    Theoritically yes on the TEMPDB basis, you may be aware Transaction logs are divided internally into sections called virtual log files. Virtual log files are the unit of space that can be reused. The active portion of the transaction log, the active log, cannot be truncated, because the active log is required to recover the database. The most recent checkpoint defines the active log.

    For example, the transaction log file may have 100 virtual log files, and only 2 virtual log files are used as active. SQL Server will be using first virtual log file at the start of the transaction log file and the second used virtual log file in the middle of the transaction log file. SQL Server moves the start of the logical log to the next available virtual log file that is specified by the log manager.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  7. rerichards New Member

    Considering the output of DBCC LogInfo(TempDb) that was in my initial post on this thread, does that mean that my "Active Log" consists of all the rows where Status = 2?



    quote:Originally posted by satya

    The most recent checkpoint defines the active log.
  8. satya Moderator

    Yes all the transactions associated in that sequence (LSN).

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  9. rerichards New Member

    So if I have:

    5 rows that have an LSN = 5065000008293000544, all with a status of 2 (and are the last 5 rows returned by DBCC LogInfo)

    and

    5 rows that have an LSN = 5041000003779800256, all with a status of 2,

    Does that mean my Active Log consists of those files associated with LSN = 5065000008293000544 ??


    quote:Originally posted by satya

    Yes all the transactions associated in that sequence (LSN).

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  10. rohit2900 Member

    How to I interpret the output of the command 'DBCC LOGINFO' as whhat does each field means?

Share This Page