check sql licence: standard

Showing posts with label standard. Show all posts

Thursday, March 22, 2012

Classic ASP in Report Manager

Can I run classic asp pages in the report manager?
I'm implementing a multi-value select report which works fine using a
standard html page. I want to serive my values from a database however
and am therefor using asp. The asp page will display in the report
manager but the code will not run.
Thoughts?
Thanks!I had uploaded the asp page to the report manager. Keeping the page
outside report manager did the trick.
-J|||You would need to add the correct script mappings to the Report Manager
virtual directory. We do not add .asp mappings by default.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim" <emailjeffp@.yahoo.com> wrote in message
news:1114279221.384407.35000@.z14g2000cwz.googlegroups.com...
>I had uploaded the asp page to the report manager. Keeping the page
> outside report manager did the trick.
> -J
>

Class does not support aggregation

I am trying to port a database from SQL Server 2005 Express to SQL
Server 2005 Standard. I
detached my database from the SQL Sever 2005 Express instance and
attached it to the SQL Server 2005 Standard instance. Whenever I do a
select on tables in the database I have no problem. However if I
attempt to open or modify a table by selecting open or modify from the
popup menu, I get the following error --

TITLE: Microsoft SQL Server Management Studio
----------

Class does not support aggregation (or class object is remote)
(Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
(Microsoft.SqlServer.SqlTools.VSIntegration)

----------
BUTTONS:

OK
----------

I tried repairing the .NET 2.0 framework but to no avail.

Please help!!!

Thanks,Crazy Cat (danbredy@.hotmail.com) writes:
> I am trying to port a database from SQL Server 2005 Express to SQL
> Server 2005 Standard. I
> detached my database from the SQL Sever 2005 Express instance and
> attached it to the SQL Server 2005 Standard instance. Whenever I do a
> select on tables in the database I have no problem. However if I
> attempt to open or modify a table by selecting open or modify from the
> popup menu, I get the following error --
> TITLE: Microsoft SQL Server Management Studio
> ----------
> Class does not support aggregation (or class object is remote)
> (Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
> (Microsoft.SqlServer.SqlTools.VSIntegration)
> ----------
> BUTTONS:
> OK
> ----------
> I tried repairing the .NET 2.0 framework but to no avail.

Sounds like a bug in Management Studio. Could you create a new table
with the same definition, and see how it that works? If the error
persists, could you post the CREATE TABLE statement?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> Crazy Cat (danbredy@.hotmail.com) writes:
> > I am trying to port a database from SQL Server 2005 Express to SQL
> > Server 2005 Standard. I
> > detached my database from the SQL Sever 2005 Express instance and
> > attached it to the SQL Server 2005 Standard instance. Whenever I do a
> > select on tables in the database I have no problem. However if I
> > attempt to open or modify a table by selecting open or modify from the
> > popup menu, I get the following error --
> > TITLE: Microsoft SQL Server Management Studio
> > ----------
> > Class does not support aggregation (or class object is remote)
> > (Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION))
> > (Microsoft.SqlServer.SqlTools.VSIntegration)
> > ----------
> > BUTTONS:
> > OK
> > ----------
> > I tried repairing the .NET 2.0 framework but to no avail.
> Sounds like a bug in Management Studio. Could you create a new table
> with the same definition, and see how it that works? If the error
> persists, could you post the CREATE TABLE statement?
Actually I found a post somewhere that suggested uninstalling the
workstation components, repairing the .NET 2.0 framework, and
reinstalling the workstation.
I worked perfectly. I just wanted to post back in case someone else ran
into the same problem.

Thanks for replying.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,

Crazy Cat wrote:

> Actually I found a post somewhere that suggested uninstalling the
> workstation components, repairing the .NET 2.0 framework, and
> reinstalling the workstation.
> I worked perfectly. I just wanted to post back in case someone else ran
> into the same problem.

you probably had the CTP Express Version of the Mangement Tool
installed previously, and then uninstalled it after the release-version
of SQL2005 had been installed?!

At least that's what I did, and I'm getting the same error.
I'll try just reinstalling the 2005 client tools...

--
Stefan Klepsch

Monday, March 19, 2012

Choosing DB Edition (Std vs Ent)

I need to decided between Standard and Enterprise Edition (Cost is a
criteria - but its secondary to performance - )

The server spec under consideration: Dual Xeon, 1GB RAM, 36GB - RAID 1
(Dell PowerEdge 1850).

Application: Windows 2003 Std Server, ASP.NET, MS SQL Server 2000 based
data driven web application.

Approximately 25 simultaneous clients. Peak activity would probably be 50
transactions/activities per second (2 per second per client). I expect
the database size to grow up to 4GB in 1 year.

The application would use only basic OLAP features (if at all)...so
feature set wise I believe that standard edition is good enough.

What I am concerned about is when MS documentation says that Standard
Edition is for "organization that do not require the advanced scalability,
availability, performance, or analysis features of the SQL Server 2000
Enterprise Edition"

Is there a difference in performance between Std and Ent editions? In
terms of number of transactions per second that can be serviced?

What other criteria should I be aware of before deciding to go one way or
the other?

Any ideas?"Jonas Hei" <maps_263@.hotmail.com> wrote in message
news:opsehfbjyzr0m89z@.fx1025...
>I need to decided between Standard and Enterprise Edition (Cost is a
>criteria - but its secondary to performance - )
> The server spec under consideration: Dual Xeon, 1GB RAM, 36GB - RAID 1
> (Dell PowerEdge 1850).
> Application: Windows 2003 Std Server, ASP.NET, MS SQL Server 2000 based
> data driven web application.
> Approximately 25 simultaneous clients. Peak activity would probably be 50
> transactions/activities per second (2 per second per client). I expect
> the database size to grow up to 4GB in 1 year.
> The application would use only basic OLAP features (if at all)...so
> feature set wise I believe that standard edition is good enough.
> What I am concerned about is when MS documentation says that Standard
> Edition is for "organization that do not require the advanced scalability,
> availability, performance, or analysis features of the SQL Server 2000
> Enterprise Edition"
> Is there a difference in performance between Std and Ent editions? In
> terms of number of transactions per second that can be serviced?
> What other criteria should I be aware of before deciding to go one way or
> the other?
> Any ideas?

I'd guess that we're referring to features in the section you quoted even
though it makes it sound like the Enterprise Edition is inherently faster
than the Standard Edition. That's simply not the case. For example,
Clustering is a high availability option that is only available in the
Enterprise Edition. You can get more information about features by Edition
and choosing a particular Edition here:
http://www.microsoft.com/sql/evalua...es/choosing.asp.

There is nothing in either the Standard or Enterprise Edition engine that
I'm aware of that throttles performance based on the Edition that you're
using. The only Edition that has a performance throttle based on the Edition
is MSDE.

If the Standard Edition contains the features your application needs, my
guess is that it will run it just fine. Of course, without testing that's
impossible to know for sure.

--
Sincerely,
Stephen Dybing

This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi

The engine is the same for both editions, the Enterprise edition has
additional features, such as failover clustering, built in log shipping and
automatic use of indexed views.

See
http://msdn.microsoft.com/library/d..._ar_ts_1cdv.asp

If you don't want to use these features or if you are happy to "manually"
implement the features or can provide your own solutions, then standard
edition should be ok. All editions should be supported on your hardware.

John

"Jonas Hei" <maps_263@.hotmail.com> wrote in message
news:opsehfbjyzr0m89z@.fx1025...
> I need to decided between Standard and Enterprise Edition (Cost is a
> criteria - but its secondary to performance - )
> The server spec under consideration: Dual Xeon, 1GB RAM, 36GB - RAID 1
> (Dell PowerEdge 1850).
> Application: Windows 2003 Std Server, ASP.NET, MS SQL Server 2000 based
> data driven web application.
> Approximately 25 simultaneous clients. Peak activity would probably be 50
> transactions/activities per second (2 per second per client). I expect
> the database size to grow up to 4GB in 1 year.
> The application would use only basic OLAP features (if at all)...so
> feature set wise I believe that standard edition is good enough.
> What I am concerned about is when MS documentation says that Standard
> Edition is for "organization that do not require the advanced scalability,
> availability, performance, or analysis features of the SQL Server 2000
> Enterprise Edition"
> Is there a difference in performance between Std and Ent editions? In
> terms of number of transactions per second that can be serviced?
> What other criteria should I be aware of before deciding to go one way or
> the other?
> Any ideas?|||Stephen Dybing [MSFT] (stephd@.online.microsoft.com) writes:
> There is nothing in either the Standard or Enterprise Edition engine
> that I'm aware of that throttles performance based on the Edition that
> you're using. The only Edition that has a performance throttle based on
> the Edition is MSDE.

There are however features in Enterprise Edition that may help to
improve performance. One such features in indexed views. You can use
indexed views in Std Edition too, but there situations where the optimizer
will not consider the view.

Then again, if you are not using indexed views, this will not make a
difference.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||True, but in my defense, it's listed on the Features page I pointed
everybody at. :-)

--
Sincerely,
Stephen Dybing

This posting is provided "AS IS" with no warranties, and confers no rights.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns956828A66F7CYazorman@.127.0.0.1...
> Stephen Dybing [MSFT] (stephd@.online.microsoft.com) writes:
>> There is nothing in either the Standard or Enterprise Edition engine
>> that I'm aware of that throttles performance based on the Edition that
>> you're using. The only Edition that has a performance throttle based on
>> the Edition is MSDE.
> There are however features in Enterprise Edition that may help to
> improve performance. One such features in indexed views. You can use
> indexed views in Std Edition too, but there situations where the optimizer
> will not consider the view.
> Then again, if you are not using indexed views, this will not make a
> difference.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Sunday, March 11, 2012

Chinese DB

can i create a chinese database with microsoft sql 2000 server standard
edition.Hi
You can create a database with the non-default collation using the COLLATE
arguement of the CREATE DATABASE statement (see books online) There are
various chineese collations see fn_helpcollations in Books online.
You may want to look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
John
"kEVin" wrote:
> can i create a chinese database with microsoft sql 2000 server standard
> edition.

Thursday, March 8, 2012

Chinese characters

:|Hello,
After a long search, I found this article i thought to solve my
problem.
I am using Win 2000 Advanced Server, SQL Server 2000 standard edn.
dotNET F/W 1.1
My table structure is
empid integer
empname varchar(30)
empname_chinese nvarchar(20)

>From the asp.net page form field, If I input chinese characters, the
chinese characters are displayed as ? 40 ?(after 40 days)
Whats the problem.
i am really disgusted with this. I am searching a lot in google. But
still didnt solve.
Can anyone please help me to solve my problem?
I've changed the <globalization fileEncoding="utf-8" ...> in web.config
also
still no use.
Any help is highly appreciated.
I am inserting into my database table from web form as
"insert into employee(empid,empname,empname_chinese)
values(@.empid,@.empname,@.empname_chinese)
"
dbcommand.parameters.add(new
SqlParameter("@.empname_chinese",sqlDBType.NVarchar)).value=txtNameChin.text
......
dbcommand.executeNonQuery()
connection and all statements skipped for explanation.http://sqljunkies.com/Forums/ShowPo...ostID=2892#2948
Hope it helps

Wednesday, March 7, 2012

Checkpointing Not Happening in Simple Recovery Model

The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
We have at least two databases on this server in simple recovery model. Of
course, one of these databases is tempdb so this is very problematic.
The transaction logs just keep filling up and filling up, growing to max,
and finally become full. We would then have to issue an alter database
statement to increase the size of the log. Although an alter database
statement is one of those things that should trigger a checkpoint - it does
not clear out the space used in the log file. So, once we are able to get a
bit of free space, we can manually issue a checkpoint.
We have incorporated a checkpoint to run every 15 minutes. We also have an
alert that will catch a log at 80% full and then issues a checkpoint on that
database. But, we want to figure out what is going on and what is causing
this.
Any ideas?
MichelleThis sounds more like the result of long-running transactions, e.g.:
begin tran
-- a whole bunch of statements
commit tran
The log cannot be truncated beyond the first open transaction.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"michelle" <michelle@.nospam.com> wrote in message
news:%23FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
We have at least two databases on this server in simple recovery model. Of
course, one of these databases is tempdb so this is very problematic.
The transaction logs just keep filling up and filling up, growing to max,
and finally become full. We would then have to issue an alter database
statement to increase the size of the log. Although an alter database
statement is one of those things that should trigger a checkpoint - it does
not clear out the space used in the log file. So, once we are able to get a
bit of free space, we can manually issue a checkpoint.
We have incorporated a checkpoint to run every 15 minutes. We also have an
alert that will catch a log at 80% full and then issues a checkpoint on that
database. But, we want to figure out what is going on and what is causing
this.
Any ideas?
Michelle|||Do you have long running transactions? If so, the log can't be truncated
until you either commit or roll back.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"michelle" <michelle@.nospam.com> wrote in message
news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> We have at least two databases on this server in simple recovery model. Of
> course, one of these databases is tempdb so this is very problematic.
> The transaction logs just keep filling up and filling up, growing to max,
> and finally become full. We would then have to issue an alter database
> statement to increase the size of the log. Although an alter database
> statement is one of those things that should trigger a checkpoint - it
does
> not clear out the space used in the log file. So, once we are able to get
a
> bit of free space, we can manually issue a checkpoint.
> We have incorporated a checkpoint to run every 15 minutes. We also have an
> alert that will catch a log at 80% full and then issues a checkpoint on
that
> database. But, we want to figure out what is going on and what is causing
> this.
> Any ideas?
> Michelle
>|||I appreciate that two people have pointed to long-running transactions
(perhaps transactions left 'open' that never commit?).
But, would I then be able to issue a checkpoint and recover the free space
or wouldn't these long-running transactions still just keep the space in the
log? If there are transactions still open, I would think that issuing a
checkpoint statement manually would not do any good. Maybe I'm wrong.
Please note that depending on how much space we have allocated to these
logs, it can take days to fill it up. For example, tempdb would go for
several days (and the space used in the log would keep growing and growing)
until it would finally get full. It didn't seem like anything would then
roll back - I waited 45 minutes one day (server is pretty powerful, fast
disks on SAN, 4 GB RAM, 2 HT cpus).
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eKX$p%23Q6EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Do you have long running transactions? If so, the log can't be truncated
> until you either commit or roll back.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "michelle" <michelle@.nospam.com> wrote in message
> news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> >
> > We have at least two databases on this server in simple recovery model.
Of
> > course, one of these databases is tempdb so this is very problematic.
> >
> > The transaction logs just keep filling up and filling up, growing to
max,
> > and finally become full. We would then have to issue an alter database
> > statement to increase the size of the log. Although an alter database
> > statement is one of those things that should trigger a checkpoint - it
> does
> > not clear out the space used in the log file. So, once we are able to
get
> a
> > bit of free space, we can manually issue a checkpoint.
> >
> > We have incorporated a checkpoint to run every 15 minutes. We also have
an
> > alert that will catch a log at 80% full and then issues a checkpoint on
> that
> > database. But, we want to figure out what is going on and what is
causing
> > this.
> >
> > Any ideas?
> >
> > Michelle
> >
> >
>|||Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN for
the databases in question. If you are seeing a long-running transaction, it
will identify the SPID for it, as well as the date/time it started.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"michelle" <michelle@.nospam.com> wrote in message
news:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
I appreciate that two people have pointed to long-running transactions
(perhaps transactions left 'open' that never commit?).
But, would I then be able to issue a checkpoint and recover the free space
or wouldn't these long-running transactions still just keep the space in the
log? If there are transactions still open, I would think that issuing a
checkpoint statement manually would not do any good. Maybe I'm wrong.
Please note that depending on how much space we have allocated to these
logs, it can take days to fill it up. For example, tempdb would go for
several days (and the space used in the log would keep growing and growing)
until it would finally get full. It didn't seem like anything would then
roll back - I waited 45 minutes one day (server is pretty powerful, fast
disks on SAN, 4 GB RAM, 2 HT cpus).
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eKX$p%23Q6EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Do you have long running transactions? If so, the log can't be truncated
> until you either commit or roll back.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "michelle" <michelle@.nospam.com> wrote in message
> news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> >
> > We have at least two databases on this server in simple recovery model.
Of
> > course, one of these databases is tempdb so this is very problematic.
> >
> > The transaction logs just keep filling up and filling up, growing to
max,
> > and finally become full. We would then have to issue an alter database
> > statement to increase the size of the log. Although an alter database
> > statement is one of those things that should trigger a checkpoint - it
> does
> > not clear out the space used in the log file. So, once we are able to
get
> a
> > bit of free space, we can manually issue a checkpoint.
> >
> > We have incorporated a checkpoint to run every 15 minutes. We also have
an
> > alert that will catch a log at 80% full and then issues a checkpoint on
> that
> > database. But, we want to figure out what is going on and what is
causing
> > this.
> >
> > Any ideas?
> >
> > Michelle
> >
> >
>|||I guess that's my point. If I have open transactions, checkpoint shouldn't
help me because they'll stay in the log and take up space. BUT, when I issue
a checkpoint, the space is freed - leading me to believe that the log is NOT
full of open transactions but full of committed transactions. Yet, the logs
are becoming well over 70% full (or were until we started issuing regular
checkpoints). I'm not coming up with any open transactions, either.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN
for
> the databases in question. If you are seeing a long-running transaction,
it
> will identify the SPID for it, as well as the date/time it started.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> I appreciate that two people have pointed to long-running transactions
> (perhaps transactions left 'open' that never commit?).
> But, would I then be able to issue a checkpoint and recover the free space
> or wouldn't these long-running transactions still just keep the space in
the
> log? If there are transactions still open, I would think that issuing a
> checkpoint statement manually would not do any good. Maybe I'm wrong.
> Please note that depending on how much space we have allocated to these
> logs, it can take days to fill it up. For example, tempdb would go for
> several days (and the space used in the log would keep growing and
growing)
> until it would finally get full. It didn't seem like anything would then
> roll back - I waited 45 minutes one day (server is pretty powerful, fast
> disks on SAN, 4 GB RAM, 2 HT cpus).
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:eKX$p%23Q6EHA.2568@.TK2MSFTNGP11.phx.gbl...
> > Do you have long running transactions? If so, the log can't be truncated
> > until you either commit or roll back.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "michelle" <michelle@.nospam.com> wrote in message
> > news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> > >
> > > We have at least two databases on this server in simple recovery
model.
> Of
> > > course, one of these databases is tempdb so this is very problematic.
> > >
> > > The transaction logs just keep filling up and filling up, growing to
> max,
> > > and finally become full. We would then have to issue an alter database
> > > statement to increase the size of the log. Although an alter database
> > > statement is one of those things that should trigger a checkpoint - it
> > does
> > > not clear out the space used in the log file. So, once we are able to
> get
> > a
> > > bit of free space, we can manually issue a checkpoint.
> > >
> > > We have incorporated a checkpoint to run every 15 minutes. We also
have
> an
> > > alert that will catch a log at 80% full and then issues a checkpoint
on
> > that
> > > database. But, we want to figure out what is going on and what is
> causing
> > > this.
> > >
> > > Any ideas?
> > >
> > > Michelle
> > >
> > >
> >
> >
>|||Have you run:
sp_configure "recovery interval (min)"
If this has changed from the default, then that can have an influence on
checkpointing - and thus the amount of used space in your logs.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"michelle" <michelle@.nospam.com> wrote in message
news:%2312oIES6EHA.3840@.tk2msftngp13.phx.gbl...
I guess that's my point. If I have open transactions, checkpoint shouldn't
help me because they'll stay in the log and take up space. BUT, when I issue
a checkpoint, the space is freed - leading me to believe that the log is NOT
full of open transactions but full of committed transactions. Yet, the logs
are becoming well over 70% full (or were until we started issuing regular
checkpoints). I'm not coming up with any open transactions, either.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN
for
> the databases in question. If you are seeing a long-running transaction,
it
> will identify the SPID for it, as well as the date/time it started.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> I appreciate that two people have pointed to long-running transactions
> (perhaps transactions left 'open' that never commit?).
> But, would I then be able to issue a checkpoint and recover the free space
> or wouldn't these long-running transactions still just keep the space in
the
> log? If there are transactions still open, I would think that issuing a
> checkpoint statement manually would not do any good. Maybe I'm wrong.
> Please note that depending on how much space we have allocated to these
> logs, it can take days to fill it up. For example, tempdb would go for
> several days (and the space used in the log would keep growing and
growing)
> until it would finally get full. It didn't seem like anything would then
> roll back - I waited 45 minutes one day (server is pretty powerful, fast
> disks on SAN, 4 GB RAM, 2 HT cpus).
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:eKX$p%23Q6EHA.2568@.TK2MSFTNGP11.phx.gbl...
> > Do you have long running transactions? If so, the log can't be truncated
> > until you either commit or roll back.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "michelle" <michelle@.nospam.com> wrote in message
> > news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> > >
> > > We have at least two databases on this server in simple recovery
model.
> Of
> > > course, one of these databases is tempdb so this is very problematic.
> > >
> > > The transaction logs just keep filling up and filling up, growing to
> max,
> > > and finally become full. We would then have to issue an alter database
> > > statement to increase the size of the log. Although an alter database
> > > statement is one of those things that should trigger a checkpoint - it
> > does
> > > not clear out the space used in the log file. So, once we are able to
> get
> > a
> > > bit of free space, we can manually issue a checkpoint.
> > >
> > > We have incorporated a checkpoint to run every 15 minutes. We also
have
> an
> > > alert that will catch a log at 80% full and then issues a checkpoint
on
> > that
> > > database. But, we want to figure out what is going on and what is
> causing
> > > this.
> > >
> > > Any ideas?
> > >
> > > Michelle
> > >
> > >
> >
> >
>|||I know that we talked about looking into changing this to see if it would
make a difference but it looks like we're still using the default settings
for this:
name minimum maximum config_value
run_value
recovery interval (min) 0 32767 0
0
Right?
Thanks - Michelle
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O1cBGMS6EHA.796@.TK2MSFTNGP09.phx.gbl...
> Have you run:
> sp_configure "recovery interval (min)"
> If this has changed from the default, then that can have an influence on
> checkpointing - and thus the amount of used space in your logs.
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:%2312oIES6EHA.3840@.tk2msftngp13.phx.gbl...
> I guess that's my point. If I have open transactions, checkpoint shouldn't
> help me because they'll stay in the log and take up space. BUT, when I
issue
> a checkpoint, the space is freed - leading me to believe that the log is
NOT
> full of open transactions but full of committed transactions. Yet, the
logs
> are becoming well over 70% full (or were until we started issuing regular
> checkpoints). I'm not coming up with any open transactions, either.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN
> for
> > the databases in question. If you are seeing a long-running
transaction,
> it
> > will identify the SPID for it, as well as the date/time it started.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "michelle" <michelle@.nospam.com> wrote in message
> > news:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > I appreciate that two people have pointed to long-running transactions
> > (perhaps transactions left 'open' that never commit?).
> >
> > But, would I then be able to issue a checkpoint and recover the free
space
> > or wouldn't these long-running transactions still just keep the space in
> the
> > log? If there are transactions still open, I would think that issuing a
> > checkpoint statement manually would not do any good. Maybe I'm wrong.
> >
> > Please note that depending on how much space we have allocated to these
> > logs, it can take days to fill it up. For example, tempdb would go for
> > several days (and the space used in the log would keep growing and
> growing)
> > until it would finally get full. It didn't seem like anything would then
> > roll back - I waited 45 minutes one day (server is pretty powerful, fast
> > disks on SAN, 4 GB RAM, 2 HT cpus).
> >
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:eKX$p%23Q6EHA.2568@.TK2MSFTNGP11.phx.gbl...
> > > Do you have long running transactions? If so, the log can't be
truncated
> > > until you either commit or roll back.
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "michelle" <michelle@.nospam.com> wrote in message
> > > news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > > > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> > > >
> > > > We have at least two databases on this server in simple recovery
> model.
> > Of
> > > > course, one of these databases is tempdb so this is very
problematic.
> > > >
> > > > The transaction logs just keep filling up and filling up, growing to
> > max,
> > > > and finally become full. We would then have to issue an alter
database
> > > > statement to increase the size of the log. Although an alter
database
> > > > statement is one of those things that should trigger a checkpoint -
it
> > > does
> > > > not clear out the space used in the log file. So, once we are able
to
> > get
> > > a
> > > > bit of free space, we can manually issue a checkpoint.
> > > >
> > > > We have incorporated a checkpoint to run every 15 minutes. We also
> have
> > an
> > > > alert that will catch a log at 80% full and then issues a checkpoint
> on
> > > that
> > > > database. But, we want to figure out what is going on and what is
> > causing
> > > > this.
> > > >
> > > > Any ideas?
> > > >
> > > > Michelle
> > > >
> > > >
> > >
> > >
> >
> >
>|||Well, this is odd. With the default values here, you shouldn't be
experiencing what you have. You could try experimenting with low values -
like 1, 2 or 5 - and see if that gets things under control.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"michelle" <michelle@.nospam.com> wrote in message
news:udRdWoS6EHA.3856@.tk2msftngp13.phx.gbl...
I know that we talked about looking into changing this to see if it would
make a difference but it looks like we're still using the default settings
for this:
name minimum maximum config_value
run_value
recovery interval (min) 0 32767 0
0
Right?
Thanks - Michelle
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O1cBGMS6EHA.796@.TK2MSFTNGP09.phx.gbl...
> Have you run:
> sp_configure "recovery interval (min)"
> If this has changed from the default, then that can have an influence on
> checkpointing - and thus the amount of used space in your logs.
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:%2312oIES6EHA.3840@.tk2msftngp13.phx.gbl...
> I guess that's my point. If I have open transactions, checkpoint shouldn't
> help me because they'll stay in the log and take up space. BUT, when I
issue
> a checkpoint, the space is freed - leading me to believe that the log is
NOT
> full of open transactions but full of committed transactions. Yet, the
logs
> are becoming well over 70% full (or were until we started issuing regular
> checkpoints). I'm not coming up with any open transactions, either.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > Checkpoint isn't gonna buy you anything here. Try running DBCC OPENTRAN
> for
> > the databases in question. If you are seeing a long-running
transaction,
> it
> > will identify the SPID for it, as well as the date/time it started.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "michelle" <michelle@.nospam.com> wrote in message
> > news:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > I appreciate that two people have pointed to long-running transactions
> > (perhaps transactions left 'open' that never commit?).
> >
> > But, would I then be able to issue a checkpoint and recover the free
space
> > or wouldn't these long-running transactions still just keep the space in
> the
> > log? If there are transactions still open, I would think that issuing a
> > checkpoint statement manually would not do any good. Maybe I'm wrong.
> >
> > Please note that depending on how much space we have allocated to these
> > logs, it can take days to fill it up. For example, tempdb would go for
> > several days (and the space used in the log would keep growing and
> growing)
> > until it would finally get full. It didn't seem like anything would then
> > roll back - I waited 45 minutes one day (server is pretty powerful, fast
> > disks on SAN, 4 GB RAM, 2 HT cpus).
> >
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:eKX$p%23Q6EHA.2568@.TK2MSFTNGP11.phx.gbl...
> > > Do you have long running transactions? If so, the log can't be
truncated
> > > until you either commit or roll back.
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "michelle" <michelle@.nospam.com> wrote in message
> > > news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > > > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> > > >
> > > > We have at least two databases on this server in simple recovery
> model.
> > Of
> > > > course, one of these databases is tempdb so this is very
problematic.
> > > >
> > > > The transaction logs just keep filling up and filling up, growing to
> > max,
> > > > and finally become full. We would then have to issue an alter
database
> > > > statement to increase the size of the log. Although an alter
database
> > > > statement is one of those things that should trigger a checkpoint -
it
> > > does
> > > > not clear out the space used in the log file. So, once we are able
to
> > get
> > > a
> > > > bit of free space, we can manually issue a checkpoint.
> > > >
> > > > We have incorporated a checkpoint to run every 15 minutes. We also
> have
> > an
> > > > alert that will catch a log at 80% full and then issues a checkpoint
> on
> > > that
> > > > database. But, we want to figure out what is going on and what is
> > causing
> > > > this.
> > > >
> > > > Any ideas?
> > > >
> > > > Michelle
> > > >
> > > >
> > >
> > >
> >
> >
>|||We'll give this a try after the weekend - don't want to make trouble over
the Christmas Holiday - :)
I'll report back with the results.
Thanks for your help!
Michelle
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%233MfXsS6EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Well, this is odd. With the default values here, you shouldn't be
> experiencing what you have. You could try experimenting with low values -
> like 1, 2 or 5 - and see if that gets things under control.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:udRdWoS6EHA.3856@.tk2msftngp13.phx.gbl...
> I know that we talked about looking into changing this to see if it would
> make a difference but it looks like we're still using the default settings
> for this:
> name minimum maximum config_value
> run_value
> recovery interval (min) 0 32767 0
> 0
> Right?
> Thanks - Michelle
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O1cBGMS6EHA.796@.TK2MSFTNGP09.phx.gbl...
> > Have you run:
> >
> > sp_configure "recovery interval (min)"
> >
> > If this has changed from the default, then that can have an influence on
> > checkpointing - and thus the amount of used space in your logs.
> >
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "michelle" <michelle@.nospam.com> wrote in message
> > news:%2312oIES6EHA.3840@.tk2msftngp13.phx.gbl...
> > I guess that's my point. If I have open transactions, checkpoint
shouldn't
> > help me because they'll stay in the log and take up space. BUT, when I
> issue
> > a checkpoint, the space is freed - leading me to believe that the log is
> NOT
> > full of open transactions but full of committed transactions. Yet, the
> logs
> > are becoming well over 70% full (or were until we started issuing
regular
> > checkpoints). I'm not coming up with any open transactions, either.
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:%23uBD8kR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > > Checkpoint isn't gonna buy you anything here. Try running DBCC
OPENTRAN
> > for
> > > the databases in question. If you are seeing a long-running
> transaction,
> > it
> > > will identify the SPID for it, as well as the date/time it started.
> > >
> > > --
> > > Tom
> > >
> > > ---
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinnaclepublishing.com
> > >
> > >
> > > "michelle" <michelle@.nospam.com> wrote in message
> > > news:uYsCGHR6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> > > I appreciate that two people have pointed to long-running transactions
> > > (perhaps transactions left 'open' that never commit?).
> > >
> > > But, would I then be able to issue a checkpoint and recover the free
> space
> > > or wouldn't these long-running transactions still just keep the space
in
> > the
> > > log? If there are transactions still open, I would think that issuing
a
> > > checkpoint statement manually would not do any good. Maybe I'm wrong.
> > >
> > > Please note that depending on how much space we have allocated to
these
> > > logs, it can take days to fill it up. For example, tempdb would go for
> > > several days (and the space used in the log would keep growing and
> > growing)
> > > until it would finally get full. It didn't seem like anything would
then
> > > roll back - I waited 45 minutes one day (server is pretty powerful,
fast
> > > disks on SAN, 4 GB RAM, 2 HT cpus).
> > >
> > >
> > > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > > news:eKX$p%23Q6EHA.2568@.TK2MSFTNGP11.phx.gbl...
> > > > Do you have long running transactions? If so, the log can't be
> truncated
> > > > until you either commit or roll back.
> > > >
> > > > Regards
> > > > --
> > > > Mike Epprecht, Microsoft SQL Server MVP
> > > > Zurich, Switzerland
> > > >
> > > > IM: mike@.epprecht.net
> > > >
> > > > MVP Program: http://www.microsoft.com/mvp
> > > >
> > > > Blog: http://www.msmvps.com/epprecht/
> > > >
> > > > "michelle" <michelle@.nospam.com> wrote in message
> > > > news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > > > > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> > > > >
> > > > > We have at least two databases on this server in simple recovery
> > model.
> > > Of
> > > > > course, one of these databases is tempdb so this is very
> problematic.
> > > > >
> > > > > The transaction logs just keep filling up and filling up, growing
to
> > > max,
> > > > > and finally become full. We would then have to issue an alter
> database
> > > > > statement to increase the size of the log. Although an alter
> database
> > > > > statement is one of those things that should trigger a
checkpoint -
> it
> > > > does
> > > > > not clear out the space used in the log file. So, once we are able
> to
> > > get
> > > > a
> > > > > bit of free space, we can manually issue a checkpoint.
> > > > >
> > > > > We have incorporated a checkpoint to run every 15 minutes. We also
> > have
> > > an
> > > > > alert that will catch a log at 80% full and then issues a
checkpoint
> > on
> > > > that
> > > > > database. But, we want to figure out what is going on and what is
> > > causing
> > > > > this.
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Michelle
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Are you sure that you have simple recovery model selected for tempdb?
Regards,
Daniel
"michelle" <michelle@.nospam.com> wrote in message
news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> We have at least two databases on this server in simple recovery model. Of
> course, one of these databases is tempdb so this is very problematic.
> The transaction logs just keep filling up and filling up, growing to max,
> and finally become full. We would then have to issue an alter database
> statement to increase the size of the log. Although an alter database
> statement is one of those things that should trigger a checkpoint - it
does
> not clear out the space used in the log file. So, once we are able to get
a
> bit of free space, we can manually issue a checkpoint.
> We have incorporated a checkpoint to run every 15 minutes. We also have an
> alert that will catch a log at 80% full and then issues a checkpoint on
that
> database. But, we want to figure out what is going on and what is causing
> this.
> Any ideas?
> Michelle
>|||You cannot set the recovery model in tempdb.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
Are you sure that you have simple recovery model selected for tempdb?
Regards,
Daniel
"michelle" <michelle@.nospam.com> wrote in message
news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> We have at least two databases on this server in simple recovery model. Of
> course, one of these databases is tempdb so this is very problematic.
> The transaction logs just keep filling up and filling up, growing to max,
> and finally become full. We would then have to issue an alter database
> statement to increase the size of the log. Although an alter database
> statement is one of those things that should trigger a checkpoint - it
does
> not clear out the space used in the log file. So, once we are able to get
a
> bit of free space, we can manually issue a checkpoint.
> We have incorporated a checkpoint to run every 15 minutes. We also have an
> alert that will catch a log at 80% full and then issues a checkpoint on
that
> database. But, we want to figure out what is going on and what is causing
> this.
> Any ideas?
> Michelle
>|||Yes, you are right, I read that Michelle is writing about logs not log, is
there possibility that she looks at transaction logs on databases with full
or bulk logged recovery model?
Regards,
Daniel
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> You cannot set the recovery model in tempdb.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Are you sure that you have simple recovery model selected for tempdb?
> Regards,
> Daniel
> "michelle" <michelle@.nospam.com> wrote in message
> news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> >
> > We have at least two databases on this server in simple recovery model.
Of
> > course, one of these databases is tempdb so this is very problematic.
> >
> > The transaction logs just keep filling up and filling up, growing to
max,
> > and finally become full. We would then have to issue an alter database
> > statement to increase the size of the log. Although an alter database
> > statement is one of those things that should trigger a checkpoint - it
> does
> > not clear out the space used in the log file. So, once we are able to
get
> a
> > bit of free space, we can manually issue a checkpoint.
> >
> > We have incorporated a checkpoint to run every 15 minutes. We also have
an
> > alert that will catch a log at 80% full and then issues a checkpoint on
> that
> > database. But, we want to figure out what is going on and what is
causing
> > this.
> >
> > Any ideas?
> >
> > Michelle
> >
> >
>|||She says that she has simple recovery and that one of them is tempdb. Maybe
the next thing we should look at is doing sp_helpdb.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:uevSRcu6EHA.2012@.TK2MSFTNGP15.phx.gbl...
Yes, you are right, I read that Michelle is writing about logs not log, is
there possibility that she looks at transaction logs on databases with full
or bulk logged recovery model?
Regards,
Daniel
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> You cannot set the recovery model in tempdb.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Are you sure that you have simple recovery model selected for tempdb?
> Regards,
> Daniel
> "michelle" <michelle@.nospam.com> wrote in message
> news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> >
> > We have at least two databases on this server in simple recovery model.
Of
> > course, one of these databases is tempdb so this is very problematic.
> >
> > The transaction logs just keep filling up and filling up, growing to
max,
> > and finally become full. We would then have to issue an alter database
> > statement to increase the size of the log. Although an alter database
> > statement is one of those things that should trigger a checkpoint - it
> does
> > not clear out the space used in the log file. So, once we are able to
get
> a
> > bit of free space, we can manually issue a checkpoint.
> >
> > We have incorporated a checkpoint to run every 15 minutes. We also have
an
> > alert that will catch a log at 80% full and then issues a checkpoint on
> that
> > database. But, we want to figure out what is going on and what is
causing
> > this.
> >
> > Any ideas?
> >
> > Michelle
> >
> >
>|||I think so,
also I think next line will go straight.
select name,databasepropertyex(name,'recovery') model from
master.dbo.sysdatabases
Regards,
Daniel
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OqkKwr06EHA.2488@.TK2MSFTNGP14.phx.gbl...
> She says that she has simple recovery and that one of them is tempdb.
Maybe
> the next thing we should look at is doing sp_helpdb.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:uevSRcu6EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Yes, you are right, I read that Michelle is writing about logs not log, is
> there possibility that she looks at transaction logs on databases with
full
> or bulk logged recovery model?
> Regards,
> Daniel
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> > You cannot set the recovery model in tempdb.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> > news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > Are you sure that you have simple recovery model selected for tempdb?
> >
> > Regards,
> > Daniel
> >
> > "michelle" <michelle@.nospam.com> wrote in message
> > news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> > >
> > > We have at least two databases on this server in simple recovery
model.
> Of
> > > course, one of these databases is tempdb so this is very problematic.
> > >
> > > The transaction logs just keep filling up and filling up, growing to
> max,
> > > and finally become full. We would then have to issue an alter database
> > > statement to increase the size of the log. Although an alter database
> > > statement is one of those things that should trigger a checkpoint - it
> > does
> > > not clear out the space used in the log file. So, once we are able to
> get
> > a
> > > bit of free space, we can manually issue a checkpoint.
> > >
> > > We have incorporated a checkpoint to run every 15 minutes. We also
have
> an
> > > alert that will catch a log at 80% full and then issues a checkpoint
on
> > that
> > > database. But, we want to figure out what is going on and what is
> causing
> > > this.
> > >
> > > Any ideas?
> > >
> > > Michelle
> > >
> > >
> >
> >
>|||Now all we need are the results...
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:u6RXHH66EHA.3368@.TK2MSFTNGP10.phx.gbl...
I think so,
also I think next line will go straight.
select name,databasepropertyex(name,'recovery') model from
master.dbo.sysdatabases
Regards,
Daniel
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OqkKwr06EHA.2488@.TK2MSFTNGP14.phx.gbl...
> She says that she has simple recovery and that one of them is tempdb.
Maybe
> the next thing we should look at is doing sp_helpdb.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:uevSRcu6EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Yes, you are right, I read that Michelle is writing about logs not log, is
> there possibility that she looks at transaction logs on databases with
full
> or bulk logged recovery model?
> Regards,
> Daniel
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> > You cannot set the recovery model in tempdb.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> > news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > Are you sure that you have simple recovery model selected for tempdb?
> >
> > Regards,
> > Daniel
> >
> > "michelle" <michelle@.nospam.com> wrote in message
> > news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> > >
> > > We have at least two databases on this server in simple recovery
model.
> Of
> > > course, one of these databases is tempdb so this is very problematic.
> > >
> > > The transaction logs just keep filling up and filling up, growing to
> max,
> > > and finally become full. We would then have to issue an alter database
> > > statement to increase the size of the log. Although an alter database
> > > statement is one of those things that should trigger a checkpoint - it
> > does
> > > not clear out the space used in the log file. So, once we are able to
> get
> > a
> > > bit of free space, we can manually issue a checkpoint.
> > >
> > > We have incorporated a checkpoint to run every 15 minutes. We also
have
> an
> > > alert that will catch a log at 80% full and then issues a checkpoint
on
> > that
> > > database. But, we want to figure out what is going on and what is
> causing
> > > this.
> > >
> > > Any ideas?
> > >
> > > Michelle
> > >
> > >
> >
> >
>|||Yes, I am looking at databases set to simple:
distribution SIMPLE
master SIMPLE
tempdb SIMPLE
sqlprofile SIMPLE
msdb SIMPLE
pubs SIMPLE
Northwind SIMPLE
PERFMON SIMPLE
When we're fully-staffed again tomorrow, we'll look into stopping the
15-minute checkpoints and altering the recovery interval.
Michelle
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uXBQ3Q66EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Now all we need are the results...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:u6RXHH66EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I think so,
> also I think next line will go straight.
> select name,databasepropertyex(name,'recovery') model from
> master.dbo.sysdatabases
> Regards,
> Daniel
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OqkKwr06EHA.2488@.TK2MSFTNGP14.phx.gbl...
> > She says that she has simple recovery and that one of them is tempdb.
> Maybe
> > the next thing we should look at is doing sp_helpdb.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> > .
> > "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> > news:uevSRcu6EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > Yes, you are right, I read that Michelle is writing about logs not log,
is
> > there possibility that she looks at transaction logs on databases with
> full
> > or bulk logged recovery model?
> >
> > Regards,
> > Daniel
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:ONo0e4b6EHA.1392@.tk2msftngp13.phx.gbl...
> > > You cannot set the recovery model in tempdb.
> > >
> > > --
> > > Tom
> > >
> > > ---
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinnaclepublishing.com
> > >
> > >
> > > "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in
message
> > > news:u5NjKcV6EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > > Are you sure that you have simple recovery model selected for tempdb?
> > >
> > > Regards,
> > > Daniel
> > >
> > > "michelle" <michelle@.nospam.com> wrote in message
> > > news:#FjEC4Q6EHA.260@.TK2MSFTNGP10.phx.gbl...
> > > > The server is Windows 2003, SQL Server 2000, sp3 Standard Edition.
> > > >
> > > > We have at least two databases on this server in simple recovery
> model.
> > Of
> > > > course, one of these databases is tempdb so this is very
problematic.
> > > >
> > > > The transaction logs just keep filling up and filling up, growing to
> > max,
> > > > and finally become full. We would then have to issue an alter
database
> > > > statement to increase the size of the log. Although an alter
database
> > > > statement is one of those things that should trigger a checkpoint -
it
> > > does
> > > > not clear out the space used in the log file. So, once we are able
to
> > get
> > > a
> > > > bit of free space, we can manually issue a checkpoint.
> > > >
> > > > We have incorporated a checkpoint to run every 15 minutes. We also
> have
> > an
> > > > alert that will catch a log at 80% full and then issues a checkpoint
> on
> > > that
> > > > database. But, we want to figure out what is going on and what is
> > causing
> > > > this.
> > > >
> > > > Any ideas?
> > > >
> > > > Michelle
> > > >
> > > >
> > >
> > >
> >
> >
>

Checkpointing Not Happening in Simple Recovery Model

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

Friday, February 24, 2012

Checking the AS Edition

Hi,

How do i check if the AS edition (Standard, Enterprise etc.) in my PC?

thanks,

cherriesh

Hi,

Right click on the analyis server node in SSMS. Select reports, standard reports, general. This will generate a report with the edition and the version of the analysis server.

Thanks

Giles

|||

What tool would you use?

If it would be custome code in .Net - use the AMO.Server object

Server lServer = new Server()

lServer.Connect("local");

long lEditionId = mServer.EditionID;

ServerEdition lEdition = mServer.Edition;

Thursday, March 22, 2012

Classic ASP in Report Manager

Class does not support aggregation

Monday, March 19, 2012

Choosing DB Edition (Std vs Ent)

Sunday, March 11, 2012

Chinese DB

Thursday, March 8, 2012

Chinese characters

Wednesday, March 7, 2012

Checkpointing Not Happening in Simple Recovery Model

Checkpointing Not Happening in Simple Recovery Model

Friday, February 24, 2012

Checking the AS Edition

check sql licence

Blog Archive

About Me