That's what I'm talking about.
> This is what I just found about this subject of your growing log file. It
> is
> proberly an SQL Server 2005 problem I found on the Best practices technet
> site:
> "Once the mirror database becomes operational [again after a failure], it
> automatically assumes the role of the mirror and starts synchronizing with
> the principal. For as long as the mirroring state stays DISCONNECTED, the
> transaction log space on the principal cannot be reused, even if you back
> up
> the transaction log. If the log file grows and reaches its maximum size
> limit
> or runs out of disk space, the complete database comes to a halt. To
> prevent
> this you have two options—either plan for enough disk space for the
> transaction log to grow and bring back the mirror database before the
> space
> fills up, or break the database mirroring session."
> Complete article can be found:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/db...
> Hans
> "Ekrem Önsoy" wrote:
>> Index operations may cause the log files get bigger. However your problem
>> seems the broken Database Mirroring I believe.
>> As there is an active Database Mirroring (even if the mirror server is
>> broken), SQL Server keeps the transactions in the transaction log file to
>> send them to the mirror database when it's available. So, if you break
>> the
>> Database Mirroring, then SQL Server will let the passive virtual logs
>> truncated in the transaction file when you take it's backup.
>> --
>> Ekrem nsoy
>> "Test" <t...@hotmail.com> wrote in message
>> news:025D5AF2-04F6-4CE2-B6F5-EAE0D1327C21@microsoft.com...
>> > we have to make sure the log backup file size should be less, we
>> > observed
>> > that right after db Maintainance job, the log file size is too big and
>> > rest are small, that means we are doing some thing wrong in
>> > maintainance
>> > plan?
>> > In that maintainance plan we are doing following things
>> > 1. Full Backup
>> > 2. Statistics update
>> > 3. Index Defrag.
>> > Any idea, the above thing cause the file size bigger?
>> > Thanks
>> > "Ekrem nsoy" <ek...@compecta.com> wrote in message
>> > news:3FEB8F84-AF24-4CB0-9C08-0B3B53F61DFF@microsoft.com...
>> >> When your database is a part of Database Mirroring, you can not
>> >> truncate
>> >> the log even if you back up the transaction log.
>> >> So, you may think to break Database Mirroring and truncate your
>> >> transaction log file by backing it up.
>> >> And then try setup Database Mirroring again. But you should find out
>> >> why
>> >> this happened. Is you network bandwith enough to handle the workload
>> >> between your principal and mirror servers? Is there a problem with the
>> >> IO
>> >> on the principal or mirror?
>> >> --
>> >> Ekrem nsoy
>> >> "Test" <t...@hotmail.com> wrote in message
>> >> news:B1E73D7A-EEE9-4CA1-A911-2ACA85957550@microsoft.com...
>> >>>I am using Database Mirroring... The principle database Log File is 10
>> >>>GB
>> >>>and I can't shrink it... Is there any way I can reduce the log file
>> >>>size.
>> >>>earlier I took log backup every 6 hours but now every 1 hour but still
>> >>>log fize size is 10 GB and database size is 15 GB.
>> >>> I am having database mirroring error. Following is the error
>> >>> Communications to the remote server instance
>> >>> 'TCP://192.168.204.152:5432'
>> >>> failed before database mirroring was fully started. The ALTER
>> >>> DATABASE
>> >>> command failed. Retry the command when the remote database is
>> >>> started.
>> >>> I am thinking may be its becauase of log file size is too big.
>> >>> Thanks
>> >>> "Ekrem nsoy" <ek...@compecta.com> wrote in message
>> >>> news:38F0FE72-6C58-49F6-86A9-87FE33065062@microsoft.com...
>> >>>> To truncate the passive virtual logs in your tlog file use the
>> >>>> following line:
>> >>>> BACKUP LOG your_db WITH NO_LOG
>> >>>> To shrink the file physically, use the following one:
>> >>>> DBCC SHRINKFILE (your_log_file's_logical_name, <size of the tlog
>> >>>> file
>> >>>> you want it to be>)
>> >>>> Result of this operation depends on the active virtual logs in the
>> >>>> t-log file.
>> >>>> However, you also better take a look at the following article:
>> >>>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>>> --
>> >>>> Ekrem nsoy
>> >>>> "Test" <t...@hotmail.com> wrote in message
>> >>>> news:4AF6B35D-96F8-47B9-A03A-01D31144EC4F@microsoft.com...
>> >>>>>I would appreciate if anyone let me know why the log file size is
>> >>>>>increasing too much. My database size is 15 GB and my log file size
>> >>>>>is
>> >>>>>10 GB.
>> >>>>> Anyway I can reduce my file size?
>> >>>>> Thanks
|
