We use a Patch Management program, Shavlik NetCheck Protect, and the required
database is SQL 2K5. I'm using SQL 2K5 Ent SP2 with latest patches.

The way I understand things to work is that, during use, the logs will grow
in size, until they are written or committed to the actual database, then the
return to (near) initial size?

I understood that running a backup (or sheduling a maintenance plan that
includes a backup among other things) would do this, so the net result would
be that, although the MDF file may get bigger, the LDF file would get smaller?

In my case, both files seem to grow, with the LDF file growing
astronomically large, larger than the MDF file (MDF-1.4 GB, LDF 2.8 GB). The
product, of course, gets slower and slower. I finally end up deleting all
entries that the product makes to the database (using a tool in the product
to delete older scans, not by using SQL or anything, directly).

I presume something is wrong, or, I'm doing something wrong, but, I'm not
sure what. I have a maintenance plan that runs daily, that does pretty much
all the default assigned tasks, including backup (Full), yet, the logs get
huge.

Thanks.

Hello Bill,

Transaction Log file (the *.ldf) would not be truncated by itself. It will
get bigger by time and if you do not act against it, it may even fill up
your disk.

To truncate the passive virtual logs in your Transaction Log file, you
should back it up using Transaction Log Backup.
See BACKUP in Books Online
(http://msdn2.microsoft.com/en-us/library/ms189826.aspx)

And this truncation will not shrink the file size by itself. It just makes
room in the Trsansaction Log file. To make this file smaller, you can erase
those rooms so that you'll have a smaller file in size. However, that file
will demand more space and make itself bigger by time. Which means, it's
like a living thing and it grows. This is quite normal. As much as you
perform operations against your database, this file will grow that much. Of
course this depends on the Recovery Model you use for your database. If you
use Full Recovery Model, then every possible operation will be logged. Which
is good for almost all production OLTP SQL Server databases. You also should
take a look at the Recovery Models from the Books Online.

And you may want to take a look at Tibor's article about this Truncation and
Shirinkage. Then you'll see why you should not perform this action anytime,
anywhere.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Ekrem Önsoy



"Bill Bradley" <BillBradley@discussions.microsoft.com> wrote in message
news38C1096-96CB-4742-A8AD-454B7D5F5BBE@microsoft.com...
> We use a Patch Management program, Shavlik NetCheck Protect, and the
> required
> database is SQL 2K5. I'm using SQL 2K5 Ent SP2 with latest patches.
>
> The way I understand things to work is that, during use, the logs will
> grow
> in size, until they are written or committed to the actual database, then
> the
> return to (near) initial size?
>
> I understood that running a backup (or sheduling a maintenance plan that
> includes a backup among other things) would do this, so the net result
> would
> be that, although the MDF file may get bigger, the LDF file would get
> smaller?
>
> In my case, both files seem to grow, with the LDF file growing
> astronomically large, larger than the MDF file (MDF-1.4 GB, LDF 2.8 GB).
> The
> product, of course, gets slower and slower. I finally end up deleting all
> entries that the product makes to the database (using a tool in the
> product
> to delete older scans, not by using SQL or anything, directly).
>
> I presume something is wrong, or, I'm doing something wrong, but, I'm not
> sure what. I have a maintenance plan that runs daily, that does pretty
> much
> all the default assigned tasks, including backup (Full), yet, the logs get
> huge.
>
> Thanks.

> The way I understand things to work is that, during use, the logs will grow
> in size, until they are written or committed to the actual database, then the
> return to (near) initial size?

No, that is not how it works. The log file will grow if there is not enough
room to accommodate the current transactions. Transactions will stay in the
log until you back up the database or back up the log. When transactions
are cleared from the log, the system doesn't automatically shrink the file
for you, because if it needed to be that big once, it will need to be that
big again. Since grow and shrink actions are relatively expensive, you
should try to avoid them if possible... If you try to keep the log as small
as possible, then an end user (or a group of users) is going to pay the
price, since they will need to wait for a growth event before their action
can be committed. Instead, leave the log file as big as you anticipate it
will need to be, and resign yourself to the fact that it requires space. An
exception is when you do abnormal maintenance, e.g. a large data load or
purge, or a massive reindex, that causes the log to grow larger than typical
day-to-day activity would have done. Read this article for more
information:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> In my case, both files seem to grow, with the LDF file growing
> astronomically large, larger than the MDF file (MDF-1.4 GB, LDF 2.8 GB). The
> product, of course, gets slower and slower.

Are you backing up the database? How often? What recovery model are you
in? If full, how often are you backing up the log? Are you backing up the
log? What is your tolerance for data loss? E.g. If you are backing up the
database once a day at 10:00 PM, and you do not back up the log throughout
the day, then if your database hits the fan, you will need to restore to
10:00 PM last night and you will have lost everything that has happened
since.

A