Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

SPS2003 SQL Transaction Log Question

  Asked By: Marques    Date: Jan 08    Category: Sharepoint    Views: 1450

I recently noticed that I've got 70GB in the SQL Transaction Log for my
Sharepoint 2003 server.

When I backup the server, the whole backup is about 2GB in side.

I assume that the entire transaction log may not be being backed up...
Which, if that's the case, means that I may be able to run a SQL command
to flush some of older transactions...

Would someone out set me straight about what I'm seeing and, if it can
be done, tell me how to compact the DB?



7 Answers Found

Answer #1    Answered By: Caleb Gordon     Answered On: Jan 08

I wrote a blog post about a month ago about fixing slow or non-
responsive full text index searches within team sites that probably
could help you. There is a SQL query in the post that if you
execute will move your transaction  log into a .log file. You can
either save the file or delete it. I will caution you I am not a
DBA this is just the solution that I chose for the problem I was
posed with. Hope it helps


Answer #2    Answered By: Christie Carlson     Answered On: Jan 08

That'll get the transaction  log to another file, effectively flushing
the log  from the database.

The odd thing is that I thought there would be a way to do this without
killing off the entire transaction log.

In my particular case, I set a restriction to the size of the TL, but
that size restriction is actually LESS than the CURRENT size of the log.
So sql  2000 appears to be ignoring that size restriction...


Answer #3    Answered By: Dorothy Farmer     Answered On: Jan 08

I'm not sure how much you know about sql  so come back to me if you need
a step by step guide but, basically, your tl's are growing as they are
never being truncated

You have 2 choices, back up your transaction  logs or don't create them
in the first place

If you don't create them in the first place you can only restore back to
your last database backup

If you do create them and back them up you can do point in time restores
back to the last tl backup

To turn them off right click the db, choose properties and on one of the
tabs you'll see recovery mode is probably set to full, drop down the
combo box and choose simple - this way only minor log  files will be
created (i.e. it'll do circular logging)

Even if you do turn off logging you should take a backup just to reduce
them in size - the easiest way is to go into management and create a
database management plan and on the appropriate page select to back up
the tl logs and truncate them - if you are in full recovery mode you can
set this to run however often you want (some of our db's are set to
daily, some are set to daily, some are set to 5 minutes) - if you have
gone for simple recovery you can just run the job once to truncate the
log back

Answer #4    Answered By: Jacklyn Burnett     Answered On: Jan 08

This covers it rather nicely. I didn't realize that the MS SQL
backup function has specific tasks for dealing with the TL log...

Answer #5    Answered By: Breann Beach     Answered On: Jan 08

Like I said, sql  maintenance is not my specialty. I work on very
large installations of sharepoint so I don't usually perform the
backups and maintainance on the databases. I am sure if you do a
little searching on google on "limiting sql transaction  log size"
or "sql transaction log  cleaning" you will find something. You
might also want to look into the SQL help (I have found going into
query analyzer and using the help within that program is much better
than the one within enterprise manager for SQL 2000).

Answer #6    Answered By: Vinay Thakur     Answered On: Jan 08

In SQL the transaction  log will continue to grow if you do not
perform maintenance on it.
When the transaction log  is backed up it will normally shrink.
However, if it has grown to a very large size you may need to take
more drastic measures.
The steps to follow would be:
1. Perform a full backup of the database to give you a valid
restore point.
2. Execute this command
This will not save the transaction log and will remove all
entries that are not pending.
3. Perform a full backup of the database to give you a valid
restore point following the transaction log truncation.

If the physical size of the transaction log is still large you may
still need to shrink the database at this point.

Open Enterprise Manager, right click on the database, select All
Tasks, select Shrink Database
At the bottom of the popup control select Files
select the log file from the droplist at the top
click ok, then click ok again.

Do a search on Google for "SQL 2000" "Backup transaction
log" "truncate_only" for examples and further information.

Answer #7    Answered By: Ian Powell     Answered On: Jan 08

In addition to this, you may find helpful information here:
www.sharepointblogs.com/.../18789.aspx I had
to figure some of this out while testing upgrade scenarios.. I didn't
need to work with log  files that were growing larger than the databases

Didn't find what you were looking for? Find more on SPS2003 SQL Transaction Log Question Or get search suggestion and latest updates.