Re: Shrinking database, simple model

After running dbcc opentran we got:

Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)

Oldest non-distributed LSN : (11585:3549:1)

Is there anything concerning?


"m" <miroslavsi@xxxxxxxxx> wrote in message
news:h9g18k$ccf$1@xxxxxxxxxxxxxxxxxxx
Thanks for your answer,

In normal conditions LDF file isn't so big, it takes few Mb
This extra growing happened after extensive deleting (external
administration system delete row by row in many tables)

So, after that extensive deleting it is recommended to free LDF space
becouse we had problem with hard disk space?

Regards



"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9C9086AC36B2CYazorman@xxxxxxxxxxxx
m (miroslavsi@xxxxxxxxx) writes:
We have database in SIMPLE recovery model.

MDF file is about 3GB
but LDF file is about 15GB

We tried to shrink LDF, and after shrinking we got LDF about 7GB.

We used shrinking before and LDF file was always about 1Mb.

Can anyone tell me why SQL server still keep that 7 GB in LDF file and
is there any chance for server to release that space on his own without
forsing it? Is that just a question of time, and becouse SIMPLE recovery
model SQL server will do it by itself?

First, if the log file keeps growing to 15 GB, let it be that size. You
only lose performance if you shrink it. Particularly if you shrink to 1MB
with 10% autogrow.

If your log files grows to that size in simple recovery, this means that
you have individual transactions that require that amount of log space.
Do you perform heavy table reloads? Maybe these could be improved?

One reason the file does shrink more is that there is an open
transaction.
The log will not be truncated past the oldest transaction in the
database.
You can run DBCC OPENTRAN in the database to find out.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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




.

Relevant Pages

  • Re: Shrinking database, simple model
    ... We tried to shrink LDF, and after shrinking we got LDF about 7GB. ... We used shrinking before and LDF file was always about 1Mb. ... model SQL server will do it by itself? ... First, if the log file keeps growing to 15 GB, let it be that size. ...
    (comp.databases.ms-sqlserver)
  • Re: Shrinking database, simple model
    ... In normal conditions LDF file isn't so big, ... We tried to shrink LDF, and after shrinking we got LDF about 7GB. ... model SQL server will do it by itself? ... First, if the log file keeps growing to 15 GB, let it be that size. ...
    (comp.databases.ms-sqlserver)
  • Transaction log growth with MSDE and MSSQL
    ... We have experienced that the LDF file grow out of proportions. ... Knowledge Base we have found some descriptions on how to shrink the file. ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ...
    (microsoft.public.sqlserver.msde)
  • Re: big log file
    ... the log does not shrink automatically. ... transaction that required 50 MB in the log, the log grew to 50 MB. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Shrink .LDF File
    ... > The SQL Server 2000 in my network is hosting many database for many ... > and web hosting. ... > One of the .LDF file has grown very fast and I am very sure that it is ... > Maha Arupputhan Pappan ...
    (microsoft.public.sqlserver.datamining)