sql server (alternate): Hi,

I have a big log file in my database.
Probably it is result of debugging process
of my application (break execution before commit).

What should i do with this log ?
Is it possible just to remove it ?

best regards
pluton

pluton (zielonadupa@poczta.onet.pl) writes:
[quoted text, click to view]

*NEVER* delete a log file. *NEVER*. Unless you are dropping the entire
database.

If the log file is overly big, you can use DBCC SHRINKFILE to shrink it.
Use the target_size parameter (look it up in Books Online) to shrink it
to a normal size. Don't shrink it to nothing, as it will autogrow, and
autogrow takes performance.

Note also that if the database is in full or bulk-logged recovery mode,
you need to backup the transaction log regularly.


--
Erland Sommarskog, SQL Server MVP, esquel@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

Hi,

[quoted text, click to view]

Thanks a lot.

[quoted text, click to view]

The point is, that i'd like to 'commit' all opened transactions
or 'remove' them, because I know that they are not necessary anymore.

best regards
pluton

pluton (zielonadupa@poczta.onet.pl) writes:
[quoted text, click to view]

You cannot remove open transactions from the log, by any other means
that killing the connections that owns the transaction. But if you said
that you had been debugging, and then exited during a breakpoint, those
transactions were rolled back when you disconnected.

Once a transaction is not open any more, it can be removed from the log,
which can happen in three ways:

o You backup the transaction log to disk or tape.
o You use BACKUP WITH TRUNCATE_ONLY, which cuts the log, but does not
save it.
o You have the database in simple recovery mode, in which case SQL Server
will auto-truncate it.

Notice that truncating is not the same thing as shrinking the file.

--
Erland Sommarskog, SQL Server MVP, esquel@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

Hi,

[quoted text, click to view]

Great! but WHY logi file has 50 MB ? :)
How to shrink it ?

[quoted text, click to view]

Thanks a lot! This is it !

Best regards
pluton

pluton (zielonadupa@poczta.onet.pl) writes:
[quoted text, click to view]

As I said, the log does not shrink automatically. So if you had a
transaction that required 50 MB in the log, the log grew to 50 MB.

Further review the recovery mode for the database. (Use sp_helpdb for
that.) I explained my previous post when data are removed from the
transaction log.

[quoted text, click to view]

That, too, I have told you in a previous post. But there is little point
in shrinking a 50 MB log file. Most likely it will grow to that size
again, unless your database is extremely tiny.


--
Erland Sommarskog, SQL Server MVP, esquel@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