ClassNotFoundException

Hi all,
I am new to java. I have installed SQL Server JDBC Driver SP3 on my
computer.
When I run the statement Class.forName("..."); , I get the
ClassNotFoundException.
I have read the following article
http://support.microsoft.com/default...;en-us;313100.
And I am sure that I have my CLASSPATH variable is like the following:
CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for
JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for
JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for
JDBC\lib\mssqlserver.jar
But the exception still occurs. Even when I copied the 3 jar files into
c:\j2sdk\lib, it also didn't work.
Anyone can help me?
Thank you!
Hi Charlie,
Are you sure that the driver classes are accesible from your calling class,
ie, have you imported the driver package?
Ian
"Charlie Tong" wrote:

> Hi all,
> I am new to java. I have installed SQL Server JDBC Driver SP3 on my
> computer.
> When I run the statement Class.forName("..."); , I get the
> ClassNotFoundException.
> I have read the following article
> http://support.microsoft.com/default...;en-us;313100.
> And I am sure that I have my CLASSPATH variable is like the following:
> CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for
> JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for
> JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for
> JDBC\lib\mssqlserver.jar
> But the exception still occurs. Even when I copied the 3 jar files into
> c:\j2sdk\lib, it also didn't work.
> Anyone can help me?
> Thank you!
>
>
|||It works now.
Thanks Ian!
"Ian555" <Ian555@.discussions.microsoft.com> wrote in message
news:CFED38BD-C33C-4336-97E3-624D5C5513DD@.microsoft.com...[vbcol=seagreen]
> Hi Charlie,
> Are you sure that the driver classes are accesible from your calling
> class,
> ie, have you imported the driver package?
> Ian
> "Charlie Tong" wrote:

clarification needed on sqlxml installation

I have 2 servers. One is an IIS server and the other is
the SQL Server 2000 SP3 server. I am confused on where i
am supposed to install SQLXML 3.0 SP2. Should i install
the software on the SQL Server machine or the IIS machine?
thanks
In most cases you just need to install it on the IIS Server.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Giovanni" <anonymous@.discussions.microsoft.com> wrote in message
news:6ae201c4cb1d$2d894f00$a301280a@.phx.gbl...
I have 2 servers. One is an IIS server and the other is
the SQL Server 2000 SP3 server. I am confused on where i
am supposed to install SQLXML 3.0 SP2. Should i install
the software on the SQL Server machine or the IIS machine?
thanks
|||Now you are making me confused.
Are you sure that you are supposed to install it on the IIS and not the SQL?
What data should the msxisapi.dll file present if not the data from the SQL
server? I have installed the XML service on the SQL server and not the IIS.
Can you explain why you install it on the WEB server and how you configure
it to retrieve data from the SQL server...
Reg
Jrgen Persson
"Graeme Malcolm" <graemem_cm@.hotmail.com> skrev i meddelandet
news:eaeKy97yEHA.336@.TK2MSFTNGP10.phx.gbl...
> In most cases you just need to install it on the IIS Server.
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Giovanni" <anonymous@.discussions.microsoft.com> wrote in message
> news:6ae201c4cb1d$2d894f00$a301280a@.phx.gbl...
> I have 2 servers. One is an IIS server and the other is
> the SQL Server 2000 SP3 server. I am confused on where i
> am supposed to install SQLXML 3.0 SP2. Should i install
> the software on the SQL Server machine or the IIS machine?
> thanks
>
|||It depends what you are doing, but SQLXML is primarily a client-side
technology. Install it on the IIS server and use the "Configure IIS Support"
tool to create a VDir that connects to your SQL Server to retrieve the data
you need to expose through IIS (either via a template, a schema, a SOAP
vName).
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Jrgen" <jorgen_persson78@.hotmail.com> wrote in message
news:%23pNo3H8yEHA.3376@.TK2MSFTNGP12.phx.gbl...
Now you are making me confused.
Are you sure that you are supposed to install it on the IIS and not the SQL?
What data should the msxisapi.dll file present if not the data from the SQL
server? I have installed the XML service on the SQL server and not the IIS.
Can you explain why you install it on the WEB server and how you configure
it to retrieve data from the SQL server...
Reg
Jrgen Persson
"Graeme Malcolm" <graemem_cm@.hotmail.com> skrev i meddelandet
news:eaeKy97yEHA.336@.TK2MSFTNGP10.phx.gbl...
> In most cases you just need to install it on the IIS Server.
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Giovanni" <anonymous@.discussions.microsoft.com> wrote in message
> news:6ae201c4cb1d$2d894f00$a301280a@.phx.gbl...
> I have 2 servers. One is an IIS server and the other is
> the SQL Server 2000 SP3 server. I am confused on where i
> am supposed to install SQLXML 3.0 SP2. Should i install
> the software on the SQL Server machine or the IIS machine?
> thanks
>

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

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
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
>

Checkpoint Activities

Dear Support,
I am trying to find a way to monitor the checkpoint acitivites in our SQL Server 2000 with sp3. The error log file doesn't show. Am I missing something here?
Also, after I ran the a checkpoint statment on a database, I am expecting the database mdf file to be updated. So, I am expecting last modified date of the mdf file to be changed. But, unfortunately,mind is not. I am wondering if this is normal.
The way I run the checkpoint statment is by use of the Job scheduling. The history log shows successful on all checkpoint statments run.
Thank you!
MartinThe modified date on the file will only change if it changes size (or if
autoclose database option is on). SQL keeps the file open when its running
and pages being updated etc in the file will not change the modified date on
the file itself (again unless autoclose is on)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:9A6D13D7-D9CF-47F5-A5E9-441F087190B9@.microsoft.com...
> Dear Support,
> I am trying to find a way to monitor the checkpoint acitivites in our SQL
Server 2000 with sp3. The error log file doesn't show. Am I missing
something here?
> Also, after I ran the a checkpoint statment on a database, I am expecting
the database mdf file to be updated. So, I am expecting last modified date
of the mdf file to be changed. But, unfortunately,mind is not. I am
wondering if this is normal.
> The way I run the checkpoint statment is by use of the Job scheduling. The
history log shows successful on all checkpoint statments run.
> Thank you!
> Martin|||I think trace flag 3502 still works with SQL2000. The
trace flag instructs SQL to record an entry in the
errorlog when a database is checkpointed. You can use this
trace flag and monitor your errorlog for the checkpoint
entries.
To enable the trace flag, you can run the following:
dbcc traceon(3605, 3502, -1)
If you are only interested in the execution of the
checkpoint statement executed from your job, you can run
the following in your job:
dbcc traceon(3605, 3502)
checkpoint
dbcc traceoff(3605, 3502)
Linchi
>--Original Message--
>Dear Support,
>I am trying to find a way to monitor the checkpoint
acitivites in our SQL Server 2000 with sp3. The error log
file doesn't show. Am I missing something here?
>Also, after I ran the a checkpoint statment on a
database, I am expecting the database mdf file to be
updated. So, I am expecting last modified date of the mdf
file to be changed. But, unfortunately,mind is not. I am
wondering if this is normal.
>The way I run the checkpoint statment is by use of the
Job scheduling. The history log shows successful on all
checkpoint statments run.
>Thank you!
>Martin
>.
>|||Thanks Linchi and Jaspi,
Your information is quite helpful.
Martin|||Martin,
Why do you feel a need to run checkpoint via a scheduled job?. SQL =server will be doing checkpoints at intervals anyway, and about the only =time I have needed a manual checkpoint is when performance testing to =force updates back to disc prior to forcing a cache flush. If you are in =a very strange position you *may* want to adjust the checkpoint =interval, but that is the exception as well.
Mike John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message =news:9A6D13D7-D9CF-47F5-A5E9-441F087190B9@.microsoft.com...
> Dear Support,
> I am trying to find a way to monitor the checkpoint acitivites in our =SQL Server 2000 with sp3. The error log file doesn't show. Am I =missing something here?
> Also, after I ran the a checkpoint statment on a database, I am =expecting the database mdf file to be updated. So, I am expecting last =modified date of the mdf file to be changed. But, unfortunately,mind is =not. I am wondering if this is normal. > The way I run the checkpoint statment is by use of the Job scheduling. =The history log shows successful on all checkpoint statments run.
> Thank you!
> Martin

Checkpoint Activities

Dear Support,
I am trying to find a way to monitor the checkpoint acitivites in our SQL Se
rver 2000 with sp3. The error log file doesn't show. Am I missing somethin
g here?
Also, after I ran the a checkpoint statment on a database, I am expecting th
e database mdf file to be updated. So, I am expecting last modified date of
the mdf file to be changed. But, unfortunately,mind is not. I am wondering
if this is normal.
The way I run the checkpoint statment is by use of the Job scheduling. The h
istory log shows successful on all checkpoint statments run.
Thank you!
MartinThe modified date on the file will only change if it changes size (or if
autoclose database option is on). SQL keeps the file open when its running
and pages being updated etc in the file will not change the modified date on
the file itself (again unless autoclose is on)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:9A6D13D7-D9CF-47F5-A5E9-441F087190B9@.microsoft.com...

quote:

---

> Dear Support,
> I am trying to find a way to monitor the checkpoint acitivites in our SQL

---

Server 2000 with sp3. The error log file doesn't show. Am I missing
something here?

quote:

---

> Also, after I ran the a checkpoint statment on a database, I am expecting

---

the database mdf file to be updated. So, I am expecting last modified date
of the mdf file to be changed. But, unfortunately,mind is not. I am
wondering if this is normal.

quote:

---

> The way I run the checkpoint statment is by use of the Job scheduling. The

---

history log shows successful on all checkpoint statments run.

quote:

---

> Thank you!
> Martin

---

|||I think trace flag 3502 still works with SQL2000. The
trace flag instructs SQL to record an entry in the
errorlog when a database is checkpointed. You can use this
trace flag and monitor your errorlog for the checkpoint
entries.
To enable the trace flag, you can run the following:
dbcc traceon(3605, 3502, -1)
If you are only interested in the execution of the
checkpoint statement executed from your job, you can run
the following in your job:
dbcc traceon(3605, 3502)
checkpoint
dbcc traceoff(3605, 3502)
Linchi

quote:

---

>--Original Message--
>Dear Support,
>I am trying to find a way to monitor the checkpoint

---

acitivites in our SQL Server 2000 with sp3. The error log
file doesn't show. Am I missing something here?

quote:

---

>Also, after I ran the a checkpoint statment on a

---

database, I am expecting the database mdf file to be
updated. So, I am expecting last modified date of the mdf
file to be changed. But, unfortunately,mind is not. I am
wondering if this is normal.

quote:

---

>The way I run the checkpoint statment is by use of the

---

Job scheduling. The history log shows successful on all
checkpoint statments run.

quote:

---

>Thank you!
>Martin
>.
>

---

|||Thanks Linchi and Jaspi,
Your information is quite helpful.
Martin|||Martin,
Why do you feel a need to run checkpoint via a scheduled job?. SQL =
server will be doing checkpoints at intervals anyway, and about the only =
time I have needed a manual checkpoint is when performance testing to =
force updates back to disc prior to forcing a cache flush. If you are in =
a very strange position you *may* want to adjust the checkpoint =
interval, but that is the exception as well.
Mike John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message =
news:9A6D13D7-D9CF-47F5-A5E9-441F087190B9@.microsoft.com...

quote:

---

> Dear Support,
> I am trying to find a way to monitor the checkpoint acitivites in our =

---

SQL Server 2000 with sp3. The error log file doesn't show. Am I =
missing something here?

quote:

---

> Also, after I ran the a checkpoint statment on a database, I am =

---

expecting the database mdf file to be updated. So, I am expecting last =
modified date of the mdf file to be changed. But, unfortunately,mind is =
not. I am wondering if this is normal. =20

quote:

---

> The way I run the checkpoint statment is by use of the Job scheduling. =

---

The history log shows successful on all checkpoint statments run.

quote:

---

> Thank you!
> Martin

---

checkdb filling up tempdb

Hello all,
I have a 10 gig SQL 2000 sp3 database that is having the following problem.
The tempdb which is on the c drive is expanding past two gigs when I run my
checkdb maintenance job. This is filling the c drive and bringing down my
app. I am going to move the tempdb to another drive that has more room but
my question is why would an integrity check fill up the tempdb? I assume
this is the problem since the time stamps for the job and problem are
identical.
TIA
jjThis is By Design. Any of the DBCC checks require space in tempdb for
processing check information - this is documented in Books Online. The
amount of space is proportional to the size of the database being checked,
and the amount of active transaction log for that database. You can use the
WITH ESTIMATEONLY option to find out the minimum amount of space required in
tempdb, not counting that dependant on the transaction log size.
From the numbers you've given, it sounds like you either have a large
active log or one very large table in the database.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eFjJS2V1DHA.2700@.TK2MSFTNGP11.phx.gbl...
> Hello all,
> I have a 10 gig SQL 2000 sp3 database that is having the following
problem.
> The tempdb which is on the c drive is expanding past two gigs when I run
my
> checkdb maintenance job. This is filling the c drive and bringing down my
> app. I am going to move the tempdb to another drive that has more room but
> my question is why would an integrity check fill up the tempdb? I assume
> this is the problem since the time stamps for the job and problem are
> identical.
> TIA
> jj
>