1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

MSSQL log backup Capacity Planning

Discussion in 'Microsoft SQL Server' started by mmbdcis, Jun 6, 2012.

  1. mmbdcis

    mmbdcis New Member

    Joined:
    Aug 24, 2011
    Messages:
    17
    Likes Received:
    0
    Hi,


    I'm trying to produce a report for all SQL servers backup up with TDP, which should include the space reserves available for each DB, in case of a TSM disaster/failure.
    In other words, I need to know for each of the Databases its average use of transaction logs, so that I can calculate how long that DB could run, staying in Full Recovery mode while the TSM backup is unavailable, considering its current disk space and limits.

    Does anyone know a relevant SQL command or tool to see statistics of this sort, in order to understand how much transaction logs does a DB create every day on average?

    Thanks,
     
  2.  
  3. Trident

    Trident Senior Member

    Joined:
    Apr 2, 2007
    Messages:
    277
    Likes Received:
    25
    Occupation:
    IT operations
    Location:
    Oslo, Norway
    Code:
    select START_TIME,bytes from summary where entity='SQLTDP' and schedule_name='YourLogSchedulename'
    This will give you a time range and bytes transferred. The actual log size on the sql server may be larger. If possible, allow for 2-3 days of logdata on the sql server.
     
    Last edited: Jun 6, 2012
  4. mmbdcis

    mmbdcis New Member

    Joined:
    Aug 24, 2011
    Messages:
    17
    Likes Received:
    0
    Thanks Trident, I will check this query this week and will report back.
     
  5. mmbdcis

    mmbdcis New Member

    Joined:
    Aug 24, 2011
    Messages:
    17
    Likes Received:
    0
    Well, tested the query and it looks like it's exactly what I need, thanks!
    Now I'm just gonna write a script to wrap it in to and I'm ready to go.
     

Share This Page