Prev: How to set locks to 0
Next: Large Log File
From: Nighthawk on 11 Aug 2010 05:13
Hello,

i have a replicated database (3 GB) with a log file that grown to 38 GB.
The recovery Model is simple.


DBCC Shrinkfile doesnt works.

DBCC OPENTRAN say:

Replizierte Transaktionsinformationen:
Oldest distributed : (0:0:0)
Oldest non-distributed LSN : (297400:25:1)





Rich Lawson (nospam(a)nospam.com) writes:
> I have a database which is 2 GB but the log file has grown to 35 GB. I
> would like to truncate the log so I set the recovery method to SIMPLE
> but the log file did not truncate after last night's backup.

If you set the recocvery model to SIMPLE, this will truncate the transaction
log. However, note that the size of the file will not change. You will need
to shrink it with DBCC SHRINKFILE.

Note also that the log never is never truncated past the oldest active
transaction; you can use DBCC OPENTRAN to check for this condition.

Note also that replication can affect whether the log is truncated.
Transactions not yet send to the distribution database will not be
truncated.

> This has worked for other databases and I get a small log file. Later, I
> reset the recovery method back to FULL.

I hope that you understand that when you do this that you break the log
chain, and if you have a requirement to be able to do point in time
restores, you will need to run a full backup directly after having set the
recovery model back to full.

You are backing up your transaction log regularly, aren't you?


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx




From: Erland Sommarskog on 11 Aug 2010 06:42
Nighthawk (beensen(a)contelos.de) writes:
> i have a replicated database (3 GB) with a log file that grown to 38 GB.
> The recovery Model is simple.
>
> DBCC Shrinkfile doesnt works.
>
> DBCC OPENTRAN say:
>
> Replizierte Transaktionsinformationen:
> Oldest distributed : (0:0:0)
> Oldest non-distributed LSN : (297400:25:1)
>
> What can i do, to shrink the log file?

Apparent there is a hiccup in replication, so that data has not been picked
up by the distributor.

It may be better to ask in a forum devoted to replication.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Sandy on 12 Aug 2010 08:10
On Aug 11, 3:42 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> Nighthawk (been...(a)contelos.de) writes:
> > i have a replicated database (3 GB) with a log file that grown to 38 GB..
> > The recovery Model is simple.
>
> > DBCC Shrinkfile doesnt works.
>
> > DBCC OPENTRAN say:
>
> > Replizierte Transaktionsinformationen:
> >         Oldest distributed : (0:0:0)
> >         Oldest non-distributed LSN : (297400:25:1)
>
> > What can i do, to shrink the log file?
>
> Apparent there is a hiccup in replication, so that data has not been picked
> up by the distributor.
>
> It may be better to ask in a forum devoted to replication.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Execute Exec sp_repldone command and you can shrink the log file.
But the commands pending for replication will not be replicated.

Regards,
Sandesh Segu
http://www.sanssql.com
 | 
Pages: 1
Prev: How to set locks to 0
Next: Large Log File