February 1, 2012 at 5:43 am
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.
February 1, 2012 at 6:03 am
Why are you regularly shrinking the log?
Please read through this - Managing Transaction Logs[/url]
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
February 1, 2012 at 6:15 am
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.
February 1, 2012 at 6:20 am
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
February 1, 2012 at 11:18 am
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.
February 1, 2012 at 11:52 am
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
February 1, 2012 at 12:47 pm
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.
February 1, 2012 at 1:03 pm
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?
February 2, 2012 at 12:42 am
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.
February 2, 2012 at 9:43 am
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.
February 3, 2012 at 12:11 am
Robert Davis (2/2/2012)
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.
Yup..Am aware about it. But it cannot happen overnight in that database for sure. Thats why I asked if it's possible to find if there was any such transaction which caused the log file size growth ?
Thanks a lot.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply