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
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
|||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...[vbcol=seagreen]
> 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...
Of[vbcol=seagreen]
max,[vbcol=seagreen]
> does
get[vbcol=seagreen]
> a
an[vbcol=seagreen]
> that
causing
>
|||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...[vbcol=seagreen]
> 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...
Of[vbcol=seagreen]
max,[vbcol=seagreen]
> does
get[vbcol=seagreen]
> a
an[vbcol=seagreen]
> that
causing
>
|||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)[vbcol=seagreen]
> 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...
model.[vbcol=seagreen]
> Of
> max,
> get
have[vbcol=seagreen]
> an
on
> causing
>
|||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)[vbcol=seagreen]
> 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...
model.[vbcol=seagreen]
> Of
> max,
> get
have[vbcol=seagreen]
> an
on
> causing
>
|||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[vbcol=seagreen]
> 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...
> for
transaction,[vbcol=seagreen]
> it
space[vbcol=seagreen]
> the
> growing)
truncated[vbcol=seagreen]
> model.
problematic.[vbcol=seagreen]
database[vbcol=seagreen]
database[vbcol=seagreen]
it[vbcol=seagreen]
to
> have
> on
>
|||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[vbcol=seagreen]
> 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...
> for
transaction,[vbcol=seagreen]
> it
space[vbcol=seagreen]
> the
> growing)
truncated[vbcol=seagreen]
> model.
problematic.[vbcol=seagreen]
database[vbcol=seagreen]
database[vbcol=seagreen]
it[vbcol=seagreen]
to
> have
> on
>
|||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...[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
>
No comments:
Post a Comment