Mirroring and triggers - effect on transaction log sizes.

  • I have two separate servers with SQL server 2005 (Server I and Server II). Mirroring is configured between Database A in Server I and Database A (mirror) in Server II.

    There is a trigger written in both principal and mirror version of Database A. The trigger basically does the audit on a table on Database A and writes the audit data to Database B which is also mirrored to Server II. The triggers are not specified with NOT FOR REPLICATION.

    Two weeks has passed after the deployment and now the transaction log size grew very large (around 32 GB) in Server I (principal). I know that the transaction file growth is a side effect of mirroring. But does such a fast growth has anything to do with triggers written?

    What are the precautions to be taken in a scenario where triggers and mirroring comes together?

  • 1) How do you have the mirroring setup, with or without witness (async or sync mirroring)?

    The t-log size for the Database A would only grow if the mirroring is not happening. By the way, which t-log is growing?

    2) What appears when you pull up the Mirroring Monitor?

    3) Are you doing frequent t-log backups on Server 1?

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • 1) How do you have the mirroring setup, with or without witness (async or sync mirroring)?

    We are doing synchronous mirroring without witness.

    The t-log size for the Database A would only grow if the mirroring is not happening. By the way, which t-log is growing?

    t-log of Database A is only growing.

    2) What appears when you pull up the Mirroring Monitor?

    Am not able to see the Launch Mirroring Monitor option in tasks. Is there any other alternate?

    3) Are you doing frequent t-log backups on Server 1?

    This important task was not not done regularly. Now we have started doing it.

  • Check if the t-logs are performed successfully.

    --The query below will pull all trace data using the log auto growth event

    SELECT

    loginname,

    loginsid,

    spid,

    hostname,

    applicationname,

    servername,

    databasename,

    objectName,

    e.category_id,

    cat.name,

    textdata,

    starttime,

    endtime,

    duration,

    eventclass,

    eventsubclass,

    e.name as EventName

    FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\<log_latestnumber>.trc',0)

    INNER JOIN sys.trace_events e ON eventclass = trace_event_id

    INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id

    WHERE databasename = '<Db-Name>' AND

    e.category_id = 2 AND --category 2 is database

    e.trace_event_id = 93 --93=Log File Auto Grow

    Pavan.

  • Hi Pavan

    I tried the query and found that the tlogs are coming successful.

    I got around 45 rows with EventName="Log File Auto Grow"

  • Richard's #3 is exactly it. How frequently are you performing log backups. It should be a minimum frequency of 30 minutes. If your server is very busy, then you should do it more frequently such as every 15, 10, or 5 minutes.

    You need to have backups set up on the mirror side as well so that backups will continue in the case of a failover.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Thank you all for your valuable help. From here on I will make sure my tlogs are backed up frequently. 🙂

  • logicwonder (6/15/2010)


    The t-log size for the Database A would only grow if the mirroring is not happening. By the way, which t-log is growing?

    t-log of Database A is only growing.

    Here's the catch as well. If the mirroring "is not happening" as you state, it makes perfect sense that the t-log would grow, as the server can not relay the changes to the mirrored DB and needs to keep them somewhere until it can... Doing frequent t-log backups will only allow it to empty it IF they have been relayed to the other server, so if the t-log of Database A grows as a result of accumulating transactions it couldn't send, you would need to manually shrink it at some point, even if it is empty because of frequent t-log backups (and synced with Server 2 of course)

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply