DUMP TRANSACTION database WITH NO_LOG

Hello there,
I am trying to learn about MS-SQL Server on my own and am faced with a
problem that requires action quickly, even though I would prefer to take my
time and dig out the answer from all the documentation available.
On the web site that I manage, some log files grow to be as large or larger
than the MDF files themselves, and I am concernced with preserving hard disk
space for obvious reasons. I asked the web hosting company's tech support
department for a way to reduce the size of these log files, and they pointed
me to this suggestion:
DUMP TRANSACTION database WITH NO_LOG
While I have used this command to reduce the log file size successfully, I
am concerned that I am actually truncating data that has not been committed
to the database yet. Am I worrying needlessly? Or am I actually losing data
when I run this command?
I have observed that when I run this query from the SQL Query Analyzer that
the log file does not instantly get smaller in size. Rather it gets
substantially smaller a few hours later. The MDF file is about 5.3 GB and
the LDF file is about 9 GB.
Thank you for whatever help you can offer.
Sincerely,
Mr. Mixter
Mick,
Don't worry. This command will truncate committed transactions from the
transaction log. If you don't have much control over your database in terms
of backups, etc, consider changing the recovery model to simple, this means
that committed transactions are removed from the transaction log, which will
stop it growing.
Once you have changed the recovery model and truncated the log, you will
need to shrink the log file using DBCC SHRINKFILE. Look that up in BOL.
Sounds like your hosting company admins need to go on a SQL Server course.
Regards,
Mark.
"Mick Keily" wrote:

> Hello there,
> I am trying to learn about MS-SQL Server on my own and am faced with a
> problem that requires action quickly, even though I would prefer to take my
> time and dig out the answer from all the documentation available.
> On the web site that I manage, some log files grow to be as large or larger
> than the MDF files themselves, and I am concernced with preserving hard disk
> space for obvious reasons. I asked the web hosting company's tech support
> department for a way to reduce the size of these log files, and they pointed
> me to this suggestion:
> DUMP TRANSACTION database WITH NO_LOG
> While I have used this command to reduce the log file size successfully, I
> am concerned that I am actually truncating data that has not been committed
> to the database yet. Am I worrying needlessly? Or am I actually losing data
> when I run this command?
> I have observed that when I run this query from the SQL Query Analyzer that
> the log file does not instantly get smaller in size. Rather it gets
> substantially smaller a few hours later. The MDF file is about 5.3 GB and
> the LDF file is about 9 GB.
> Thank you for whatever help you can offer.
> Sincerely,
> Mr. Mixter