Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: log file sizes for all databases

    Hi All,

    I ran the following query to get the log file sizes for all databases:

    select (size*8.00)/1024,filename from master..sysaltfiles

    When I compared the results from this query with the transaction log properties in EM, not all of the sizes match.
    For example, EM shows the transaction log size for tempdb to be 2 MB but the results of the query shows that transaction log size for tempdb is .5 MB. Which query can I run to get the numbers that would match between two? Thanks.

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    I have checked that and find the difference.

    Run the below code which gives you the size of tempdb which is as same as physically stored file on your harddisk.

    sp_helpdb 'tempdb'

    or

    select name, filename, (size*8) as size from tempdb..sysfiles

    The size in the master..sysaltfiles will helps you to determine the size of files in the tempdb when SQL Server was last started.

    Refer the below web page & Sr # 6. RESOURCES

    http://www.sqlmag.com/Articles/Print...rticleID=39550

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    I've ran the statement that you sent me and I am still coming up with wrong number.

  4. #4
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by inka
    I've ran the statement that you sent me and I am still coming up with wrong number.
    The statement I have given will help you to figure out current tempdb log size.

    The size in the master..sysaltfiles will help you to determine the size of the files in the tempdb when SQL Server was last started.

    Refer the link I have posted above.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •