SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log file has grown too big


Log file has grown too big

Author
Message
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3264 Visits: 3200
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89377 Visits: 45284
Why are you regularly shrinking the log?

Please read through this - Managing Transaction Logs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3264 Visits: 3200
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89377 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


DBA_Dom
DBA_Dom
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 81110
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89377 Visits: 45284
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, MVP, M.Sc (Comp Sci)
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


DBA_Dom
DBA_Dom
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 81110
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.
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2836 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Joy Smith San
Joy Smith San
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3264 Visits: 3200
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.
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2836 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search