June 14, 2010 at 6:31 am
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?
June 14, 2010 at 12:23 pm
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?
June 15, 2010 at 12:51 am
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.
June 15, 2010 at 4:46 am
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.
June 15, 2010 at 7:23 am
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"
June 16, 2010 at 8:37 am
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.
June 16, 2010 at 8:45 pm
Thank you all for your valuable help. From here on I will make sure my tlogs are backed up frequently. 🙂
June 17, 2010 at 1:21 pm
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)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply