Database mirroring and log file growth

Is there a way to shrink the log file while using database mirroring?
We have a 20 GB database with a log file about 80 GB.
Transaction log backup is done every 30 minutes, full backup each night, but the log file is not truncated.

Since it seems that the log file grows about 1 GB each day (we have migrated from sql2000 about 50 days ago) we are going to run out of disk space in a few weeks!

Ivan

refer this link,

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=377087

they have suggested to ,issue checkpoint and then truncate log......

USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO

|||

Is the Database also part of replication ?

Is the Database Mirroring Active , STATE = SYNCRONIZED ?

|||just try the above method and get back if its not possible you need to remove mirroring

refer, http://technet.microsoft.com/en-us/library/ms189112.aspx then try to truncate the tran log and try to shrink using dbcc shrinkfile........i am not sure which of the above 2 will work out.........|||

Removing and Reestablishing mirroring for a large database might be some work , again it depends how quickly you want to fix it , however what i will suggest ; check if the scheduled TLOG backups are successfull ? if the database is acting as a publisher in replication topology ; also if you could post the mode of mirroring Sync or Async ?

Database Maintenance Plans and shrink database

I have read that having databases auto shrink can cause performance
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
Thanks
Yes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>

Database Maintenance Plans and shrink database

I have read that having databases auto shrink can cause performance
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
ThanksYes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>

Database Maintenance Plans and shrink database

I have read that having databases auto shrink can cause performance
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
ThanksYes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>

Database Maintenance

I have a maintenance plan set up to:
Remove unused space from database files
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink = 10%
Does this effect log files or just data files, or both?
--
Message posted via http://www.sqlmonster.comboth.
Many DBA's would not recommend shrinking the Database unless you
specifically are running into storage space issues.
My personal recommendation is that you disable this feature.
Greg Jackson
PDX, Oregon|||The maintenance plan will do a dbcc shrinkdatabase, not a
dbcc shrinkfile. As Greg posted, you really don't need this
in a regular maintenance plan. It's better to just size your
files appropriately and let them have the space they may
need for your normal operations. If they grow and you shrink
them and then they grow again right after you shrink them,
you just waste resources. Also, I think the maintenance plan
defaults to where you do a dbreindex and then a
shrinkdatabase. In that scenario, you likely just
reintroduce the fragmentation you cleaned up with the
dbreindex.
The following article on Tibor Karaszi's site had some
additional information on shrinking:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Tue, 08 Feb 2005 22:21:42 GMT, "Robert Richards via
SQLMonster.com" <forum@.SQLMonster.com> wrote:
>I have a maintenance plan set up to:
>Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
>Does this effect log files or just data files, or both?|||I have applied "autoshrink" to many of my databases specifically= to try and keep the size of the logs down, and it seems to help.= I had much trouble with logs growinbg larger than the database,= but this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink =3D 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"engel1" <engel1@.aew_nospam.com> wrote in message news:OE6hmHxEFHA.4004@.tk2msftngp13.phx.gbl...
I have applied "autoshrink" to many of my databases specifically to try and keep the size of the
logs down, and it seems to help. I had much trouble with logs growinbg larger than the database, but
this has improved with autoshrink and nightly log backups.
> I have a maintenance plan set up to:
> Remove unused space from database files
> Shrink database when it grows beyond: 50 MB
> Amount of free space to remain after shrink = 10%
> Does this effect log files or just data files, or both?
> --
> Message posted via http://www.sqlmonster.com
User submitted from AEWNET (http://www.aewnet.com/)

Database Log file doesn't shrink ?

Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon
i'm trying to shrink some of the log files for my databases
One has free space of about 200mb for the log file
I've tried shrinking it in enterprise manager but this doesn't help
It still has 200mb free space
What do i need to do
I was hoping to shrink them all b4 going to SP3
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
> >Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
> >i'm trying to shrink some of the log files for my databases.
> >One has free space of about 200mb for the log file.
> >I've tried shrinking it in enterprise manager but this doesn't help.
> >It still has 200mb free space.
> >
> >What do i need to do ?
> >I was hoping to shrink them all b4 going to SP3.
> >
> >thanks
>

Database Log file doesn't shrink ?

Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:

>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.
4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>
>

Database Log file doesn't shrink ?

Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanks
Refer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:

>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks
|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>