woody splawn

I am using SQL Server 2000 on a stand alone machine. What is the easiest
way to keep my log file small? I would like it to be, at a maximum, only
about 20 % of the size of my database. Right now it is 3 times the size
of my db and I would like to reduce it's size.

woody splawn

OK, I've been doing some reading since my first post. I have determined for
what I am doing Recovery simple is the best plan. I understand all the
caveats. I have set Recovery to Simple for the given database.

In looking at the Transaction log tab of the properties for the database I
see the property of Restrict File Growth (MB). Right now the value is 146.
I would like to change that to a smaller number but when I do and move off
the field, the number reverts back to 146. There is probably still
something fundamental I am missing. Could someone help me?

sue hoegemeier

Are you backing up the log? That's one way to make sure the
size remains reasonable. If you aren't backing up the log
and aren't using simple recovery, it's going to keep
growing.
Another option if you are sure you don't need the log for
your recovery, restore plans is to set the database to
simple recovery model.

-Sue

woody splawn

In Enterprise Manager I Select all tasks, Backup the database. I see that
there is a radio for backing up the transaction log but I can not find a way
to make it active (un-dimmed).

As I mentioned in the post above I am using Simple recovery. That is, I
have a database called Corp and from ****yser I ran the following:

Alter database Corp
SET
RECOVERY SIMPLE

The bottom line is that I have a database of about 50MB and a log file of
about 150MB. I am reading the help files and other do***entation but I it
still is not clear to me what I need to do to get rid of the big log file
and then to fix it so that in the future it doesn't get too big.

Anyone?

sue hoegemeier

The option to backup the log won't be available now that you
have changed the recovery model. That should keep it from
getting to big in the future when not doing log backups. I'd
guess that the issue at this point is just that you need to
shrink the log down to an appropriate size. Refer to the
following:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318

-Sue

woody splawn

I switched the Recovery plan back to Full and the Radio for backing up the
log file became available. I then followed your recommendation with regard
to the knowledgbase artcile and things seem to be working correctly now.

sue hoegemeier

Glad it's working now...thanks for posting back your
results!

-Sue