Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Log file has grown too big Expand / Collapse
Author
Message
Posted Wednesday, February 1, 2012 5:43 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:01 AM
Points: 1,557, Visits: 2,613
Hi All,

In one of the database where mirroring is configured, log file has grown too big.
We had backed up and shrunk the log file yesterday night.
No job or anything scheduled to run. There was no any bulk insert or reindexing as such.
I checked and found that there are no open transactions as well.

What else could be the issue. ? Where and how to check ? Please help.


Thanks,
Smith.
Post #1245002
Posted Wednesday, February 1, 2012 6:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 40,164, Visits: 36,552
Why are you regularly shrinking the log?

Please read through this - Managing Transaction Logs



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1245014
Posted Wednesday, February 1, 2012 6:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:01 AM
Points: 1,557, Visits: 2,613
Thanks Gila

Not regulerly. Since it had grown too big shrunk it once.
By the way, Mirror database is in "Restoring...." mode.
I mean, it's NOT displayed as "Synchronized/Restoring"..
Its just DBName(Restoring.......). is anything wrong ?
What and where to check. Can log grow because of any pbm in mirroring..?
Log size in both principal and mirroring are same as well.

Thanks.


Post #1245025
Posted Wednesday, February 1, 2012 6:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 40,164, Visits: 36,552
Yes, of course the log can grow because of mirroring.
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

I'm not a mirroring expert, check the transmission and redo queues and maybe post another thread specifically for mirroring problems.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1245029
Posted Wednesday, February 1, 2012 11:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 568, Visits: 73,736
In mirroring the log cannot be reused until a log backup occurs.

My suggestion is that if the log is growing to large between your log backup job executions then you should increase the frequency of the log backup job.

I had to do this for one of my servers because for one database through out the day the log would grow to 60GB and I was only taking a backup once a day. i modified this to happen 6 times a day and now the log is stable at about 20GB.
Post #1245240
Posted Wednesday, February 1, 2012 11:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 40,164, Visits: 36,552
DBA_Dom (2/1/2012)
In mirroring the log cannot be reused until a log backup occurs.


That's true for any time the DB is in full or bulk-logged recovery, not just when there's database mirroring.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1245252
Posted Wednesday, February 1, 2012 12:47 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 568, Visits: 73,736
Gila,
Granted, I was using the statement 'in mirroring..' as this is the situation and you can't have mirroring without the database in Full Recovery. Guess I could have worded this more like 'Since mirroring is required to have the database in Full Recovery model...'

I did also miss the part that it says the mirror shows as just restoring, does the mirror monitor show that this database is synchronized or is it in some other state. I would assume that if the logs are the same size and grow with each other than it is sychronized but never too careful.
Post #1245291
Posted Wednesday, February 1, 2012 1:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:42 PM
Points: 1,618, Visits: 1,549
My general rule of thumb is that you should back up the log AT LEAST every half hour. If the database is busy, then do it more frequently. The busier the database is, the more frequently you should back it up. This is the corner stone of log file maintenance.

When you shrank the log file on the principal, did it shrink on the mirror as well? If not, what build of SQL Server are you on?




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1245303
Posted Thursday, February 2, 2012 12:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:01 AM
Points: 1,557, Visits: 2,613
My general rule of thumb is that you should back up the log AT LEAST every half hour. If the database is busy, then do it more frequently. The busier the database is, the more frequently you should back it up. This is the corner stone of log file maintenance.


Database is not that busy.. There was no any such huge transaction as such ?
...OR how can I check if there was any such transaction which caused the log file size growth ?


When you shrank the log file on the principal, did it shrink on the mirror as well? If not, what build of SQL Server are you on?


No.. Actualy the log files size were diff.. think it was not synchronised that time. After we shrank and reconfgiured both are of same size now.

Thanks a lot to all.
Post #1245509
Posted Thursday, February 2, 2012 9:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:42 PM
Points: 1,618, Visits: 1,549
It doesn't have to have huge transactions. If you're not backing up the log, it adds up. Obviously it is that busy because the log had to grow.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1245923
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse