Anyway I can reduce my file size?
Thanks
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" <te...@hotmail.com> wrote in message
news:4AF6B35D-96F8-47B9...@microsoft.com...
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...@microsoft.com...
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" <te...@hotmail.com> wrote in message
news:B1E73D7A-EEE9-4CA1...@microsoft.com...
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...@microsoft.com...
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" <te...@hotmail.com> wrote in message
news:025D5AF2-04F6-4CE2...@microsoft.com...
"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/dbm_best_pract.mspx
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" <te...@hotmail.com> wrote in message
> news:025D5AF2-04F6-4CE2...@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...@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" <te...@hotmail.com> wrote in message
> >> news:B1E73D7A-EEE9-4CA1...@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...@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
--
Ekrem Önsoy
"Hans S" <Hans S...@discussions.microsoft.com> wrote in message
news:10CEFE9F-1478-4269...@microsoft.com...