Re: Checkpointing Not Happening in Simple Recovery Model
From: michelle (michelle_at_nospam.com)
Date: 12/23/04
- Next message: DBA72: "restoring filegroup from different date than mdf file"
- Previous message: Subbaiahd: "Re: sql server 2005 availability"
- In reply to: Mike Epprecht \(SQL MVP\): "Re: Checkpointing Not Happening in Simple Recovery Model"
- Next in thread: Tom Moreau: "Re: Checkpointing Not Happening in Simple Recovery Model"
- Reply: Tom Moreau: "Re: Checkpointing Not Happening in Simple Recovery Model"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 23 Dec 2004 11:06:38 -0600
I appreciate that two people have pointed to long-running transactions
(perhaps transactions left 'open' that never commit?).
But, would I then be able to issue a checkpoint and recover the free space
or wouldn't these long-running transactions still just keep the space in the
log? If there are transactions still open, I would think that issuing a
checkpoint statement manually would not do any good. Maybe I'm wrong.
Please note that depending on how much space we have allocated to these
logs, it can take days to fill it up. For example, tempdb would go for
several days (and the space used in the log would keep growing and growing)
until it would finally get full. It didn't seem like anything would then
roll back - I waited 45 minutes one day (server is pretty powerful, fast
disks on SAN, 4 GB RAM, 2 HT cpus).
"Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
news:eKX$p%23Q6EHA.2568@TK2MSFTNGP11.phx.gbl...
> Do you have long running transactions? If so, the log can't be truncated
> until you either commit or roll back.
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: mike@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "michelle" <michelle@nospam.com> wrote in message
> news:#FjEC4Q6EHA.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
> >
> >
>
>
- Next message: DBA72: "restoring filegroup from different date than mdf file"
- Previous message: Subbaiahd: "Re: sql server 2005 availability"
- In reply to: Mike Epprecht \(SQL MVP\): "Re: Checkpointing Not Happening in Simple Recovery Model"
- Next in thread: Tom Moreau: "Re: Checkpointing Not Happening in Simple Recovery Model"
- Reply: Tom Moreau: "Re: Checkpointing Not Happening in Simple Recovery Model"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
