Log file Size..

[Test]

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

[Ekrem Önsoy]

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" <te...@hotmail.com> wrote in message
news:4AF6B35D-96F8-47B9...@microsoft.com...

[Test]

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...

[Ekrem Önsoy]

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...

[Test]

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...

[Ekrem Önsoy]

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...

[S@discussions.microsoft.com Hans S]

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/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]

That's what I'm talking about.

--
Ekrem Önsoy

"Hans S" <Hans S...@discussions.microsoft.com> wrote in message
news:10CEFE9F-1478-4269...@microsoft.com...