check sql licence: checkpointing

Showing posts with label checkpointing. Show all posts

Wednesday, March 7, 2012

Checkpointing Not Happening in Simple Recovery Model

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?
MichelleThis 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|||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
>|||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
> >
> >
>|||Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN for
the databases in question. If you are seeing a long-running transaction, it
will identify the SPID for it, as well as the date/time it started.
--
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:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
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
> >
> >
>|||I guess that's my point. If I have open transactions, checkpoint shouldn't
help me because they'll stay in the log and take up space. BUT, when I issue
a checkpoint, the space is freed - leading me to believe that the log is NOT
full of open transactions but full of committed transactions. Yet, the logs
are becoming well over 70% full (or were until we started issuing regular
checkpoints). I'm not coming up with any open transactions, either.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN
for
> the databases in question. If you are seeing a long-running transaction,
it
> will identify the SPID for it, as well as the date/time it started.
> --
> 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:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> 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
> > >
> > >
> >
> >
>|||Have you run:
sp_configure "recovery interval (min)"
If this has changed from the default, then that can have an influence on
checkpointing - and thus the amount of used space in your logs.
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:%2312oIES6EHA.3840@.tk2msftngp13.phx.gbl...
I guess that's my point. If I have open transactions, checkpoint shouldn't
help me because they'll stay in the log and take up space. BUT, when I issue
a checkpoint, the space is freed - leading me to believe that the log is NOT
full of open transactions but full of committed transactions. Yet, the logs
are becoming well over 70% full (or were until we started issuing regular
checkpoints). I'm not coming up with any open transactions, either.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN
for
> the databases in question. If you are seeing a long-running transaction,
it
> will identify the SPID for it, as well as the date/time it started.
> --
> 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:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> 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
> > >
> > >
> >
> >
>|||I know that we talked about looking into changing this to see if it would
make a difference but it looks like we're still using the default settings
for this:
name minimum maximum config_value
run_value
recovery interval (min) 0 32767 0
0
Right?
Thanks - Michelle
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O1cBGMS6EHA.796@.TK2MSFTNGP09.phx.gbl...
> Have you run:
> sp_configure "recovery interval (min)"
> If this has changed from the default, then that can have an influence on
> checkpointing - and thus the amount of used space in your logs.
>
> --
> 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:%2312oIES6EHA.3840@.tk2msftngp13.phx.gbl...
> I guess that's my point. If I have open transactions, checkpoint shouldn't
> help me because they'll stay in the log and take up space. BUT, when I
issue
> a checkpoint, the space is freed - leading me to believe that the log is
NOT
> full of open transactions but full of committed transactions. Yet, the
logs
> are becoming well over 70% full (or were until we started issuing regular
> checkpoints). I'm not coming up with any open transactions, either.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN
> for
> > the databases in question. If you are seeing a long-running
transaction,
> it
> > will identify the SPID for it, as well as the date/time it started.
> >
> > --
> > 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:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > 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
> > > >
> > > >
> > >
> > >
> >
> >
>|||Well, this is odd. With the default values here, you shouldn't be
experiencing what you have. You could try experimenting with low values -
like 1, 2 or 5 - and see if that gets things under control.
--
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:udRdWoS6EHA.3856@.tk2msftngp13.phx.gbl...
I know that we talked about looking into changing this to see if it would
make a difference but it looks like we're still using the default settings
for this:
name minimum maximum config_value
run_value
recovery interval (min) 0 32767 0
0
Right?
Thanks - Michelle
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O1cBGMS6EHA.796@.TK2MSFTNGP09.phx.gbl...
> Have you run:
> sp_configure "recovery interval (min)"
> If this has changed from the default, then that can have an influence on
> checkpointing - and thus the amount of used space in your logs.
>
> --
> 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:%2312oIES6EHA.3840@.tk2msftngp13.phx.gbl...
> I guess that's my point. If I have open transactions, checkpoint shouldn't
> help me because they'll stay in the log and take up space. BUT, when I
issue
> a checkpoint, the space is freed - leading me to believe that the log is
NOT
> full of open transactions but full of committed transactions. Yet, the
logs
> are becoming well over 70% full (or were until we started issuing regular
> checkpoints). I'm not coming up with any open transactions, either.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN
> for
> > the databases in question. If you are seeing a long-running
transaction,
> it
> > will identify the SPID for it, as well as the date/time it started.
> >
> > --
> > 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:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > 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
> > > >
> > > >
> > >
> > >
> >
> >
>|||We'll give this a try after the weekend - don't want to make trouble over
the Christmas Holiday - :)
I'll report back with the results.
Thanks for your help!
Michelle
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%233MfXsS6EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Well, this is odd. With the default values here, you shouldn't be
> experiencing what you have. You could try experimenting with low values -
> like 1, 2 or 5 - and see if that gets things under control.
> --
> 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:udRdWoS6EHA.3856@.tk2msftngp13.phx.gbl...
> I know that we talked about looking into changing this to see if it would
> make a difference but it looks like we're still using the default settings
> for this:
> name minimum maximum config_value
> run_value
> recovery interval (min) 0 32767 0
> 0
> Right?
> Thanks - Michelle
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O1cBGMS6EHA.796@.TK2MSFTNGP09.phx.gbl...
> > Have you run:
> >
> > sp_configure "recovery interval (min)"
> >
> > If this has changed from the default, then that can have an influence on
> > checkpointing - and thus the amount of used space in your logs.
> >
> >
> > --
> > 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:%2312oIES6EHA.3840@.tk2msftngp13.phx.gbl...
> > I guess that's my point. If I have open transactions, checkpoint
shouldn't
> > help me because they'll stay in the log and take up space. BUT, when I
> issue
> > a checkpoint, the space is freed - leading me to believe that the log is
> NOT
> > full of open transactions but full of committed transactions. Yet, the
> logs
> > are becoming well over 70% full (or were until we started issuing
regular
> > checkpoints). I'm not coming up with any open transactions, either.
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > > Checkpoint isn't gonna buy you anything here. Try running DBCC
OPENTRAN
> > for
> > > the databases in question. If you are seeing a long-running
> transaction,
> > it
> > > will identify the SPID for it, as well as the date/time it started.
> > >
> > > --
> > > 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:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Are you sure that you have simple recovery model selected for tempdb?
Regards,
Daniel
"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
>|||You cannot set the recovery model in tempdb.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
Are you sure that you have simple recovery model selected for tempdb?
Regards,
Daniel
"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
>|||Yes, you are right, I read that Michelle is writing about logs not log, is
there possibility that she looks at transaction logs on databases with full
or bulk logged recovery model?
Regards,
Daniel
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> You cannot set the recovery model in tempdb.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Are you sure that you have simple recovery model selected for tempdb?
> Regards,
> Daniel
> "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
> >
> >
>|||She says that she has simple recovery and that one of them is tempdb. Maybe
the next thing we should look at is doing sp_helpdb.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:uevSRcu6EHA.2012@.TK2MSFTNGP15.phx.gbl...
Yes, you are right, I read that Michelle is writing about logs not log, is
there possibility that she looks at transaction logs on databases with full
or bulk logged recovery model?
Regards,
Daniel
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> You cannot set the recovery model in tempdb.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Are you sure that you have simple recovery model selected for tempdb?
> Regards,
> Daniel
> "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
> >
> >
>|||I think so,
also I think next line will go straight.
select name,databasepropertyex(name,'recovery') model from
master.dbo.sysdatabases
Regards,
Daniel
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OqkKwr06EHA.2488@.TK2MSFTNGP14.phx.gbl...
> She says that she has simple recovery and that one of them is tempdb.
Maybe
> the next thing we should look at is doing sp_helpdb.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:uevSRcu6EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Yes, you are right, I read that Michelle is writing about logs not log, is
> there possibility that she looks at transaction logs on databases with
full
> or bulk logged recovery model?
> Regards,
> Daniel
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> > You cannot set the recovery model in tempdb.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> > news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > Are you sure that you have simple recovery model selected for tempdb?
> >
> > Regards,
> > Daniel
> >
> > "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
> > >
> > >
> >
> >
>|||Now all we need are the results...
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:u6RXHH66EHA.3368@.TK2MSFTNGP10.phx.gbl...
I think so,
also I think next line will go straight.
select name,databasepropertyex(name,'recovery') model from
master.dbo.sysdatabases
Regards,
Daniel
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OqkKwr06EHA.2488@.TK2MSFTNGP14.phx.gbl...
> She says that she has simple recovery and that one of them is tempdb.
Maybe
> the next thing we should look at is doing sp_helpdb.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:uevSRcu6EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Yes, you are right, I read that Michelle is writing about logs not log, is
> there possibility that she looks at transaction logs on databases with
full
> or bulk logged recovery model?
> Regards,
> Daniel
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> > You cannot set the recovery model in tempdb.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> > news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > Are you sure that you have simple recovery model selected for tempdb?
> >
> > Regards,
> > Daniel
> >
> > "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
> > >
> > >
> >
> >
>|||Yes, I am looking at databases set to simple:
distribution SIMPLE
master SIMPLE
tempdb SIMPLE
sqlprofile SIMPLE
msdb SIMPLE
pubs SIMPLE
Northwind SIMPLE
PERFMON SIMPLE
When we're fully-staffed again tomorrow, we'll look into stopping the
15-minute checkpoints and altering the recovery interval.
Michelle
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uXBQ3Q66EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Now all we need are the results...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:u6RXHH66EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I think so,
> also I think next line will go straight.
> select name,databasepropertyex(name,'recovery') model from
> master.dbo.sysdatabases
> Regards,
> Daniel
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OqkKwr06EHA.2488@.TK2MSFTNGP14.phx.gbl...
> > She says that she has simple recovery and that one of them is tempdb.
> Maybe
> > the next thing we should look at is doing sp_helpdb.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> > .
> > "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> > news:uevSRcu6EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > Yes, you are right, I read that Michelle is writing about logs not log,
is
> > there possibility that she looks at transaction logs on databases with
> full
> > or bulk logged recovery model?
> >
> > Regards,
> > Daniel
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> > > You cannot set the recovery model in tempdb.
> > >
> > > --
> > > Tom
> > >
> > > ---
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinnaclepublishing.com
> > >
> > >
> > > "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in
message
> > > news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > > Are you sure that you have simple recovery model selected for tempdb?
> > >
> > > Regards,
> > > Daniel
> > >
> > > "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
> > > >
> > > >
> > >
> > >
> >
> >
>

Checkpointing Not Happening in Simple Recovery Model

I'll report back with the results.
Thanks for your help!
Michelle
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%233MfXsS6EHA.3124@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Well, this is odd. With the default values here, you shouldn't be
> experiencing what you have. You could try experimenting with low values -
> like 1, 2 or 5 - and see if that gets things under control.
> --
> 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:udRdWoS6EHA.3856@.tk2msftngp13.phx.gbl...
> I know that we talked about looking into changing this to see if it would
> make a difference but it looks like we're still using the default settings
> for this:
> name minimum maximum config_value
> run_value
> recovery interval (min) 0 32767 0
> 0
> Right?
> Thanks - Michelle
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O1cBGMS6EHA.796@.TK2MSFTNGP09.phx.gbl...
shouldn't[vbcol=seagreen]
> issue
> NOT
> logs
regular[vbcol=seagreen]
OPENTRAN[vbcol=seagreen]
> transaction,
> space
in[vbcol=seagreen]
a[vbcol=seagreen]
these[vbcol=seagreen]
then[vbcol=seagreen]
fast[vbcol=seagreen]
> truncated
> problematic.
to[vbcol=seagreen]
> database
> database
checkpoint -[vbcol=seagreen]
> it
> to
checkpoint
>

Saturday, February 25, 2012

Checkpoint and performance

While profiling our stored procs that write data to the database, we notice
that while the database is checkpointing, the performance of these stored
procs gets worse. What usually will takes 100 ms takes 400 to 700 ms. Once
the checkpoint is done, they are back to their normal runtime
Is this expected behavior?
BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running Windows
Server 2003/SQL Server 2000 Enterprise SP3a.With direct attached SCSI I saw the same behavior. Basically, your
checkpoint is saturating the IO bandwidth of your controller card. I saw
this with high-end RAID controllers with 128MB RAM when the checkpoint was
large enough to flood the on-board cache. This went away with a SAN since
FC is full duplex and the SAN has 3GB of write cache, easily enough to hold
a checkpoint.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> While profiling our stored procs that write data to the database, we
notice
> that while the database is checkpointing, the performance of these stored
> procs gets worse. What usually will takes 100 ms takes 400 to 700 ms.
Once
> the checkpoint is done, they are back to their normal runtime
> Is this expected behavior?
> BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running Windows
> Server 2003/SQL Server 2000 Enterprise SP3a.
>|||I forgot to add we are using attached to an EMC storage system with 1
terabyte of storage... The controller card is fiber to the EMC system and
the sustained throughput during a checkpoint is 8 MBps and the that is
nothing compared to what we can actually push to that system. During
database restores we can get over 50 MBps!!! So while it may be an I/O
issue, we are not even reaching the saturation point of our attached I/O
system.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> With direct attached SCSI I saw the same behavior. Basically, your
> checkpoint is saturating the IO bandwidth of your controller card. I saw
> this with high-end RAID controllers with 128MB RAM when the checkpoint was
> large enough to flood the on-board cache. This went away with a SAN since
> FC is full duplex and the SAN has 3GB of write cache, easily enough to
hold
> a checkpoint.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
>
> "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > While profiling our stored procs that write data to the database, we
> notice
> > that while the database is checkpointing, the performance of these
stored
> > procs gets worse. What usually will takes 100 ms takes 400 to 700 ms.
> Once
> > the checkpoint is done, they are back to their normal runtime
> >
> > Is this expected behavior?
> >
> > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running Windows
> > Server 2003/SQL Server 2000 Enterprise SP3a.
> >
> >
>|||I am also using an EMC SAN (CX-600) with 4 fiber cards from each cluster
host to the SAN. The real key is write cache. Mine is set to max at 3GB.
If you overfill your write cache, you go to direct write mode and lose a lot
of the benefits of your high-end IO system.
There may be CPU issues as well. Have you monitored processor time on a
per-CPU basis? Also, do you have Hyperthreading on or off?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Kevin Jackson" <softwiz@.covad.net> wrote in message
news:u6qIPUquDHA.2308@.TK2MSFTNGP11.phx.gbl...
> I forgot to add we are using attached to an EMC storage system with 1
> terabyte of storage... The controller card is fiber to the EMC system and
> the sustained throughput during a checkpoint is 8 MBps and the that is
> nothing compared to what we can actually push to that system. During
> database restores we can get over 50 MBps!!! So while it may be an I/O
> issue, we are not even reaching the saturation point of our attached I/O
> system.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> > With direct attached SCSI I saw the same behavior. Basically, your
> > checkpoint is saturating the IO bandwidth of your controller card. I
saw
> > this with high-end RAID controllers with 128MB RAM when the checkpoint
was
> > large enough to flood the on-board cache. This went away with a SAN
since
> > FC is full duplex and the SAN has 3GB of write cache, easily enough to
> hold
> > a checkpoint.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> >
> > "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> > news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > > While profiling our stored procs that write data to the database, we
> > notice
> > > that while the database is checkpointing, the performance of these
> stored
> > > procs gets worse. What usually will takes 100 ms takes 400 to 700 ms.
> > Once
> > > the checkpoint is done, they are back to their normal runtime
> > >
> > > Is this expected behavior?
> > >
> > > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running
Windows
> > > Server 2003/SQL Server 2000 Enterprise SP3a.
> > >
> > >
> >
> >
>|||I have seen minimally configured SAN systems saturate at around 50MB/sec.
are you seeing high disk queue lengths? if so, then how much bandwidth do
you have to the storage subsystem? How many spindles, what raid level, how
much write cache?
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kevin Jackson" <softwiz@.covad.net> wrote in message
news:u6qIPUquDHA.2308@.TK2MSFTNGP11.phx.gbl...
> I forgot to add we are using attached to an EMC storage system with 1
> terabyte of storage... The controller card is fiber to the EMC system and
> the sustained throughput during a checkpoint is 8 MBps and the that is
> nothing compared to what we can actually push to that system. During
> database restores we can get over 50 MBps!!! So while it may be an I/O
> issue, we are not even reaching the saturation point of our attached I/O
> system.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> > With direct attached SCSI I saw the same behavior. Basically, your
> > checkpoint is saturating the IO bandwidth of your controller card. I
saw
> > this with high-end RAID controllers with 128MB RAM when the checkpoint
was
> > large enough to flood the on-board cache. This went away with a SAN
since
> > FC is full duplex and the SAN has 3GB of write cache, easily enough to
> hold
> > a checkpoint.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> >
> > "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> > news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > > While profiling our stored procs that write data to the database, we
> > notice
> > > that while the database is checkpointing, the performance of these
> stored
> > > procs gets worse. What usually will takes 100 ms takes 400 to 700 ms.
> > Once
> > > the checkpoint is done, they are back to their normal runtime
> > >
> > > Is this expected behavior?
> > >
> > > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running
Windows
> > > Server 2003/SQL Server 2000 Enterprise SP3a.
> > >
> > >
> >
> >
>|||Very short disk queue lengths.
4 ea FC2 HBAs per host computer. Theoretical max 800MB/sec. Box maxes out
at 1100 MB/sec manufacturer's spec. Real limit seems to be aroudn
200MB/sec.
Most RAID sets are 16 spindles RAID 1+0 Some are fewer spindles but all are
1+0.
Total system has 3GB write cache.
Front end is 4-node 3 instance cluster (Windows Server 2003) 8x2.8GHz procs
and 32GB RAM /box. (Unisys ES-7000)
Obviously NOT a minimally configured system. I was able to get 50MB/sec
from direct attached SCSI. Again, the problem I ran into was cache
saturation and half-duplex data pathing.
Then again, there is the flip-side problem where the IO system can be too
fast.
FIX: The Checkpoint Process Can Delay SQL Server Database Activity and Does
Not Yield Scheduler Correctly Causing Error: 17883 to Occur
http://support.microsoft.com/default.aspx?scid=kb;en-us;815056&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:u2X1wUsuDHA.1512@.TK2MSFTNGP10.phx.gbl...
> I have seen minimally configured SAN systems saturate at around 50MB/sec.
> are you seeing high disk queue lengths? if so, then how much bandwidth do
> you have to the storage subsystem? How many spindles, what raid level,
how
> much write cache?
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kevin Jackson" <softwiz@.covad.net> wrote in message
> news:u6qIPUquDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > I forgot to add we are using attached to an EMC storage system with 1
> > terabyte of storage... The controller card is fiber to the EMC system
and
> > the sustained throughput during a checkpoint is 8 MBps and the that is
> > nothing compared to what we can actually push to that system. During
> > database restores we can get over 50 MBps!!! So while it may be an I/O
> > issue, we are not even reaching the saturation point of our attached I/O
> > system.
> >
> > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> > > With direct attached SCSI I saw the same behavior. Basically, your
> > > checkpoint is saturating the IO bandwidth of your controller card. I
> saw
> > > this with high-end RAID controllers with 128MB RAM when the checkpoint
> was
> > > large enough to flood the on-board cache. This went away with a SAN
> since
> > > FC is full duplex and the SAN has 3GB of write cache, easily enough to
> > hold
> > > a checkpoint.
> > >
> > > --
> > > Geoff N. Hiten
> > > Microsoft SQL Server MVP
> > > Senior Database Administrator
> > > Careerbuilder.com
> > >
> > >
> > >
> > >
> > > "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> > > news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > > > While profiling our stored procs that write data to the database, we
> > > notice
> > > > that while the database is checkpointing, the performance of these
> > stored
> > > > procs gets worse. What usually will takes 100 ms takes 400 to 700
ms.
> > > Once
> > > > the checkpoint is done, they are back to their normal runtime
> > > >
> > > > Is this expected behavior?
> > > >
> > > > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running
> Windows
> > > > Server 2003/SQL Server 2000 Enterprise SP3a.
> > > >
> > > >
> > >
> > >
> >
> >
>|||16 spindles is not very much - 200MB/sec is actually really quite good for
16 15KRPM spindles.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:Os3suQ1uDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Very short disk queue lengths.
> 4 ea FC2 HBAs per host computer. Theoretical max 800MB/sec. Box maxes
out
> at 1100 MB/sec manufacturer's spec. Real limit seems to be aroudn
> 200MB/sec.
> Most RAID sets are 16 spindles RAID 1+0 Some are fewer spindles but all
are
> 1+0.
> Total system has 3GB write cache.
> Front end is 4-node 3 instance cluster (Windows Server 2003) 8x2.8GHz
procs
> and 32GB RAM /box. (Unisys ES-7000)
> Obviously NOT a minimally configured system. I was able to get 50MB/sec
> from direct attached SCSI. Again, the problem I ran into was cache
> saturation and half-duplex data pathing.
> Then again, there is the flip-side problem where the IO system can be too
> fast.
> FIX: The Checkpoint Process Can Delay SQL Server Database Activity and
Does
> Not Yield Scheduler Correctly Causing Error: 17883 to Occur
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;815056&Product=sql2k
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:u2X1wUsuDHA.1512@.TK2MSFTNGP10.phx.gbl...
> > I have seen minimally configured SAN systems saturate at around
50MB/sec.
> > are you seeing high disk queue lengths? if so, then how much bandwidth
do
> > you have to the storage subsystem? How many spindles, what raid level,
> how
> > much write cache?
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kevin Jackson" <softwiz@.covad.net> wrote in message
> > news:u6qIPUquDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > > I forgot to add we are using attached to an EMC storage system with 1
> > > terabyte of storage... The controller card is fiber to the EMC system
> and
> > > the sustained throughput during a checkpoint is 8 MBps and the that is
> > > nothing compared to what we can actually push to that system. During
> > > database restores we can get over 50 MBps!!! So while it may be an
I/O
> > > issue, we are not even reaching the saturation point of our attached
I/O
> > > system.
> > >
> > > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > > news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> > > > With direct attached SCSI I saw the same behavior. Basically, your
> > > > checkpoint is saturating the IO bandwidth of your controller card.
I
> > saw
> > > > this with high-end RAID controllers with 128MB RAM when the
checkpoint
> > was
> > > > large enough to flood the on-board cache. This went away with a SAN
> > since
> > > > FC is full duplex and the SAN has 3GB of write cache, easily enough
to
> > > hold
> > > > a checkpoint.
> > > >
> > > > --
> > > > Geoff N. Hiten
> > > > Microsoft SQL Server MVP
> > > > Senior Database Administrator
> > > > Careerbuilder.com
> > > >
> > > >
> > > >
> > > >
> > > > "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> > > > news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > > > > While profiling our stored procs that write data to the database,
we
> > > > notice
> > > > > that while the database is checkpointing, the performance of these
> > > stored
> > > > > procs gets worse. What usually will takes 100 ms takes 400 to 700
> ms.
> > > > Once
> > > > > the checkpoint is done, they are back to their normal runtime
> > > > >
> > > > > Is this expected behavior?
> > > > >
> > > > > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running
> > Windows
> > > > > Server 2003/SQL Server 2000 Enterprise SP3a.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Wednesday, March 7, 2012

Checkpointing Not Happening in Simple Recovery Model

Checkpointing Not Happening in Simple Recovery Model

Saturday, February 25, 2012

Checkpoint and performance

check sql licence

Blog Archive

About Me