RE: SPS 2003 transaction log question

We met this problem before. :)

It is a SQL question, Not SPS question. here are three approaches to help
you truncate the sql transaction logs

http://leyton.org/techiemusings/2005/06/08/truncating-sql-server-transaction-logs/

"
In Enterprise manager, set the recovery model of the database to “Simple”. A
Simple recovery model ensures that when every checkpoint occurs the
transaction log is truncated down. As transactions occur, the log fills up,
and it’s only when a transaction is either committed or rolled back that the
space can be freed. This is directly analogous to the Sybase setting “trunc.
log on chkpt.” (Truncate log on checkpoint), and can be achieved through use
of the ’sp_dboption’ stored procedure.

Run the direct SQL operation “dump tran dbname with truncate_only” in Query
Analyser. This truncates the transaction log. In some situations you may need
to change truncate_only to no_log, which is slightly more restricted in what
it does.

If a database is, for some reason, being created and dropped frequently, or
that you want to prevent the problem occurring for new databases, change the
‘model’ database properties to “Simple”. The model database is just that, a
model by which other databases are built (including tempdb). Any new database
therefore “inherits” the properties, sizes and contents of the model
database. Setting it to a simple recovery model will ensure new databases are
created with an automatically truncating transaction log.


"

br

ted


"anandk" wrote:

> Hi all. I am a newbie with SPS2003. I have run into a bit of a problem.
> Since I was too ignorant to understand the maintenance of the portal and the
> MS SQL Server maintenance as well, I now have a SITE database which is 62 GB
> and the associated SITE log file which is 134GB! I need to somehow SAFELY
> reduce the size of the log file before I even try to backup the whole lot. I
> suspect that the 134 + 62 GB of total space to backup will take far too long
> for our users to deal with. Can anyone please help me out with some steps on
> how I can decrease the log file size significantly? Then, I think I can
> backup the entire portal using the SPS Admin tools.
>
> Any help is appreciated. All in all, SPS is working well for us.
> Unfortunately, I am not an SPS or SQL professional, but that task is now on
> my head. I wish I would have realized that the size of the transaction log
> was getting quite large much earlier on. Well, I am learning.
>
> I am reading up on SQL server and truncating logs, but as I have NO
> experience with this, I want to ask someone who knows about this before I do
> anything.
>
> Thanks for any help.
>
> -Anand
.

Relevant Pages

  • Re: Help---Run-Time error
    ... Here the solution seems to have been to install SQL Server 2005 SP2 and make sure to install SP2 for the client tools as well, ... If you are reloading the database daily should you be running the BULK-LOGGED recovery model for this database? ... Also, if I update the current date column on a million rows, the database will not change size, but the transaction log will now grow by two million rows plus overhead.) ...
    (microsoft.public.sqlserver.odbc)
  • RE: Backup in maintenance plan
    ... I selected "Back up the transaction log of the database as part of the ... To ensure an efficient and effective backup and recovery strategy for your ... The transaction log should not be backed up if the truncate log on ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction Log Size
    ... We currently use a 3rd party backup agent to backup our SQL databases and ... transaction logs (Commvault Galaxy iDataAgent for SQL) and the database is ... Because we are using a 3rd party agent to backup the database, ... > here are some articles on how to shrink your transaction log. ...
    (microsoft.public.sqlserver.server)
  • Re: Log Shipping Role Reversal
    ... 3101 if There Are Outstanding Transaction Log Backups ... SQL Server MVP ... I run this SQL statement on the primary server: ... > This places the primary database into read-only mode. ...
    (microsoft.public.sqlserver.server)
  • Re: Edit botton does not work
    ... how can we solve this problem to allow SQL start taking inputs ... Can we tune the database? ... The transaction log for database 'databasename' is full. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)