Re: Checkpointing Not Happening in Simple Recovery Model

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 12/23/04 

Date: Thu, 23 Dec 2004 11:46:02 -0500

This sounds more like the result of long-running transactions, e.g.:

begin tran

-- a whole bunch of statements

commit tran

The log cannot be truncated beyond the first open transaction. 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"michelle" <michelle@nospam.com> wrote in message
news:%23FjEC4Q6EHA.260@TK2MSFTNGP10.phx.gbl...
The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
We have at least two databases on this server in simple recovery model. Of
course, one of these databases is tempdb so this is very problematic.
The transaction logs just keep filling up and filling up, growing to max,
and finally become full. We would then have to issue an alter database
statement to increase the size of the log. Although an alter database
statement is one of those things that should trigger a checkpoint - it does
not clear out the space used in the log file. So, once we are able to get a
bit of free space, we can manually issue a checkpoint.
We have incorporated a checkpoint to run every 15 minutes. We also have an
alert that will catch a log at 80% full and then issues a checkpoint on that
database. But, we want to figure out what is going on and what is causing
this.
Any ideas?
Michelle

Relevant Pages

  • RE: Foreign Key Violations During Insert in Transaction
    ... parent row into the database, SQL Server generates a new identity value. ... that value does really exist even before you commit the transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: statement not allowed within multi-statement transaction (Long)
    ... So if I do have an open transaction I can't figure ... > 100 Database does not exist in sysdatabases can not dettach ... > --Verify database Exiists for detach ... >> Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: SQLNCLI is not allowing CREATE DATABASE inside a TRansaction
    ... successfully call CREATE DATABASE. ... CREATE DATABASE statement not allowed within multi-statement transaction. ... open a second connection to SQL Server behind the back. ... I can post that some test program using JDBC ...
    (microsoft.public.data.oledb)
  • Re: Insert Into Without Log
    ... level of logging of logged in the database ... transaction log, however, you can minimize this via setting the database ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Help - Timing Logic
    ... server application, both of which ran on the same box. ... the client applications and 'lodging' them in the database. ... Another part of the server application was dedicated to retrieving messages ... commit transaction ...
    (microsoft.public.dotnet.languages.vb)
Loading