SQL Server Performance

Transaction log file space usage check

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Rabani, Mar 8, 2009.

  1. Rabani New Member

    Hi Freinds,
    Is there any query that can retrieve the current usage by transaction log file. Please, help.
    Thanks,
    Rabani
    Rabani, Mar 8, 2009
    #2

  2. Panik77 New Member

    SELECT name, physical_name, size, max_size, growth FROM sys.master_files
    WHERE name = 'templog'
    Change "tempdb" to the logical name of the transaction log file you are interested in.
    Size and Max_size, are returned in 8-KB pages. Divide by 128 to convert to MB
    Documentation for this system table is here:
    http://msdn.microsoft.com/en-us/library/ms186782(SQL.90).aspx
    Panik77, Mar 8, 2009
    #3

  3. johnson_ef Member

    johnson_ef, Mar 10, 2009
    #4

  4. pavelcc New Member

    dbcc sqlperf(logspace)
    pavelcc, Apr 13, 2011
    #5

  5. satya Moderator

    Pavel
    SQLPERF(LOGSPACE) will get you % of space used by the log, however the OP asks about actual physical file usage in terms of MB or GB.
    satya, May 3, 2011
    #6

  6. mmarovic Active Member

    [quote user="satya"]
    Pavel
    SQLPERF(LOGSPACE) will get you % of space used by the log, however the OP asks about actual physical file usage in terms of MB or GB.
    [/quote]Besides that, the question was posted in 2009. [:)]
    mmarovic, May 4, 2011
    #7

  7. pavelcc New Member

    Did you try this query?
    dbcc sqlperf(logspace)
    Db name Log_Size(MB) Log space Used%
    master .4921875 84.12698 0
    tempdb 26.17969 36.64578 0
    model 0.4921875 80.95238 0
    msdb 1.992188 43.33333 0
    I ans this questing knowing the fact that, it is an old question. However, I felt the ans should be dbcc sqlperf(logspace). In case any one still look for something like this today.
    pavelcc, May 4, 2011
    #8

  8. satya Moderator

    Appreciate your concern, but your response is for inside the transaction log usage and OP has specifically asked about Tlog file space usage, which means on the operating system.
    Anyhow please share your knowledge that can help the users here [:)], don't think that I'm picking you on this one.
    satya, May 9, 2011
    #9

  9. pavelcc New Member

    T-Log Size? Or log size? Because Log size can be determined by dbcc sqlperf(logspace)
    pavelcc, May 9, 2011
    #10

  10. satya Moderator

    Yes, the logical size and returns the current size of the transaction log and the percentage of log space used for each database. You can use this information to monitor the amount of space used in a transaction log.
    satya, May 9, 2011
    #11

Share This Page