Re: Virtual SQL Server Transaction Log Auto-Grow problem.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Sezgin Rafed (anonymous_at_anonymous.com)
Date: 11/10/04 

Date: Wed, 10 Nov 2004 08:57:07 +0200

Hi Michael,

It appears that SQL Server cannot increase the size of the Transaction Log
and because the log space used percentage is nearly 100% normal operations
cannot be carried out.
I am not sure, but it seems that this only happens after it grows beyond
certain size(let's say 20-30 GB).
Restarting SQL Server causes the expected increase of 10% in the Transaction
Log, so because now there is free space in it normal operation is resumed.
Changing the Recovery model to Simple also restores normal operation mode,
because the Transaction Log is no longer full.
During normal operation we take regular backups and don't experience any
problems. The project using the database is offline at the moment, so I had
disabled the backups. I am not sure why the Transaction Log had grown so
large, I am monitoring it now.
Simple Recovery Model is not acceptable - normaly Transaction Log backup is
taken every half an hour.

Regards.

Sezgin Rafed

Relevant Pages

  • Re: DELETING 100 million from a table weekly SQl SERVER 2000
    ... TRUNCATE command to avoid using log space. ... you run the risk that SQL Server ... >DELETING 100 million from a table weekly SQl SERVER 2000 ... >Performance and transaction log are the issues we are facing. ...
    (microsoft.public.sqlserver)
  • Re: Transaction log and tempdb
    ... - Is it possible to reduce the size of the transaction log fil during ... an execution? ... It sounds as if you are the database is in full recovery. ... If I execute that script sql server seems ...
    (comp.databases.ms-sqlserver)
  • Re: Transaction log problems
    ... IF you have to shrink a database or file it ... sure that you get that database backup on tape or to another machine. ... > We are using SQL Server 2000 on Windows Server 2003 Standard Edition, ... > One of the databases has recently begun showing large growth of the> transaction log and it is causing us some problems. ...
    (microsoft.public.sqlserver.server)
  • Re: Optimizing massive update to large table
    ... In SQL Server, everything is logged into the ... transaction log before it gets written into the data portion of the database. ... > channel based raid system. ...
    (microsoft.public.sqlserver.server)
  • Re: Size of SQL server logs
    ... determing the correct size of the transaction log. ... hit the max size an error message occurs in the Sql server logs. ... sql server log files so they don't eat up all my free space on the c-drive. ... > Mike Epprecht, Microsoft SQL Server MVP ...
    (microsoft.public.sqlserver.server)