How keep log file small -- sql server clients

Woody Splawn
10/1/2003 10:11:45 AM

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?

[quoted text, click to view]

Sue Hoegemeier
10/1/2003 4:01:17 PM

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

On Wed, 1 Oct 2003 08:40:18 -0700, "Woody Splawn"
[quoted text, click to view]

Woody Splawn
10/3/2003 11:13:25 AM

[quoted text, click to view]

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).

[quoted text, click to view]

As I mentioned in the post above I am using Simple recovery. That is, I
have a database called Corp and from analyser 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 documentation 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?

Woody Splawn
10/3/2003 11:57:29 AM

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
10/3/2003 12:41:45 PM

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

On Fri, 3 Oct 2003 11:13:25 -0700, "Woody Splawn"
[quoted text, click to view]

Sue Hoegemeier
10/3/2003 2:14:17 PM

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

-Sue

On Fri, 3 Oct 2003 11:57:29 -0700, "Woody Splawn"
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clients

sql server clients : How keep log file small