A couple of easy questions

Hi everyone,

Running Windows 2003 x64 and Sql25k 64-bit edition

We just have a couple of questions related with our system:

-What is Sql Server VSS Writer service for?

-What sort of implications must we assume when Integration Services service is running as NT AUTHORITY\NetworkService

Any link or advice would be welcomed.

Thanks for your time,

SQL Writer Service
(http://msdn2.microsoft.com/en-us/library/ms175536.aspx)

Setting Up Windows Service Accounts
(http://msdn2.microsoft.com/en-us/library/309b9dac-0b3a-4617-85ef-c4519ce9d014.aspx)

For SSIS the implications are much the same as any service, just bear in mind what the SSIS service does, not a lot really.

|||Thank you|||Be sure to mark the answers to your questions as an answer to the thread.

A complete uninstall of SQL 2005

I recently re-built my entire system from ground up (new hard drives, OS, etc). I now have the problem of SQL 2005 installed partially, but no way to get the management tools installed. I get an "already installed message when I try". I've never encounted this before on any other SQL install.

Sequence of events. I installed VS 2005 Pro first, then followed by SQL 2005. Somewhere this install didn't work, and I was left with the MS installed thinking I suceeded, yet none of the functions of SQL were worked or more importantly accessable.

I tried to re-install after using the ADD/Remove App from the control panel (which was sucessfull). SQL thought it was still installed. So I uninstalled VS 2005 as well, and deleted any programs in the SQL directory. Then I checked ADD/Remove programs and nothing was showing. The re-install still did not work. My guess is that the registry is still loaded with now useless entries.

Is there any solution left that does not require me to hand delete any SQL registry entries, since that is always a great way to completely kill my system.

Thanks.

Please send email to me and I will forward a script to clean those installed components.

A bit of Crystal Reports 8.5 help

Hi all, first post!

I'm working for a company that runs a business management system which uses an old version of Crystal Reports (version 8.5!) for reporting on pretty much anything the system can output.

I don't know how much it differs from the newer versions, but i'm new to the system (two days experience and counting) and I'm after a couple of pointers.

1) I am trying to output stock levels for products which are both in and out of stock. Now, due to some sillyness when the BMS was designed, if a product is out of stock, it has doesn't have a row in the bins_lots table (where stock levels are kept), rather than 0.
Therefore, if I do a report which outputs the stock levels of a product (part, items that are out of stock are missing from the report, rather than having a 0 next to their name.
Is it possible to tell Crystal that if a product exists but doesn't have an entry in bins_lots (where stock amounts are kept) then it should be 0?
I'd find this easy if it were a normal If Statement, but Crystal has to be difficult with its wierd way of doing conditionals.

2) Secondly, again due to the BMS design, if a product is on order, the amount in the order is logged in multiple purchase orders. Producing a query will give me duplicate entries for products. I know I can 'hide duplicates', but it results in a messy report.
Can I make Crystal add all of these numbers together (i.e. where product = 'AD-03', add quantity of due products together) and output the final result without using the subtotal/group functions.

All help appreciated :)1) Left join to the bins_lots table. When the amount column is null then there's no stock.
e.g. a formula to return the stock level would be (assuming your column is called amount)
if isnull({bins_lots.amount}) then 0 else {bins_lots.amount}

2) Well, I don't really know how you're going to add numbers without grouping / total functions. Why is grouping an issue? Why can't you group on the product, hide the detail, and print in the product group footer to get 1 record per product? Nothing messy there.

A better way to handle repeating dates

I am a developer who works with MS SQL Server a lot, but I'm far from
an expert. I am revamping an appointment scheduling system that
allows for appointments to reoccur daily, weekly, monthly and yearly.

Currently, I am saving the appointment date as the initial appointment
date. Then when I want to check for appointments, my stored proc does
does a select on the appropriate records fitting certain critieria
(like only appointments for this doctor, at this location, etc). Once
I have these records I cycle through them calling the DateAdd() and
DateDiff() functions to see if the appointment is reoccuring during
the dates I'm looking for.

Here's is a mock up of what I'm doing. I know cursors are a huge hit
performance-wise (especially how they are used in this scenario) and
want to get a way from this, but I can't figure out how to get
reoccuring appointments to work. Any help is appreciated. Thanks.

sp_GetAppointments(@.StartDate, @.EndDate)

set @.DateToCheck = @.StartApptDate
while @.DateToCheck <= @.EndApptDate
begin
--Start a cursor
DECLARE RepeatCursor CURSOR
FORWARD_ONLY STATIC FOR

select ApptDate from ApptTable where DoctorID = 1 and

--Check if it repeats daily
((repeat = 1 and
DateAdd(d,DateDiff(d,ApptDate,@.DateToCheck),ApptDa te) =
@.DateToCheck
and DateDiff(d,ApptDate,@.DateToCheck) >0)

--Check if it repeats weekly
or (repeat = 2 and
DateAdd(wk,DateDiff(wk,ApptDate,@.DateToCheck),Appt Date) =
@.DateToCheck
and DateDiff(d,ApptDate,@.DateToCheck) >0)

CLOSE RepeatCursor
DEALLOCATE RepeatCursor

set @.DateToCheck = DateAdd(d,1,@.DateToCheck)
end"Dean" <daudirsch@.hotmail.com> wrote in message
news:1f9c615a.0407140934.33853b8d@.posting.google.c om...
> I am a developer who works with MS SQL Server a lot, but I'm far from
> an expert. I am revamping an appointment scheduling system that
> allows for appointments to reoccur daily, weekly, monthly and yearly.
> Currently, I am saving the appointment date as the initial appointment
> date. Then when I want to check for appointments, my stored proc does
> does a select on the appropriate records fitting certain critieria
> (like only appointments for this doctor, at this location, etc). Once
> I have these records I cycle through them calling the DateAdd() and
> DateDiff() functions to see if the appointment is reoccuring during
> the dates I'm looking for.
> Here's is a mock up of what I'm doing. I know cursors are a huge hit
> performance-wise (especially how they are used in this scenario) and
> want to get a way from this, but I can't figure out how to get
> reoccuring appointments to work. Any help is appreciated. Thanks.
> sp_GetAppointments(@.StartDate, @.EndDate)
> set @.DateToCheck = @.StartApptDate
> while @.DateToCheck <= @.EndApptDate
> begin
> --Start a cursor
> DECLARE RepeatCursor CURSOR
> FORWARD_ONLY STATIC FOR
> select ApptDate from ApptTable where DoctorID = 1 and
> --Check if it repeats daily
> ((repeat = 1 and
> DateAdd(d,DateDiff(d,ApptDate,@.DateToCheck),ApptDa te) =
> @.DateToCheck
> and DateDiff(d,ApptDate,@.DateToCheck) >0)
> --Check if it repeats weekly
> or (repeat = 2 and
> DateAdd(wk,DateDiff(wk,ApptDate,@.DateToCheck),Appt Date) =
> @.DateToCheck
> and DateDiff(d,ApptDate,@.DateToCheck) >0)
> CLOSE RepeatCursor
> DEALLOCATE RepeatCursor
> set @.DateToCheck = DateAdd(d,1,@.DateToCheck)
> end

I'm not sure that I see how you identify an appointment from the information
above, since it seems that you're only looking at dates. If the doctor has
an appointment today, and one in a week, how does he know if they're related
or unrelated? And what about the time of day?

In any case, some standard advice would be to remove the sp_ prefix, which
is reserved for system stored procedures, and to investigate using a
calendar table to help you with date-related queries. For more specific
advice, you will have to give more details, and someone may be able to
suggest something - CREATE TABLE statements for the tables you're looking at
(perhaps simplified), INSERT statements for sample data, and then the output
you would like to have. But if your business requirements are complex, it
may be tricky to resolve in a newsgroup.

Simon|||As Simon has suggested, some more info would help us understand your
requirements better.

Here's a simplified example of how you could generate repeating appointments
without a cursor.

CREATE TABLE Appointments (doctorid INTEGER NOT NULL, start_dt DATETIME,
end_dt DATETIME NOT NULL, CHECK (start_dt<end_dt), repeat INTEGER NOT NULL
DEFAULT 1 CHECK (repeat>0), repeat_days INTEGER NOT NULL DEFAULT 0,
repeat_months INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (doctorid, start_dt))

The Repeat column defines how many times an appointment occurs and the
Repeat_Days / Repeat_Months columns define the interval either in months or
days.

Here are some sample appointments:

INSERT INTO Appointments VALUES /* Single appointment */
(1,'2004-01-15T10:00:00.000','2004-01-15T10:30:00.000',1,0,0)

INSERT INTO Appointments VALUES /* Weekly for 5 weeks */
(2,'2004-01-16T12:30:00.000','2004-01-16T13:30:00.000',5,7,0)

INSERT INTO Appointments VALUES /* Monthly for 6 months */
(3,'2004-02-01T14:30:00.000','2004-02-01T15:30:00.000',6,0,1)

Here's the query to generate the repeating appointments (you need to create
an auxiliary Numbers table first:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp)

SELECT doctorid,
DATEADD(MONTH,(N.number-1)*repeat_months,
DATEADD(DAY,(N.number-1)*repeat_days, A.start_dt)),
DATEADD(MONTH,(N.number-1)*repeat_months,
DATEADD(DAY,(N.number-1)*repeat_days, A.end_dt))
FROM Appointments AS A
JOIN Numbers AS N
ON N.number BETWEEN 1 AND A.repeat

Whether it then makes sense to insert this result into another table or just
extrapolate the appointments with this query as needed really depends on
your business requirements.

Hope this helps.

--
David Portas
SQL Server MVP
--|||>> I am revamping an appointment scheduling system that allows for
appointments to reoccur daily, weekly, monthly and yearly. <<

The first problem you have is your mental model. Look at the words in
your specs!

>> Currently, I am saving the appointment date as the initial [sic]
appointment date. Then when I want to check for appointments, my
stored proc does does a select on the appropriate records [sic]
fitting certain critieria ... Once I have these records [sic] I cycle
[sic] through them calling the DateAdd() and DateDiff() functions
[sic] to see if the appointment is reoccuring during the dates I'm
looking for. <<

Rows are not records. Cycles (loops) are procedural. We prefer data
that holds all the facts over functions and computations that build
them on the fly.

When you make the appointment, it is not one appointment; you are
making a set of appointments ("Well, Mr. Celko, we'll see you here
every other week until you die, or your insurance gives out for the
next five years!").

Use a calendar table for the schedules so that nobody gets a check up
on Christmas and New Years. You can also predict when a doctor is
going to be overloaded in advance and prevent it. Pull out a base
schedule from the calendar table, add the client and doctor, and then
modify it as you need to later in time ("I'm too sick to come to
chemotherapy today!"). This ad hoc change is the way this is really
done.

Worse case? A daily visit for 10 years in advance costs you (365.2422
*10 rows) = 3653 rows of (datetime, patient, doctor) data in the
appointment table. It lets me replace one doctor for another in
advance, too.|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<X5adnZu_EoBOEGjdRVn-gg@.giganews.com>...
> As Simon has suggested, some more info would help us understand your
> requirements better.
> Here's a simplified example of how you could generate repeating appointments
> without a cursor.
> CREATE TABLE Appointments (doctorid INTEGER NOT NULL, start_dt DATETIME,
> end_dt DATETIME NOT NULL, CHECK (start_dt<end_dt), repeat INTEGER NOT NULL
> DEFAULT 1 CHECK (repeat>0), repeat_days INTEGER NOT NULL DEFAULT 0,
> repeat_months INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (doctorid, start_dt))
> The Repeat column defines how many times an appointment occurs and the
> Repeat_Days / Repeat_Months columns define the interval either in months or
> days.
> Here are some sample appointments:
> INSERT INTO Appointments VALUES /* Single appointment */
> (1,'2004-01-15T10:00:00.000','2004-01-15T10:30:00.000',1,0,0)
> INSERT INTO Appointments VALUES /* Weekly for 5 weeks */
> (2,'2004-01-16T12:30:00.000','2004-01-16T13:30:00.000',5,7,0)
> INSERT INTO Appointments VALUES /* Monthly for 6 months */
> (3,'2004-02-01T14:30:00.000','2004-02-01T15:30:00.000',6,0,1)
> Here's the query to generate the repeating appointments (you need to create
> an auxiliary Numbers table first:
> http://www.bizdatasolutions.com/tsql/tblnumbers.asp)
> SELECT doctorid,
> DATEADD(MONTH,(N.number-1)*repeat_months,
> DATEADD(DAY,(N.number-1)*repeat_days, A.start_dt)),
> DATEADD(MONTH,(N.number-1)*repeat_months,
> DATEADD(DAY,(N.number-1)*repeat_days, A.end_dt))
> FROM Appointments AS A
> JOIN Numbers AS N
> ON N.number BETWEEN 1 AND A.repeat
> Whether it then makes sense to insert this result into another table or just
> extrapolate the appointments with this query as needed really depends on
> your business requirements.
> Hope this helps.

Thanks to all for the assistance, it really helped.

99.99% without a cluster ?

Is it realistic expect 99.99% reliability and system uptime without using
any clustering solutions on a SQL 2000 db ?
My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
connected to individual switches for redudancy. My organization is 8 h/day
shop and I have plenty of time for maintenance during weekends or nights.
I am asking because some co-workers would like to cluster 4 SQL servers and
I have some questions if that is worthwhile and necessary in my environment.
Clustering is for hardware failures. If your motherboard or processor dies
you are down until you can replace it. If you can do that within a time
that is acceptable to your business then great, otherwise something like
clustereing or log shipping will get you going a lot faster.
Andrew J. Kelly SQL MVP
"Marlon Brown" <marlon_brownj@.hotmail.com> wrote in message
news:OF4IjVMLEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is it realistic expect 99.99% reliability and system uptime without using
> any clustering solutions on a SQL 2000 db ?
> My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
> connected to individual switches for redudancy. My organization is 8
h/day
> shop and I have plenty of time for maintenance during weekends or nights.
> I am asking because some co-workers would like to cluster 4 SQL servers
and
> I have some questions if that is worthwhile and necessary in my
environment.
>
|||It's "possible", but not "realistic" unless you're a zen master (:
You need to ask (at least) these questions:
(a) what is our REAL availability requirement
(b) what is our real cost of downtime
(c) what solutions are available to me (there is more than just clustering)
(d) what are the TOTAL costs associated with deploying each solutions?
(e) which solution (c) costs least (d) to limit downtime (b) whilst
achieving at least (a)
Regards,
Greg Linwood
SQL Server MVP
"Marlon Brown" <marlon_brownj@.hotmail.com> wrote in message
news:OF4IjVMLEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is it realistic expect 99.99% reliability and system uptime without using
> any clustering solutions on a SQL 2000 db ?
> My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
> connected to individual switches for redudancy. My organization is 8
h/day
> shop and I have plenty of time for maintenance during weekends or nights.
> I am asking because some co-workers would like to cluster 4 SQL servers
and
> I have some questions if that is worthwhile and necessary in my
environment.
>
|||What do you mean by 99.99%? Are you counting total uptime, or just
unplanned outages? We don't have 99.99% uptime measured as a total, even
with a cluster and I'm not worried at all. That's not to say that our
servers (Dell 6650's) aren't reliable, they are; we've only had one
unplanned outage in the past year and a half and that was only for about 2
minutes as the cluster failed over... However, between hardware
maintenance/upgrades, and patches--both OS and SQL Server, we have managed
about 99.93% total uptime. But we have managed 100% uptime for business
hours over the past 11 months.
What we have found to be the biggest benefit of clustering is the ability to
do rolling upgrades, thereby reducing the total outage window for any
particular upgrade.
"Marlon Brown" <marlon_brownj@.hotmail.com> wrote in message
news:OF4IjVMLEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is it realistic expect 99.99% reliability and system uptime without using
> any clustering solutions on a SQL 2000 db ?
> My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
> connected to individual switches for redudancy. My organization is 8
h/day
> shop and I have plenty of time for maintenance during weekends or nights.
> I am asking because some co-workers would like to cluster 4 SQL servers
and
> I have some questions if that is worthwhile and necessary in my
environment.
>

99.99% without a cluster ?

Is it realistic expect 99.99% reliability and system uptime without using
any clustering solutions on a SQL 2000 db ?
My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
connected to individual switches for redudancy. My organization is 8 h/day
shop and I have plenty of time for maintenance during weekends or nights.
I am asking because some co-workers would like to cluster 4 SQL servers and
I have some questions if that is worthwhile and necessary in my environment.Clustering is for hardware failures. If your motherboard or processor dies
you are down until you can replace it. If you can do that within a time
that is acceptable to your business then great, otherwise something like
clustereing or log shipping will get you going a lot faster.
--
Andrew J. Kelly SQL MVP
"Marlon Brown" <marlon_brownj@.hotmail.com> wrote in message
news:OF4IjVMLEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is it realistic expect 99.99% reliability and system uptime without using
> any clustering solutions on a SQL 2000 db ?
> My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
> connected to individual switches for redudancy. My organization is 8
h/day
> shop and I have plenty of time for maintenance during weekends or nights.
> I am asking because some co-workers would like to cluster 4 SQL servers
and
> I have some questions if that is worthwhile and necessary in my
environment.
>|||It's "possible", but not "realistic" unless you're a zen master (:
You need to ask (at least) these questions:
(a) what is our REAL availability requirement
(b) what is our real cost of downtime
(c) what solutions are available to me (there is more than just clustering)
(d) what are the TOTAL costs associated with deploying each solutions?
(e) which solution (c) costs least (d) to limit downtime (b) whilst
achieving at least (a)
Regards,
Greg Linwood
SQL Server MVP
"Marlon Brown" <marlon_brownj@.hotmail.com> wrote in message
news:OF4IjVMLEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is it realistic expect 99.99% reliability and system uptime without using
> any clustering solutions on a SQL 2000 db ?
> My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
> connected to individual switches for redudancy. My organization is 8
h/day
> shop and I have plenty of time for maintenance during weekends or nights.
> I am asking because some co-workers would like to cluster 4 SQL servers
and
> I have some questions if that is worthwhile and necessary in my
environment.
>|||What do you mean by 99.99%? Are you counting total uptime, or just
unplanned outages? We don't have 99.99% uptime measured as a total, even
with a cluster and I'm not worried at all. That's not to say that our
servers (Dell 6650's) aren't reliable, they are; we've only had one
unplanned outage in the past year and a half and that was only for about 2
minutes as the cluster failed over... However, between hardware
maintenance/upgrades, and patches--both OS and SQL Server, we have managed
about 99.93% total uptime. But we have managed 100% uptime for business
hours over the past 11 months.
What we have found to be the biggest benefit of clustering is the ability to
do rolling upgrades, thereby reducing the total outage window for any
particular upgrade.
"Marlon Brown" <marlon_brownj@.hotmail.com> wrote in message
news:OF4IjVMLEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is it realistic expect 99.99% reliability and system uptime without using
> any clustering solutions on a SQL 2000 db ?
> My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
> connected to individual switches for redudancy. My organization is 8
h/day
> shop and I have plenty of time for maintenance during weekends or nights.
> I am asking because some co-workers would like to cluster 4 SQL servers
and
> I have some questions if that is worthwhile and necessary in my
environment.
>

99.99% without a cluster ?

Is it realistic expect 99.99% reliability and system uptime without using
any clustering solutions on a SQL 2000 db ?
My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
connected to individual switches for redudancy. My organization is 8 h/day
shop and I have plenty of time for maintenance during weekends or nights.
I am asking because some co-workers would like to cluster 4 SQL servers and
I have some questions if that is worthwhile and necessary in my environment.Clustering is for hardware failures. If your motherboard or processor dies
you are down until you can replace it. If you can do that within a time
that is acceptable to your business then great, otherwise something like
clustereing or log shipping will get you going a lot faster.
Andrew J. Kelly SQL MVP
"Marlon Brown" <marlon_brownj@.hotmail.com> wrote in message
news:OF4IjVMLEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is it realistic expect 99.99% reliability and system uptime without using
> any clustering solutions on a SQL 2000 db ?
> My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
> connected to individual switches for redudancy. My organization is 8
h/day
> shop and I have plenty of time for maintenance during weekends or nights.
> I am asking because some co-workers would like to cluster 4 SQL servers
and
> I have some questions if that is worthwhile and necessary in my
environment.
>|||It's "possible", but not "realistic" unless you're a zen master (:
You need to ask (at least) these questions:
(a) what is our REAL availability requirement
(b) what is our real cost of downtime
(c) what solutions are available to me (there is more than just clustering)
(d) what are the TOTAL costs associated with deploying each solutions?
(e) which solution (c) costs least (d) to limit downtime (b) whilst
achieving at least (a)
Regards,
Greg Linwood
SQL Server MVP
"Marlon Brown" <marlon_brownj@.hotmail.com> wrote in message
news:OF4IjVMLEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is it realistic expect 99.99% reliability and system uptime without using
> any clustering solutions on a SQL 2000 db ?
> My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
> connected to individual switches for redudancy. My organization is 8
h/day
> shop and I have plenty of time for maintenance during weekends or nights.
> I am asking because some co-workers would like to cluster 4 SQL servers
and
> I have some questions if that is worthwhile and necessary in my
environment.
>|||What do you mean by 99.99%? Are you counting total uptime, or just
unplanned outages? We don't have 99.99% uptime measured as a total, even
with a cluster and I'm not worried at all. That's not to say that our
servers (Dell 6650's) aren't reliable, they are; we've only had one
unplanned outage in the past year and a half and that was only for about 2
minutes as the cluster failed over... However, between hardware
maintenance/upgrades, and patches--both OS and SQL Server, we have managed
about 99.93% total uptime. But we have managed 100% uptime for business
hours over the past 11 months.
What we have found to be the biggest benefit of clustering is the ability to
do rolling upgrades, thereby reducing the total outage window for any
particular upgrade.
"Marlon Brown" <marlon_brownj@.hotmail.com> wrote in message
news:OF4IjVMLEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Is it realistic expect 99.99% reliability and system uptime without using
> any clustering solutions on a SQL 2000 db ?
> My server hardware is dual proc, raid1=OS, raid5=db, dual nic teamed and
> connected to individual switches for redudancy. My organization is 8
h/day
> shop and I have plenty of time for maintenance during weekends or nights.
> I am asking because some co-workers would like to cluster 4 SQL servers
and
> I have some questions if that is worthwhile and necessary in my
environment.
>

8007007F error

I have searched this forum about the subject error, but no updated soluation.
The system is windows2000 advanced server SP3 with SQL2000 standard edition SP3. I did a same computer upgrade to windows2003 enterprise edition. After upgrading, the above error appears when I try to view table data from enterprise manager. This problem happens only on the server locally.
I suspect MDAC version is the cause. But MDAC could not be downgraded. Anyone could help?
Thanks in advance, mikeI found the answer by searching the internet forum.
1. here is the link to the detailed steps
http://dbforums.com/t763447.html
2. instead of the dll file in above steps, replacing it with the latest MDAC2.8 version.
That is it!
The problem is that when upgrading from Windows 2000 server to 2003 server the oledb32.dll didnâ't not get updated with the current version from mdac 2.8.

64gb memory - recommendations, etc..

Multi proc server with 64gb memory.
Applications that use SQL Server 2000 & 2005 run on this system.
Can someone point me to a site / document / any material that has
information about best practices, recommendations, etc about configuring SQL
Server to use this much of memory ?
Cheers
sqlcatzHere are some items that you can use to shed light on the issues with large
memory.
Configuration -Memory, Large Memory Support Is Available in Windows 2000
(AWE)
http://www.support.microsoft.com/?id=283037
Configuration -Memory, SQL Server 7 & 2000 memory usage
http://www.support.microsoft.com/?id=321363
Configuration -Memory, SQL Server Memory
http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
Configuration -Memory, SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750/
Configuration -Memory, Using AWE Memory
http://www.sql-server-performance.com/awe_memory.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:F7C8E4BB-39A2-4898-88B3-AC0BEA469E19@.microsoft.com...
> Multi proc server with 64gb memory.
> Applications that use SQL Server 2000 & 2005 run on this system.
> Can someone point me to a site / document / any material that has
> information about best practices, recommendations, etc about configuring
> SQL
> Server to use this much of memory ?
> Cheers
> sqlcatz
>

64gb memory - recommendations, etc..

Multi proc server with 64gb memory.
Applications that use SQL Server 2000 & 2005 run on this system.
Can someone point me to a site / document / any material that has
information about best practices, recommendations, etc about configuring SQL
Server to use this much of memory ?
Cheers
sqlcatz
Here are some items that you can use to shed light on the issues with large
memory.
Configuration -Memory, Large Memory Support Is Available in Windows 2000
(AWE)
http://www.support.microsoft.com/?id=283037
Configuration -Memory, SQL Server 7 & 2000 memory usage
http://www.support.microsoft.com/?id=321363
Configuration -Memory, SQL Server Memory
http://sqljunkies.com/Tutorial/0D4FF...2FE2D58F6.scuk
Configuration -Memory, SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750/
Configuration -Memory, Using AWE Memory
http://www.sql-server-performance.com/awe_memory.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:F7C8E4BB-39A2-4898-88B3-AC0BEA469E19@.microsoft.com...
> Multi proc server with 64gb memory.
> Applications that use SQL Server 2000 & 2005 run on this system.
> Can someone point me to a site / document / any material that has
> information about best practices, recommendations, etc about configuring
> SQL
> Server to use this much of memory ?
> Cheers
> sqlcatz
>

64gb memory - recommendations, etc..

Multi proc server with 64gb memory.
Applications that use SQL Server 2000 & 2005 run on this system.
Can someone point me to a site / document / any material that has
information about best practices, recommendations, etc about configuring SQL
Server to use this much of memory ?
Cheers
sqlcatzHere are some items that you can use to shed light on the issues with large
memory.
Configuration -Memory, Large Memory Support Is Available in Windows 2000
(AWE)
http://www.support.microsoft.com/?id=283037
Configuration -Memory, SQL Server 7 & 2000 memory usage
http://www.support.microsoft.com/?id=321363
Configuration -Memory, SQL Server Memory
http://sqljunkies.com/Tutorial/0D4F...F2FE2D58F6.scuk
Configuration -Memory, SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750/
Configuration -Memory, Using AWE Memory
http://www.sql-server-performance.com/awe_memory.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:F7C8E4BB-39A2-4898-88B3-AC0BEA469E19@.microsoft.com...
> Multi proc server with 64gb memory.
> Applications that use SQL Server 2000 & 2005 run on this system.
> Can someone point me to a site / document / any material that has
> information about best practices, recommendations, etc about configuring
> SQL
> Server to use this much of memory ?
> Cheers
> sqlcatz
>

64-bit restore with error

Hi all,
I am testing SQL Server 2000 64-bit on Itanium2 and have hit a problem.
Our current production system is running SQL Server 2000 32-bit SP3a. I
backed up the database (about 200+ GB) and restored it on SQL Server 2000
64-bit. After the restore was completed, I did a dbcc_checkdb and it came
back with various errors (see below for sample). I repeated the restore from
the same backup on another 32-bit box and dbcc checkdb came out clean. Any
idea what's causing the errors in dbcc checkdb on the 64-bit restore?
They're fixable using the repair option but it takes a long time and it
isn't really inspiring confidence in our management.
Btw, OS on both is Windows Server 2003 Enterprise SP1.
Any help will be much appreciated.
Thanks!
aK.
Small snippet of error messages:
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:76) with values (DetailActivityCurID = 55833475 and
WorkorderCurID = 316630 and SetTypeID = 4869 and ? = NULL) points to the
data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:77) with values (DetailActivityCurID = 55833476 and
WorkorderCurID = 316630 and SetTypeID = 4870 and ? = NULL) points to the
data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:78) with values (DetailActivityCurID = 55833477 and
WorkorderCurID = 316630 and SetTypeID = 4869 and ? = 1) points to the data
row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:79) with values (DetailActivityCurID = 55833478 and
WorkorderCurID = 316630 and SetTypeID = 4870 and ? = 1) points to the data
row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
(ID 1218103380) (index ID 4). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4447340:155) with values (ActivityDtime = Jul 14 2005 4:47PM
and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = NULL) points to the
data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
(ID 1218103380) (index ID 4). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4447340:160) with values (ActivityDtime = Jul 14 2005 4:47PM
and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = 1) points to the
data row identified by ().
Server: Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1218103380, index ID 5 will be rebuilt.
Hi
This looks like:
http://support.microsoft.com/default...b;en-us;884856
You may want to upgrade the 32 bit edition to SP4 and then migrate (to the
same version).
John
"Angie" <NoSpam_angie_kong@.hotmail.NoSpam.com> wrote in message
news:OPpLxmYwFHA.908@.tk2msftngp13.phx.gbl...
> Hi all,
> I am testing SQL Server 2000 64-bit on Itanium2 and have hit a problem.
> Our current production system is running SQL Server 2000 32-bit SP3a. I
> backed up the database (about 200+ GB) and restored it on SQL Server 2000
> 64-bit. After the restore was completed, I did a dbcc_checkdb and it came
> back with various errors (see below for sample). I repeated the restore
> from the same backup on another 32-bit box and dbcc checkdb came out
> clean. Any idea what's causing the errors in dbcc checkdb on the 64-bit
> restore? They're fixable using the repair option but it takes a long time
> and it isn't really inspiring confidence in our management.
> Btw, OS on both is Windows Server 2003 Enterprise SP1.
> Any help will be much appreciated.
> Thanks!
> aK.
>
> Small snippet of error messages:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:76) with values (DetailActivityCurID = 55833475 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:77) with values (DetailActivityCurID = 55833476 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:78) with values (DetailActivityCurID = 55833477 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:79) with values (DetailActivityCurID = 55833478 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:155) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = NULL) points to
> the data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:160) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = 1) points to the
> data row identified by ().
> Server: Msg 8945, Level 16, State 1, Line 1
> Table error: Object ID 1218103380, index ID 5 will be rebuilt.
>
|||This should process should work fine. What builds are the servers on? SP3a
or above and having the same build number on both servers will probably make
these go away.
"Angie" <NoSpam_angie_kong@.hotmail.NoSpam.com> wrote in message
news:OPpLxmYwFHA.908@.tk2msftngp13.phx.gbl...
> Hi all,
> I am testing SQL Server 2000 64-bit on Itanium2 and have hit a problem.
> Our current production system is running SQL Server 2000 32-bit SP3a. I
> backed up the database (about 200+ GB) and restored it on SQL Server 2000
> 64-bit. After the restore was completed, I did a dbcc_checkdb and it came
> back with various errors (see below for sample). I repeated the restore
> from the same backup on another 32-bit box and dbcc checkdb came out
> clean. Any idea what's causing the errors in dbcc checkdb on the 64-bit
> restore? They're fixable using the repair option but it takes a long time
> and it isn't really inspiring confidence in our management.
> Btw, OS on both is Windows Server 2003 Enterprise SP1.
> Any help will be much appreciated.
> Thanks!
> aK.
>
> Small snippet of error messages:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:76) with values (DetailActivityCurID = 55833475 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:77) with values (DetailActivityCurID = 55833476 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:78) with values (DetailActivityCurID = 55833477 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:79) with values (DetailActivityCurID = 55833478 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:155) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = NULL) points to
> the data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:160) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = 1) points to the
> data row identified by ().
> Server: Msg 8945, Level 16, State 1, Line 1
> Table error: Object ID 1218103380, index ID 5 will be rebuilt.
>

64-bit restore with error

Hi all,
I am testing SQL Server 2000 64-bit on Itanium2 and have hit a problem.
Our current production system is running SQL Server 2000 32-bit SP3a. I
backed up the database (about 200+ GB) and restored it on SQL Server 2000
64-bit. After the restore was completed, I did a dbcc_checkdb and it came
back with various errors (see below for sample). I repeated the restore from
the same backup on another 32-bit box and dbcc checkdb came out clean. Any
idea what's causing the errors in dbcc checkdb on the 64-bit restore?
They're fixable using the repair option but it takes a long time and it
isn't really inspiring confidence in our management.
Btw, OS on both is Windows Server 2003 Enterprise SP1.
Any help will be much appreciated.
Thanks!
aK.
Small snippet of error messages:
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:76) with values (DetailActivityCurID = 55833475 and
WorkorderCurID = 316630 and SetTypeID = 4869 and ? = NULL) points to the
data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:77) with values (DetailActivityCurID = 55833476 and
WorkorderCurID = 316630 and SetTypeID = 4870 and ? = NULL) points to the
data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:78) with values (DetailActivityCurID = 55833477 and
WorkorderCurID = 316630 and SetTypeID = 4869 and ? = 1) points to the data
row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:79) with values (DetailActivityCurID = 55833478 and
WorkorderCurID = 316630 and SetTypeID = 4870 and ? = 1) points to the data
row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
(ID 1218103380) (index ID 4). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4447340:155) with values (ActivityDtime = Jul 14 2005 4:47PM
and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = NULL) points to the
data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
(ID 1218103380) (index ID 4). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4447340:160) with values (ActivityDtime = Jul 14 2005 4:47PM
and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = 1) points to the
data row identified by ().
Server: Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1218103380, index ID 5 will be rebuilt.Hi
This looks like:
http://support.microsoft.com/defaul...kb;en-us;884856
You may want to upgrade the 32 bit edition to SP4 and then migrate (to the
same version).
John
"Angie" <NoSpam_angie_kong@.hotmail.NoSpam.com> wrote in message
news:OPpLxmYwFHA.908@.tk2msftngp13.phx.gbl...
> Hi all,
> I am testing SQL Server 2000 64-bit on Itanium2 and have hit a problem.
> Our current production system is running SQL Server 2000 32-bit SP3a. I
> backed up the database (about 200+ GB) and restored it on SQL Server 2000
> 64-bit. After the restore was completed, I did a dbcc_checkdb and it came
> back with various errors (see below for sample). I repeated the restore
> from the same backup on another 32-bit box and dbcc checkdb came out
> clean. Any idea what's causing the errors in dbcc checkdb on the 64-bit
> restore? They're fixable using the repair option but it takes a long time
> and it isn't really inspiring confidence in our management.
> Btw, OS on both is Windows Server 2003 Enterprise SP1.
> Any help will be much appreciated.
> Thanks!
> aK.
>
> Small snippet of error messages:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:76) with values (DetailActivityCurID = 55833475 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:77) with values (DetailActivityCurID = 55833476 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:78) with values (DetailActivityCurID = 55833477 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:79) with values (DetailActivityCurID = 55833478 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:155) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = NULL) points to
> the data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:160) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = 1) points to the
> data row identified by ().
> Server: Msg 8945, Level 16, State 1, Line 1
> Table error: Object ID 1218103380, index ID 5 will be rebuilt.
>|||This should process should work fine. What builds are the servers on? SP3a
or above and having the same build number on both servers will probably make
these go away.
"Angie" <NoSpam_angie_kong@.hotmail.NoSpam.com> wrote in message
news:OPpLxmYwFHA.908@.tk2msftngp13.phx.gbl...
> Hi all,
> I am testing SQL Server 2000 64-bit on Itanium2 and have hit a problem.
> Our current production system is running SQL Server 2000 32-bit SP3a. I
> backed up the database (about 200+ GB) and restored it on SQL Server 2000
> 64-bit. After the restore was completed, I did a dbcc_checkdb and it came
> back with various errors (see below for sample). I repeated the restore
> from the same backup on another 32-bit box and dbcc checkdb came out
> clean. Any idea what's causing the errors in dbcc checkdb on the 64-bit
> restore? They're fixable using the repair option but it takes a long time
> and it isn't really inspiring confidence in our management.
> Btw, OS on both is Windows Server 2003 Enterprise SP1.
> Any help will be much appreciated.
> Thanks!
> aK.
>
> Small snippet of error messages:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:76) with values (DetailActivityCurID = 55833475 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:77) with values (DetailActivityCurID = 55833476 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:78) with values (DetailActivityCurID = 55833477 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:79) with values (DetailActivityCurID = 55833478 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:155) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = NULL) points to
> the data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:160) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = 1) points to the
> data row identified by ().
> Server: Msg 8945, Level 16, State 1, Line 1
> Table error: Object ID 1218103380, index ID 5 will be rebuilt.
>

64-bit restore with error

Hi all,
I am testing SQL Server 2000 64-bit on Itanium2 and have hit a problem.
Our current production system is running SQL Server 2000 32-bit SP3a. I
backed up the database (about 200+ GB) and restored it on SQL Server 2000
64-bit. After the restore was completed, I did a dbcc_checkdb and it came
back with various errors (see below for sample). I repeated the restore from
the same backup on another 32-bit box and dbcc checkdb came out clean. Any
idea what's causing the errors in dbcc checkdb on the 64-bit restore?
They're fixable using the repair option but it takes a long time and it
isn't really inspiring confidence in our management.
Btw, OS on both is Windows Server 2003 Enterprise SP1.
Any help will be much appreciated.
Thanks!
aK.
Small snippet of error messages:
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:76) with values (DetailActivityCurID = 55833475 and
WorkorderCurID = 316630 and SetTypeID = 4869 and ? = NULL) points to the
data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:77) with values (DetailActivityCurID = 55833476 and
WorkorderCurID = 316630 and SetTypeID = 4870 and ? = NULL) points to the
data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:78) with values (DetailActivityCurID = 55833477 and
WorkorderCurID = 316630 and SetTypeID = 4869 and ? = 1) points to the data
row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index
'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4242635:79) with values (DetailActivityCurID = 55833478 and
WorkorderCurID = 316630 and SetTypeID = 4870 and ? = 1) points to the data
row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
(ID 1218103380) (index ID 4). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4447340:155) with values (ActivityDtime = Jul 14 2005 4:47PM
and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = NULL) points to the
data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
(ID 1218103380) (index ID 4). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:4447340:160) with values (ActivityDtime = Jul 14 2005 4:47PM
and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = 1) points to the
data row identified by ().
Server: Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1218103380, index ID 5 will be rebuilt.Hi
This looks like:
http://support.microsoft.com/default.aspx?scid=kb;en-us;884856
You may want to upgrade the 32 bit edition to SP4 and then migrate (to the
same version).
John
"Angie" <NoSpam_angie_kong@.hotmail.NoSpam.com> wrote in message
news:OPpLxmYwFHA.908@.tk2msftngp13.phx.gbl...
> Hi all,
> I am testing SQL Server 2000 64-bit on Itanium2 and have hit a problem.
> Our current production system is running SQL Server 2000 32-bit SP3a. I
> backed up the database (about 200+ GB) and restored it on SQL Server 2000
> 64-bit. After the restore was completed, I did a dbcc_checkdb and it came
> back with various errors (see below for sample). I repeated the restore
> from the same backup on another 32-bit box and dbcc checkdb came out
> clean. Any idea what's causing the errors in dbcc checkdb on the 64-bit
> restore? They're fixable using the repair option but it takes a long time
> and it isn't really inspiring confidence in our management.
> Btw, OS on both is Windows Server 2003 Enterprise SP1.
> Any help will be much appreciated.
> Thanks!
> aK.
>
> Small snippet of error messages:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:76) with values (DetailActivityCurID = 55833475 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:77) with values (DetailActivityCurID = 55833476 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:78) with values (DetailActivityCurID = 55833477 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:79) with values (DetailActivityCurID = 55833478 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:155) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = NULL) points to
> the data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:160) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = 1) points to the
> data row identified by ().
> Server: Msg 8945, Level 16, State 1, Line 1
> Table error: Object ID 1218103380, index ID 5 will be rebuilt.
>|||This should process should work fine. What builds are the servers on? SP3a
or above and having the same build number on both servers will probably make
these go away.
"Angie" <NoSpam_angie_kong@.hotmail.NoSpam.com> wrote in message
news:OPpLxmYwFHA.908@.tk2msftngp13.phx.gbl...
> Hi all,
> I am testing SQL Server 2000 64-bit on Itanium2 and have hit a problem.
> Our current production system is running SQL Server 2000 32-bit SP3a. I
> backed up the database (about 200+ GB) and restored it on SQL Server 2000
> 64-bit. After the restore was completed, I did a dbcc_checkdb and it came
> back with various errors (see below for sample). I repeated the restore
> from the same backup on another 32-bit box and dbcc checkdb came out
> clean. Any idea what's causing the errors in dbcc checkdb on the 64-bit
> restore? They're fixable using the repair option but it takes a long time
> and it isn't really inspiring confidence in our management.
> Btw, OS on both is Windows Server 2003 Enterprise SP1.
> Any help will be much appreciated.
> Thanks!
> aK.
>
> Small snippet of error messages:
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:76) with values (DetailActivityCurID = 55833475 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:77) with values (DetailActivityCurID = 55833476 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = NULL) points to the
> data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:78) with values (DetailActivityCurID = 55833477 and
> WorkorderCurID = 316630 and SetTypeID = 4869 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index
> 'DetailActivityCur.pkDetailActivityCur_ID' (ID 1218103380) (index ID 2).
> Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4242635:79) with values (DetailActivityCurID = 55833478 and
> WorkorderCurID = 316630 and SetTypeID = 4870 and ? = 1) points to the data
> row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:155) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = NULL) points to
> the data row identified by ().
> Server: Msg 8952, Level 16, State 1, Line 1
> Table error: Database 'RSTBL1', index 'DetailActivityCur.akActivityDtime'
> (ID 1218103380) (index ID 4). Extra or invalid key for the keys:
> Server: Msg 8956, Level 16, State 1, Line 1
> Index row (1:4447340:160) with values (ActivityDtime = Jul 14 2005 4:47PM
> and WorkorderCurID = 316630 and SetTypeID = 4868 and ? = 1) points to the
> data row identified by ().
> Server: Msg 8945, Level 16, State 1, Line 1
> Table error: Object ID 1218103380, index ID 5 will be rebuilt.
>

64-bit

Hello,
I was wondering what kind of performance increase can I expect if i change
to a 64 bit system running sql server? What kind of problems can i run into
changing to sql 64 bit or only advantages... Are there benchmark tables
available on the internet?
thanks,
Tune-a
> I was wondering what kind of performance increase can I expect if i change
> to a 64 bit system running sql server?
Really depends on what you are doing... not everything is able to take
advantage of the advanced CPUs...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Which feautures of sql server 2000 are not capable of taking advantage of a
64 bit system?
At this moment 2 dual 3.2 Ghz (1.5 ram) xeons are handeling 800 kbits per
seconds of inserts (replication) and roundabout 2000 webusers querying both
databases.. We expect a groth of 50% on data inserts and also (at least) 50%
growth of webusers the next 4 months.
We have to make a decision about 'the new solution':
1. buy 2 big machines (32 or 64 bit), one for redundancy
2. buy more 'less expensive' machines and put loadbalancers in front of them
The easiest way is buying the big machines... If we go for that it's
important to know the pro's and con's about 64-bit machines in combination
with sql server...
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ONpBx6HMEHA.268@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
change
> Really depends on what you are doing... not everything is able to take
> advantage of the advanced CPUs...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
|||Tuna
I have been testing SQL Server performance on 64 bit in our company for over
two months
Look, try to perfom a large transactions especially if you are going to
deal with lots of INSERT's.To make the story shorten,
personally I did not get a feeeling that we should go with it. Almost the
same response time on the client site,
aslo when I backuped a database (15 gb) i was expected at least to speed up
the procsess on 64-bit but it was really the time. So as Aaaron says
:"Really depends on what you are doing... not everything is able to take
advantage of the advanced CPUs..."
"Tuna" <hier@.onetwotres.123> wrote in message
news:#bEiFJIMEHA.2500@.TK2MSFTNGP12.phx.gbl...
> Which feautures of sql server 2000 are not capable of taking advantage of
a
> 64 bit system?
> At this moment 2 dual 3.2 Ghz (1.5 ram) xeons are handeling 800 kbits per
> seconds of inserts (replication) and roundabout 2000 webusers querying
both
> databases.. We expect a groth of 50% on data inserts and also (at least)
50%
> growth of webusers the next 4 months.
> We have to make a decision about 'the new solution':
> 1. buy 2 big machines (32 or 64 bit), one for redundancy
> 2. buy more 'less expensive' machines and put loadbalancers in front of
them
> The easiest way is buying the big machines... If we go for that it's
> important to know the pro's and con's about 64-bit machines in combination
> with sql server...
>
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:ONpBx6HMEHA.268@.TK2MSFTNGP11.phx.gbl...
> change
>
|||"Tuna" <hier@.onetwotres.123> wrote in message
news:#bEiFJIMEHA.2500@.TK2MSFTNGP12.phx.gbl...

> Which feautures of sql server 2000 are not capable of taking advantage of
a
> 64 bit system?
> At this moment 2 dual 3.2 Ghz (1.5 ram) xeons are handeling 800 kbits per
> seconds of inserts (replication) and roundabout 2000 webusers querying
both
> databases.. We expect a groth of 50% on data inserts and also (at least)
50%
> growth of webusers the next 4 months.
> We have to make a decision about 'the new solution':
> 1. buy 2 big machines (32 or 64 bit), one for redundancy
> 2. buy more 'less expensive' machines and put loadbalancers in front of
them
> The easiest way is buying the big machines... If we go for that it's
> important to know the pro's and con's about 64-bit machines in combination
> with sql server...
you've made a few comments about what's going though the pipe but you
haven't said if there's a problem with it and, assuming there is a problem,
what you have found about where the problem may lie
the short story is there is not enough information here to consider the need
for 64 bit ... fwiw, my nose tells me that you probably do not need it ...
read the TPC benchmarks, flesh out the analysis; it's a completely empirical
problem
|||"Tuna" <hier@.onetwotres.123> wrote in message
news:OTNlD0HMEHA.3332@.TK2MSFTNGP10.phx.gbl...
> I was wondering what kind of performance increase can I expect if i change
> to a 64 bit system running sql server?
The main difference with 64bit is that SQL Server can directly access more
than 4GB of RAM.
So if you need more than 4GB of RAM then go for 64bit, otherwise consider
carefully where you should go for 64bit processors or would improving some
other aspect of your server spec be of more benefit, e.g. faster disks

64-bit

Hello,
I was wondering what kind of performance increase can I expect if i change
to a 64 bit system running sql server? What kind of problems can i run into
changing to sql 64 bit or only advantages... Are there benchmark tables
available on the internet?
thanks,
Tune-a> I was wondering what kind of performance increase can I expect if i change
> to a 64 bit system running sql server?
Really depends on what you are doing... not everything is able to take
advantage of the advanced CPUs...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Which feautures of sql server 2000 are not capable of taking advantage of a
64 bit system?
At this moment 2 dual 3.2 Ghz (1.5 ram) xeons are handeling 800 kbits per
seconds of inserts (replication) and roundabout 2000 webusers querying both
databases.. We expect a groth of 50% on data inserts and also (at least) 50%
growth of webusers the next 4 months.
We have to make a decision about 'the new solution':
1. buy 2 big machines (32 or 64 bit), one for redundancy
2. buy more 'less expensive' machines and put loadbalancers in front of them
The easiest way is buying the big machines... If we go for that it's
important to know the pro's and con's about 64-bit machines in combination
with sql server...
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ONpBx6HMEHA.268@.TK2MSFTNGP11.phx.gbl...
> > I was wondering what kind of performance increase can I expect if i
change
> > to a 64 bit system running sql server?
> Really depends on what you are doing... not everything is able to take
> advantage of the advanced CPUs...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||Tuna
I have been testing SQL Server performance on 64 bit in our company for over
two months
Look, try to perfom a large transactions especially if you are going to
deal with lots of INSERT's.To make the story shorten,
personally I did not get a feeeling that we should go with it. Almost the
same response time on the client site,
aslo when I backuped a database (15 gb) i was expected at least to speed up
the procsess on 64-bit but it was really the time. So as Aaaron says
:"Really depends on what you are doing... not everything is able to take
advantage of the advanced CPUs..."
"Tuna" <hier@.onetwotres.123> wrote in message
news:#bEiFJIMEHA.2500@.TK2MSFTNGP12.phx.gbl...
> Which feautures of sql server 2000 are not capable of taking advantage of
a
> 64 bit system?
> At this moment 2 dual 3.2 Ghz (1.5 ram) xeons are handeling 800 kbits per
> seconds of inserts (replication) and roundabout 2000 webusers querying
both
> databases.. We expect a groth of 50% on data inserts and also (at least)
50%
> growth of webusers the next 4 months.
> We have to make a decision about 'the new solution':
> 1. buy 2 big machines (32 or 64 bit), one for redundancy
> 2. buy more 'less expensive' machines and put loadbalancers in front of
them
> The easiest way is buying the big machines... If we go for that it's
> important to know the pro's and con's about 64-bit machines in combination
> with sql server...
>
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:ONpBx6HMEHA.268@.TK2MSFTNGP11.phx.gbl...
> > > I was wondering what kind of performance increase can I expect if i
> change
> > > to a 64 bit system running sql server?
> >
> > Really depends on what you are doing... not everything is able to take
> > advantage of the advanced CPUs...
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
>|||"Tuna" <hier@.onetwotres.123> wrote in message
news:#bEiFJIMEHA.2500@.TK2MSFTNGP12.phx.gbl...
> Which feautures of sql server 2000 are not capable of taking advantage of
a
> 64 bit system?
> At this moment 2 dual 3.2 Ghz (1.5 ram) xeons are handeling 800 kbits per
> seconds of inserts (replication) and roundabout 2000 webusers querying
both
> databases.. We expect a groth of 50% on data inserts and also (at least)
50%
> growth of webusers the next 4 months.
> We have to make a decision about 'the new solution':
> 1. buy 2 big machines (32 or 64 bit), one for redundancy
> 2. buy more 'less expensive' machines and put loadbalancers in front of
them
> The easiest way is buying the big machines... If we go for that it's
> important to know the pro's and con's about 64-bit machines in combination
> with sql server...
you've made a few comments about what's going though the pipe but you
haven't said if there's a problem with it and, assuming there is a problem,
what you have found about where the problem may lie
the short story is there is not enough information here to consider the need
for 64 bit ... fwiw, my nose tells me that you probably do not need it ...
read the TPC benchmarks, flesh out the analysis; it's a completely empirical
problem|||"Tuna" <hier@.onetwotres.123> wrote in message
news:OTNlD0HMEHA.3332@.TK2MSFTNGP10.phx.gbl...
> I was wondering what kind of performance increase can I expect if i change
> to a 64 bit system running sql server?
The main difference with 64bit is that SQL Server can directly access more
than 4GB of RAM.
So if you need more than 4GB of RAM then go for 64bit, otherwise consider
carefully where you should go for 64bit processors or would improving some
other aspect of your server spec be of more benefit, e.g. faster disks

64-bit

Hello,
I was wondering what kind of performance increase can I expect if i change
to a 64 bit system running sql server? What kind of problems can i run into
changing to sql 64 bit or only advantages... Are there benchmark tables
available on the internet?
thanks,
Tune-a> I was wondering what kind of performance increase can I expect if i change
> to a 64 bit system running sql server?
Really depends on what you are doing... not everything is able to take
advantage of the advanced CPUs...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Which feautures of sql server 2000 are not capable of taking advantage of a
64 bit system?
At this moment 2 dual 3.2 Ghz (1.5 ram) xeons are handeling 800 kbits per
seconds of inserts (replication) and roundabout 2000 webusers querying both
databases.. We expect a groth of 50% on data inserts and also (at least) 50%
growth of webusers the next 4 months.
We have to make a decision about 'the new solution':
1. buy 2 big machines (32 or 64 bit), one for redundancy
2. buy more 'less expensive' machines and put loadbalancers in front of them
The easiest way is buying the big machines... If we go for that it's
important to know the pro's and con's about 64-bit machines in combination
with sql server...
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:ONpBx6HMEHA.268@.TK2MSFTNGP11.phx.gbl...
change[vbcol=seagreen]
> Really depends on what you are doing... not everything is able to take
> advantage of the advanced CPUs...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||Tuna
I have been testing SQL Server performance on 64 bit in our company for over
two months
Look, try to perfom a large transactions especially if you are going to
deal with lots of INSERT's.To make the story shorten,
personally I did not get a feeeling that we should go with it. Almost the
same response time on the client site,
aslo when I backuped a database (15 gb) i was expected at least to speed up
the procsess on 64-bit but it was really the time. So as Aaaron says
:"Really depends on what you are doing... not everything is able to take
advantage of the advanced CPUs..."
"Tuna" <hier@.onetwotres.123> wrote in message
news:#bEiFJIMEHA.2500@.TK2MSFTNGP12.phx.gbl...
> Which feautures of sql server 2000 are not capable of taking advantage of
a
> 64 bit system?
> At this moment 2 dual 3.2 Ghz (1.5 ram) xeons are handeling 800 kbits per
> seconds of inserts (replication) and roundabout 2000 webusers querying
both
> databases.. We expect a groth of 50% on data inserts and also (at least)
50%
> growth of webusers the next 4 months.
> We have to make a decision about 'the new solution':
> 1. buy 2 big machines (32 or 64 bit), one for redundancy
> 2. buy more 'less expensive' machines and put loadbalancers in front of
them
> The easiest way is buying the big machines... If we go for that it's
> important to know the pro's and con's about 64-bit machines in combination
> with sql server...
>
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:ONpBx6HMEHA.268@.TK2MSFTNGP11.phx.gbl...
> change
>|||"Tuna" <hier@.onetwotres.123> wrote in message
news:#bEiFJIMEHA.2500@.TK2MSFTNGP12.phx.gbl...

> Which feautures of sql server 2000 are not capable of taking advantage of
a
> 64 bit system?
> At this moment 2 dual 3.2 Ghz (1.5 ram) xeons are handeling 800 kbits per
> seconds of inserts (replication) and roundabout 2000 webusers querying
both
> databases.. We expect a groth of 50% on data inserts and also (at least)
50%
> growth of webusers the next 4 months.
> We have to make a decision about 'the new solution':
> 1. buy 2 big machines (32 or 64 bit), one for redundancy
> 2. buy more 'less expensive' machines and put loadbalancers in front of
them
> The easiest way is buying the big machines... If we go for that it's
> important to know the pro's and con's about 64-bit machines in combination
> with sql server...
you've made a few comments about what's going though the pipe but you
haven't said if there's a problem with it and, assuming there is a problem,
what you have found about where the problem may lie
the short story is there is not enough information here to consider the need
for 64 bit ... fwiw, my nose tells me that you probably do not need it ...
read the TPC benchmarks, flesh out the analysis; it's a completely empirical
problem|||"Tuna" <hier@.onetwotres.123> wrote in message
news:OTNlD0HMEHA.3332@.TK2MSFTNGP10.phx.gbl...
> I was wondering what kind of performance increase can I expect if i change
> to a 64 bit system running sql server?
The main difference with 64bit is that SQL Server can directly access more
than 4GB of RAM.
So if you need more than 4GB of RAM then go for 64bit, otherwise consider
carefully where you should go for 64bit processors or would improving some
other aspect of your server spec be of more benefit, e.g. faster disks

64 bit

Hello,
I was wondering what kind of performance increase can I expect if i change
to a 64 bit system running sql server? What kind of problems can i run into
changing to sql 64 bit or only advantages... Are there benchmark tables
available on the internet?
thanks,
Tune-a
That's an interesting question. The advantages and disadvantages exist only
in the light of the context of use.
Since the release of SQL Server 7.0 in 1998, SQL Server has been on the
enterprise fast track, easily clearing the hurdles that hindered the
adoption of earlier SQL Server versions in the enterprise. Support for a
new scale-out technology called distributed partitioned views boosted SQL
Server to the top of the TPC-C rankings for clustered database systems.
(TPC-C is a standard benchmark for database systems, designed by the
Transaction Performance Processing Councilor TPCwhich consists of all the
major database vendors. The TPC-C test measures transactions per minuteor
tpmC.). The new SQL Server 2000 64-bit Enterprise Edition (formerly
code-named Liberty) moves SQL Server even closer to the peaks of enterprise
scalability.
Obviously, the primary requirements for running the 64-bit edition of SQL
Server are 64-bit hardware and a 64-bit OS. For the OS, SQL Server 2000
64-bit Enterprise Edition requires the Windows Server 2003 64-bit Edition,
which runs only on systems built on the new 64-bit Intel Itanium CPU.
Start your reading with this article on MSDN:
Microsoft SQL Server 2000 (64-bit): Intel Itanium Processor Touchstone
http://msdn.microsoft.com/library/de...us/dnsecure/ht
ml/intelitanium.asp?frame=true
Also, visit this external article
http://www.winnetmag.com/Articles/Ar...79/pg/2/2.html
and view Figure 1 which shows performance of SQL 64 bit on different
hardware platforms.
Hope this helps.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

64 bit

Hello,
I was wondering what kind of performance increase can I expect if i change
to a 64 bit system running sql server? What kind of problems can i run into
changing to sql 64 bit or only advantages... Are there benchmark tables
available on the internet?
thanks,
Tune-aThat's an interesting question. The advantages and disadvantages exist only
in the light of the context of use.
Since the release of SQL Server 7.0 in 1998, SQL Server has been on the
enterprise fast track, easily clearing the hurdles that hindered the
adoption of earlier SQL Server versions in the enterprise. Support for a
new scale-out technology called distributed partitioned views boosted SQL
Server to the top of the TPC-C rankings for clustered database systems.
(TPC-C is a standard benchmark for database systems, designed by the
Transaction Performance Processing Councilor TPCwhich consists of all the
major database vendors. The TPC-C test measures transactions per minuteor
tpmC.). The new SQL Server 2000 64-bit Enterprise Edition (formerly
code-named Liberty) moves SQL Server even closer to the peaks of enterprise
scalability.
Obviously, the primary requirements for running the 64-bit edition of SQL
Server are 64-bit hardware and a 64-bit OS. For the OS, SQL Server 2000
64-bit Enterprise Edition requires the Windows Server 2003 64-bit Edition,
which runs only on systems built on the new 64-bit Intel Itanium CPU.
Start your reading with this article on MSDN:
Microsoft SQL Server 2000 (64-bit): Intel Itanium Processor Touchstone
http://msdn.microsoft.com/library/d...-us/dnsecure/ht
ml/intelitanium.asp?frame=true
Also, visit this external article
http://www.winnetmag.com/Articles/A...779/pg/2/2.html
and view Figure 1 which shows performance of SQL 64 bit on different
hardware platforms.
Hope this helps.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

5 Database Queries to go with an online purchasing system :-)

Hi Guys,

I'm having trouble thinking of ideas for queries that would go with an online purchasing system. The queries must be partially complex (i.e. not just a simple insert query :p).

I can easily write the queries myself but i just need a little help thinking of useful queries.

A query for an 'Invoice' for example is a perfect idea as it implements table joins etc.

Possible other queries are:
Find all customers that have spent a total of more than X amount
Find all delivery address's that X customer has sent items to
Track an order to see if items have been sent yet or not.
etc...

If anyone else can think of a few other ideas i'd be very grateful :-)

thx for reading :-)

--Philkillsonline purchasing sytem? or homework assignment? ;)

total sales by category for the previous month
average price of backlogged orders (product not in stock)
most popular item (by quantity sold, not total price)|||easiest technique...
open a copy of Access, create yuour tables
create the realtionships
open up the query designer
add the required constraints...
run the query, prove it works
open up the query in SQL mode, copy and paste the SQL to your homework assignment paper
...jobsagoodun

... requires minimum thought and creativity, and absolutely no proof that you have understood the concepts behid joins, where clauses etc...|||online purchasing sytem? or homework assignment? ;)

total sales by category for the previous month
average price of backlogged orders (product not in stock)
most popular item (by quantity sold, not total price)

Some nice ideas there thx ^^

easiest technique...
open a copy of Access, create yuour tables
create the realtionships
open up the query designer
add the required constraints...
run the query, prove it works
open up the query in SQL mode, copy and paste the SQL to your homework assignment paper
...jobsagoodun

... requires minimum thought and creativity, and absolutely no proof that you have understood the concepts behid joins, where clauses etc...

and lol...

a nice way to cheat i suppose... but i do actually understand joins etc ;p|||total sales by category for the previous month
average price of backlogged orders (product not in stock)
most popular item (by quantity sold, not total price)
Variations on these:
- Total sales for all foregoing months
- Top ten most popular items
- Most popular category per client (useful for better direct advertising ;-)
- Top ten clients in terms of the amount spent
- Same question, but now in 12 columns, for each of the last 12 months|||- Customer's year to date sales (purchases) compared with last year's year to date sales (purchases).
:shocked: