First of all, the size of the transaction log being ~ 50 MB is a relative measure. Usually, it will be around 1/3 to 2 times the size of all of the data files combined. However, that is highly dependent on the types of transactions that are ran against the system.

Also, the way to manage the log is highly dependent on the environmental constraints. What version of SQL Server are you running? What is the recovery model? What is the database used for and does the recovery model support those goals? What is the DR plan? Does this system require point-in-time recovery? Etc., etc., etc.

If you just want to clear out the log and do not care about recovering any of those transaction, then just execute:

BACKUP LOG <database name>
WITH NO_LOG

However, SQL Server 6.5 uses the DUMP statement syntax.

The fact that you have to run this command means you are not running the simple recovery model. Furthermore, do you know if the space within the log is actually being used or is this just the last AUTO GROWTH extent?

You can find out by using the undocumented DBCC LOGINFO command.

If you are wanting to reduce the file size, given there is little to no content, then use the DBCC SHRINKFILE command. Again, version dependent. The next question would be what caused the log to grow to this size in the first place and is that process normal? If so, shrinking it will only work temporarily. The next execution of the process will grow it again, or fail, use up resources incrementally growing it instead of just allocating the required space, and get you right back where you started.

Sincerely,


Anthony Thomas, MCDBA, MCSA


-----Original Message-----
From: jean-marc Ledgister
[mailto:mssqldba-ezmlmshield-x89048268.[Email address protected]
Sent: Friday, July 30, 2004 9:45 AM
To: LazyDBA Discussion
Subject: transaction log is ~ 50 Meg how do i trunk/comit the log?


transaction log is ~ 50 Meg how do i trunk/comit the log?
can i just delete the log?



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]




***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************

MS Sql Server LazyDBA home page