Re: Shrinking database, simple model

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: 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)
  • 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: Open mdf in SQL server 2005
    ... I detach in sql server 2000 and I attach in SQL server and it works. ... to have the LDF file, ... Now I would like to convert open the mdf file in sql server 2005 and I ... I attach but appears an error: Fail to retrive data for this request. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Recover records from ldf file.
    ... SQL Server 2000 internals. ... recover will bring the database up to the current state and that you won't ... What if the log starts before the backup you recovered? ... Database *.ldf file from the failing hard drive. ...
    (microsoft.public.sqlserver.server)