sql2k sp3
Ive got a little bit of a Replication background but never
with "immediate updating with queued updating for
failover" like Im testing now. In fact Ive never even done
just "immediate updating". I seen lots of horror stories
here about identity columns causing replication problems
for people and was expecting to get them during my testing
this week but I havent. Im curious as to why and thought
I'd ask. Heres what I've done in testing:
1; Made one big Publication of all my tables.
2; Did a Backup/ Restore to the Subscriber.
3; Took the actions as outlined in KB 320499.
4; Took the actions as outlined in KB 320773.
Everything is up and running at this point. Replication
runs fine in both directions. Identity columns on both the
Pub and Sub are in place. I am NOT using the "Yes(Not for
Replication)" option on either box nor have I modified the
ranges on either box. This is why I thought I'd have
problems. I thought Id need to place different ranges on
them and use the "Not for Replication" option on them. But
I didn't and am having no problems. Why? Not that Im
complaining. I even did a failover test by turinng off the
Publisher and switching to Queued Updating. I did inserts
while it was in that mode and still had no problems.
Again Im not upset by my success. But just dont get why
others have the problems Ive read about and I dont? There
is something about my settings that is correct I am
curious to find out what it is.
TIA, ChrisR
Chris,
the errors people have reported come from a variety of causes. Often it is
incorrect range management - either manually or on behalf of SQL Server.
Sometimes the problems have been in using the standby server when the
internal identity value of a column hasn't been updated. In some cases
upating the identity value is not possible even through DBCC CHECKIDENT.
In your scenario as I understand it, there is no allowance for the publisher
and subscriber being allocated the same identity value. This may not be a
problem for you as yet, but if someone on the subscriber attempts to insert
a record and network connectivity is temporarily down, it'll go into the
queue and when the queue reader starts, there could be conflicts. To avoid
this you can have SQL Server allocate an identity range for you or you can
manually create the range. The latter is quite straightforward if you have
realtively few subscribers. EG if you had one subscriber, the publisher
could have a seed of 1 and increment of 2 (odd nos), while the subscriber
has a seed of 2 and increment of 2 (even nos).
HTH,
Paul Ibison
|||This may not be a
>problem for you as yet, but if someone on the subscriber
attempts to insert
>a record and network connectivity is temporarily down,
it'll go into the
>queue and when the queue reader starts, there could be
conflicts.
This is the scenario I did in testing. Turned off the
Publisher, inserted into the Subscriber. When the Pub was
back up there was no issues. You are saying there could be
conflict as I beleive you. Do you know what the
circumstances are that would amke this happen?
Thanks
>--Original Message--
>Chris,
>the errors people have reported come from a variety of
causes. Often it is
>incorrect range management - either manually or on behalf
of SQL Server.
>Sometimes the problems have been in using the standby
server when the
>internal identity value of a column hasn't been updated.
In some cases
>upating the identity value is not possible even through
DBCC CHECKIDENT.
>In your scenario as I understand it, there is no
allowance for the publisher
>and subscriber being allocated the same identity value.
This may not be a
>problem for you as yet, but if someone on the subscriber
attempts to insert
>a record and network connectivity is temporarily down,
it'll go into the
>queue and when the queue reader starts, there could be
conflicts. To avoid
>this you can have SQL Server allocate an identity range
for you or you can
>manually create the range. The latter is quite
straightforward if you have
>realtively few subscribers. EG if you had one subscriber,
the publisher
>could have a seed of 1 and increment of 2 (odd nos),
while the subscriber
>has a seed of 2 and increment of 2 (even nos).
>HTH,
>Paul Ibison
>
>.
>
|||Chris,
to test this you can force failover, or more easily set up an alternative
test system with just a queue. Stop the queue reader agent and distribution
agent. Insert a record into the publisher and subscriber, and the 2 new
records will have the same identity value.
When starting the queue reader there will be a conflict registered which is
viewable i the conflict viewer.
This is not an error like in some of the other posts, but it is a problem of
lost data whch can be avoided by partitioning the identity range.
HTH,
Paul Ibison
|||With immediate updating you are guananteed not to have identity range
problems.
The reason is that any update that happens on the subscriber is first
applied on the publisher where the publisher's identity range rules.
The problem of course is your publisher/subscriber must be well connected
and the publisher must always be online. If so, updates on your subscriber
are rolled back. If the link between the publisher and subscribers goes
down, updates can still occur on the publisher.
With queued, when your publisher is offline, all updates happen on the
subcsriber, so again, no identity problems as no updates happen on the
publisher.
As queued is an asynchronous process when the publisher comes back on line
unless you revert back to immediate you can have identity range problems
unless you are using automatic identity range management.
Automatic Identity Range Management is basically trouble free. You run into
problems with it when you have a range of lets say 100, and a batch update
that updates 1000 rows (or really anything over the 100 range). The procs
which do the automatic range management don't have time to work during the
batch and you get the problem.
So pick a range which is large. Many dba's pick very large ranges which they
know will not be blown for the lifetime of their replication solution. This
option is called set it and forget it. It works very well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:2ef4e01c46b7b$643c0120$a301280a@.phx.gbl...
> sql2k sp3
> Ive got a little bit of a Replication background but never
> with "immediate updating with queued updating for
> failover" like Im testing now. In fact Ive never even done
> just "immediate updating". I seen lots of horror stories
> here about identity columns causing replication problems
> for people and was expecting to get them during my testing
> this week but I havent. Im curious as to why and thought
> I'd ask. Heres what I've done in testing:
> 1; Made one big Publication of all my tables.
> 2; Did a Backup/ Restore to the Subscriber.
> 3; Took the actions as outlined in KB 320499.
> 4; Took the actions as outlined in KB 320773.
> Everything is up and running at this point. Replication
> runs fine in both directions. Identity columns on both the
> Pub and Sub are in place. I am NOT using the "Yes(Not for
> Replication)" option on either box nor have I modified the
> ranges on either box. This is why I thought I'd have
> problems. I thought Id need to place different ranges on
> them and use the "Not for Replication" option on them. But
> I didn't and am having no problems. Why? Not that Im
> complaining. I even did a failover test by turinng off the
> Publisher and switching to Queued Updating. I did inserts
> while it was in that mode and still had no problems.
> Again Im not upset by my success. But just dont get why
> others have the problems Ive read about and I dont? There
> is something about my settings that is correct I am
> curious to find out what it is.
> TIA, ChrisR
|||Thanks Hillary and Paul. I just realized from reading your responses that Im
not totally positive if this box will be used just for fail over if the
Publisher goes down or not. Im not sure, but I dont think the two of them
will ever be used at the same time and the Subscriber will be written to
only if the Pub is off line. If this is the case, I don't think I will have
the identity range problem will I? Come to think of it, should I switch the
whole plan over just to Queued Updating if this is the case? Would I benifit
from that in any way?
Thanks alot you guys for your help.
ChrisR
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#4F46$$aEHA.3420@.TK2MSFTNGP12.phx.gbl...
> With immediate updating you are guananteed not to have identity range
> problems.
> The reason is that any update that happens on the subscriber is first
> applied on the publisher where the publisher's identity range rules.
> The problem of course is your publisher/subscriber must be well connected
> and the publisher must always be online. If so, updates on your subscriber
> are rolled back. If the link between the publisher and subscribers goes
> down, updates can still occur on the publisher.
> With queued, when your publisher is offline, all updates happen on the
> subcsriber, so again, no identity problems as no updates happen on the
> publisher.
> As queued is an asynchronous process when the publisher comes back on line
> unless you revert back to immediate you can have identity range problems
> unless you are using automatic identity range management.
> Automatic Identity Range Management is basically trouble free. You run
into
> problems with it when you have a range of lets say 100, and a batch update
> that updates 1000 rows (or really anything over the 100 range). The procs
> which do the automatic range management don't have time to work during the
> batch and you get the problem.
> So pick a range which is large. Many dba's pick very large ranges which
they
> know will not be blown for the lifetime of their replication solution.
This
> option is called set it and forget it. It works very well.
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:2ef4e01c46b7b$643c0120$a301280a@.phx.gbl...
>
|||queued and bi-directional transactional replication are options. If you
expect schema changes I would use queued as opposed to bi-directional
transactional,
Queued will add a guid column to all tables you are replicating however.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:OQ4yT8BbEHA.3524@.TK2MSFTNGP12.phx.gbl...
> Thanks Hillary and Paul. I just realized from reading your responses that
Im
> not totally positive if this box will be used just for fail over if the
> Publisher goes down or not. Im not sure, but I dont think the two of them
> will ever be used at the same time and the Subscriber will be written to
> only if the Pub is off line. If this is the case, I don't think I will
have
> the identity range problem will I? Come to think of it, should I switch
the
> whole plan over just to Queued Updating if this is the case? Would I
benifit[vbcol=seagreen]
> from that in any way?
> Thanks alot you guys for your help.
> ChrisR
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:#4F46$$aEHA.3420@.TK2MSFTNGP12.phx.gbl...
connected[vbcol=seagreen]
subscriber[vbcol=seagreen]
line[vbcol=seagreen]
> into
update[vbcol=seagreen]
procs[vbcol=seagreen]
the
> they
> This
>
|||Ive done a bit of schema changes on replicated tables in the past. How does
queued benifit the cause?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u3h6VFHbEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> queued and bi-directional transactional replication are options. If you
> expect schema changes I would use queued as opposed to bi-directional
> transactional,
> Queued will add a guid column to all tables you are replicating however.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OQ4yT8BbEHA.3524@.TK2MSFTNGP12.phx.gbl...
that[vbcol=seagreen]
> Im
them[vbcol=seagreen]
> have
> the
> benifit
> connected
> subscriber
goes[vbcol=seagreen]
> line
problems[vbcol=seagreen]
> update
> procs
> the
which
>
|||with bi-directional transactional replication you have to drop both
publications, make changes on both sides and rebuild. You can't use
sp_repladdcolumn or sp_repldropcolumn when you are doing bi-directional
transactional replication.
You can use these stored procedures when you are using transactional
replication with queued updating subscribers.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:O$rxpQJbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Ive done a bit of schema changes on replicated tables in the past. How
does[vbcol=seagreen]
> queued benifit the cause?
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u3h6VFHbEHA.1656@.TK2MSFTNGP09.phx.gbl...
> that
the[vbcol=seagreen]
> them
to[vbcol=seagreen]
switch[vbcol=seagreen]
range[vbcol=seagreen]
first[vbcol=seagreen]
> goes
the[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
> problems
run[vbcol=seagreen]
during[vbcol=seagreen]
> which
solution.
>
|||Thanks Hilary. Ive used sp_repladdcolumn in the past in transactional repl
but it wasnt bi-directional. I assumed I could use it now as well. Good to
know. I'll find out all the requirements this weeks and will now be more
informed on which road to take. Thanks again.
CR
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:e$1Ix$LbEHA.2216@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> with bi-directional transactional replication you have to drop both
> publications, make changes on both sides and rebuild. You can't use
> sp_repladdcolumn or sp_repldropcolumn when you are doing bi-directional
> transactional replication.
> You can use these stored procedures when you are using transactional
> replication with queued updating subscribers.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:O$rxpQJbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> does
you[vbcol=seagreen]
however.[vbcol=seagreen]
> the
written[vbcol=seagreen]
> to
will[vbcol=seagreen]
> switch
> range
> first
rules.[vbcol=seagreen]
> the
> the
> on
> run
batch[vbcol=seagreen]
The
> during
> solution.
>
Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
A couple LDF questions.
I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the databa
se. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emp
tied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do
log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions.
It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup ye
t.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the databa
se. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emp
tied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do
log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions.
It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup ye
t.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.
A couple LDF questions.
I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegr oups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.
A couple LDF questions.
I do a bit a sql programming but do not handle the admin side. Our
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.
server backups are becoming huge so I looked around to see why. A
couple of our databases (small... ~60Megs) have ldf files that are
almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
shows that there are almost 200 virtual logs in each ldf file, all
with active transactions. One of these databases is still attached but
hasn't been used for over a year. How can I determine more information
about these transactions, or force old ones to close? Our datacenter
handles the actual scheduled backups so I don't really want to mess
with too much but i'm hoping there's a way for me to shrink the log
size to something more 'appropriate' without losing any necessary
information. Is this something that they (datacenter) should handle
(they don't manage our databases, just the backups) or is this likely
to be a software issue not closing the transactions so they persist?
Any thoughts or comments greatly appreciated.
Thanks.
Mark<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
>
My guess is that they are NOT handling backups correctly. Sounds like they
may not be doing a transaction log backup.
Try DBCC opentran on each database. This should return the SPIDs of any
open transactions.
dbcc inputbuffer(spid)
and
sp_who2 spid
to find out what they are doing.
You might be able to kill the spids.
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||As Greg mentioned, you have probably missed doing log backups for the database. If the db is in full
recovery mode, and you don't do log backups, then the log file is never "emptied". Or, in other
words, the virtual log file will have a status of 2. If you don't want to do log backups, you should
set the database to simple recovery mode.
I also want to point out that status 2 is not the same as open transactions. It just mean that the
virtual log file cannot be re-used, because you haven't done a log backup yet.
Some more info at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<mark_s_nospam@.entouch.net> wrote in message
news:1176998140.170272.148150@.y80g2000hsf.googlegroups.com...
>I do a bit a sql programming but do not handle the admin side. Our
> server backups are becoming huge so I looked around to see why. A
> couple of our databases (small... ~60Megs) have ldf files that are
> almost 1 gig. I did some reading and learned about 'dbcc loginfo'. It
> shows that there are almost 200 virtual logs in each ldf file, all
> with active transactions. One of these databases is still attached but
> hasn't been used for over a year. How can I determine more information
> about these transactions, or force old ones to close? Our datacenter
> handles the actual scheduled backups so I don't really want to mess
> with too much but i'm hoping there's a way for me to shrink the log
> size to something more 'appropriate' without losing any necessary
> information. Is this something that they (datacenter) should handle
> (they don't manage our databases, just the backups) or is this likely
> to be a software issue not closing the transactions so they persist?
> Any thoughts or comments greatly appreciated.
> Thanks.
> Mark
>|||many thanks to both. that helps. i'll try the code to learn more about
the spid's and will call the datacenter to see what the backup
settings are. thanks again.
Tuesday, March 20, 2012
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.
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 bit confused with merge replication?
hi all;
when i first read about merge replication , i used to think that:
when synchronized,subscriber receives changes at publisher and publisher
receives changes at subscriber and therefore both would always have same
data.
but when i configure a merge replication with pull subscription (in fact,
anonymous ) and do some tests , i see that changes in the subscriber are
propogated to publisher and deleted from subscriber. subscriber always has
the same the data when the initial snapshot was applied.
Bol says that i would mark subscription for reinitialization to get new
schema and data at the next synchronization. But reinitialization is done
before synchronization therefore changes at the subscriber will be
overwritten.
and i am using dynamic snapshot also.
What i want to do is to make subscriber and publisher have same data after
a synchronization.
Do i have to mark subscription for reinitialization after every
synchronization ? but then how can i preserve last changes at the
subscriber?
and dynamic snapshot job is now running on schedule.
is there way to make this job run after every synchronization so that
latest data and schema is always available.
thanks in advance...
Basically what happens is changes at the subscriber are merge with changes
at the publisher. If the changes are to the same row, it is termed a
conflict, and by default the publisher's changes will replace the changes on
the subscriber. If there are no conflicts, changes which occur on the
publisher are applied on the subscriber, and vice versa.
Use the conflict viewer to view any conflicts.
Only reinitialize if you want to propagate changes. You do not have to
reinitialize your subscription to get your publisher and subscriber to have
the same data.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"uykusuz" <ad> wrote in message
news:O3K86PBWFHA.2984@.tk2msftngp13.phx.gbl...
> hi all;
> when i first read about merge replication , i used to think that:
> when synchronized,subscriber receives changes at publisher and publisher
> receives changes at subscriber and therefore both would always have same
> data.
> but when i configure a merge replication with pull subscription (in fact,
> anonymous ) and do some tests , i see that changes in the subscriber are
> propogated to publisher and deleted from subscriber. subscriber always has
> the same the data when the initial snapshot was applied.
>
> Bol says that i would mark subscription for reinitialization to get new
> schema and data at the next synchronization. But reinitialization is
done
> before synchronization therefore changes at the subscriber will be
> overwritten.
> and i am using dynamic snapshot also.
> What i want to do is to make subscriber and publisher have same data
after
> a synchronization.
> Do i have to mark subscription for reinitialization after every
> synchronization ? but then how can i preserve last changes at the
> subscriber?
> and dynamic snapshot job is now running on schedule.
> is there way to make this job run after every synchronization so that
> latest data and schema is always available.
> thanks in advance...
>
>
|||Hilary, thanks for your answer,
but in my case, changes at the publisher are not applied to subscriber.
i insert one record to the 'TABLE_1 at the publisher and one at the
subscriber,
i get the following merge_status messages: (i use sqlMerge object at the
subscriber.)
-Uploading data changes to the Publisher
-Processing article 'TABLE_1'
-Uploaded 1 data changes (1 inserts, 0 updates, 0 deletes, 0 conflicts)
-Downloading data changes to the Subscriber
-Processing article 'TABLE_1'
-Downloaded 1 data changes (0 inserts, 0 updates, 1 deletes, 0 conflicts)
-Merged 2 data changes (1 inserts, 0 updates, 1 deletes, 0 resolved
conflicts).
Changes at the Subscriber are deleted automatically , therefore subscriber
always has the data when the last snapshot was applied..
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OLlM4hCWFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Basically what happens is changes at the subscriber are merge with
changes
> at the publisher. If the changes are to the same row, it is termed a
> conflict, and by default the publisher's changes will replace the changes
on
> the subscriber. If there are no conflicts, changes which occur on the
> publisher are applied on the subscriber, and vice versa.
> Use the conflict viewer to view any conflicts.
> Only reinitialize if you want to propagate changes. You do not have to
> reinitialize your subscription to get your publisher and subscriber to
have[vbcol=seagreen]
> the same data.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "uykusuz" <ad> wrote in message
> news:O3K86PBWFHA.2984@.tk2msftngp13.phx.gbl...
publisher[vbcol=seagreen]
[vbcol=seagreen]
fact,[vbcol=seagreen]
are[vbcol=seagreen]
has
> done
> after
>
|||I found this on Paul Ibison's site:
How to...... avoid subscriber deletes? - when I use filtered articles and
add a record on a subscriber, it is later deleted?
Filtering works by creating a view on the publisher. Initialisation
doesn't send this view to the subscriber, and it is only checked on the
publisher. So, you can add a record on the subscriber and on synchronisation
this record will be sent as an insert to the publisher. here, the filter is
checked, and a delete is downloaded to the subscriber. If this is not the
way you want it to work you have 2 choices:
(a) modify the insert trigger on the subscriber to ensure it doesn't write
the changed record to MSmerge_contents.
(b) use partitioned views to partition your data based on the original merge
replication filter and only replicate one table.
if we modify the insert trigger on the subscriber to ensure it
doesn't write the changed record to MSmerge_contents ,
then this record would not be send to Publisher , am i wrong or miss
something?
i did not understand b. can someone explain it?
and isn't there a formal way to prevent subscriber deletes?
thnx so much...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OLlM4hCWFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Basically what happens is changes at the subscriber are merge with
changes
> at the publisher. If the changes are to the same row, it is termed a
> conflict, and by default the publisher's changes will replace the changes
on
> the subscriber. If there are no conflicts, changes which occur on the
> publisher are applied on the subscriber, and vice versa.
> Use the conflict viewer to view any conflicts.
> Only reinitialize if you want to propagate changes. You do not have to
> reinitialize your subscription to get your publisher and subscriber to
have[vbcol=seagreen]
> the same data.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "uykusuz" <ad> wrote in message
> news:O3K86PBWFHA.2984@.tk2msftngp13.phx.gbl...
publisher[vbcol=seagreen]
fact,[vbcol=seagreen]
are[vbcol=seagreen]
has
> done
> after
>
|||Hi,
the method off my site is an unsupported hack to conveniently avoid the
proper working of merge filters. If you want a more 'standard' way of doing
things, you could have a view with an 'instead of' trigger. Inserts that
violate the filter go to a separate table with an identical structure. The
view amalgamates the records. This way, synchronization proceeds as per
usual.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thnks Paul,
i have to do some research on these issues , i would probably be here soon
then.
bye...
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oji8AKKWFHA.2424@.TK2MSFTNGP10.phx.gbl...
> Hi,
> the method off my site is an unsupported hack to conveniently avoid the
> proper working of merge filters. If you want a more 'standard' way of
doing
> things, you could have a view with an 'instead of' trigger. Inserts that
> violate the filter go to a separate table with an identical structure. The
> view amalgamates the records. This way, synchronization proceeds as per
> usual.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
when i first read about merge replication , i used to think that:
when synchronized,subscriber receives changes at publisher and publisher
receives changes at subscriber and therefore both would always have same
data.
but when i configure a merge replication with pull subscription (in fact,
anonymous ) and do some tests , i see that changes in the subscriber are
propogated to publisher and deleted from subscriber. subscriber always has
the same the data when the initial snapshot was applied.
Bol says that i would mark subscription for reinitialization to get new
schema and data at the next synchronization. But reinitialization is done
before synchronization therefore changes at the subscriber will be
overwritten.
and i am using dynamic snapshot also.
What i want to do is to make subscriber and publisher have same data after
a synchronization.
Do i have to mark subscription for reinitialization after every
synchronization ? but then how can i preserve last changes at the
subscriber?
and dynamic snapshot job is now running on schedule.
is there way to make this job run after every synchronization so that
latest data and schema is always available.
thanks in advance...
Basically what happens is changes at the subscriber are merge with changes
at the publisher. If the changes are to the same row, it is termed a
conflict, and by default the publisher's changes will replace the changes on
the subscriber. If there are no conflicts, changes which occur on the
publisher are applied on the subscriber, and vice versa.
Use the conflict viewer to view any conflicts.
Only reinitialize if you want to propagate changes. You do not have to
reinitialize your subscription to get your publisher and subscriber to have
the same data.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"uykusuz" <ad> wrote in message
news:O3K86PBWFHA.2984@.tk2msftngp13.phx.gbl...
> hi all;
> when i first read about merge replication , i used to think that:
> when synchronized,subscriber receives changes at publisher and publisher
> receives changes at subscriber and therefore both would always have same
> data.
> but when i configure a merge replication with pull subscription (in fact,
> anonymous ) and do some tests , i see that changes in the subscriber are
> propogated to publisher and deleted from subscriber. subscriber always has
> the same the data when the initial snapshot was applied.
>
> Bol says that i would mark subscription for reinitialization to get new
> schema and data at the next synchronization. But reinitialization is
done
> before synchronization therefore changes at the subscriber will be
> overwritten.
> and i am using dynamic snapshot also.
> What i want to do is to make subscriber and publisher have same data
after
> a synchronization.
> Do i have to mark subscription for reinitialization after every
> synchronization ? but then how can i preserve last changes at the
> subscriber?
> and dynamic snapshot job is now running on schedule.
> is there way to make this job run after every synchronization so that
> latest data and schema is always available.
> thanks in advance...
>
>
|||Hilary, thanks for your answer,
but in my case, changes at the publisher are not applied to subscriber.
i insert one record to the 'TABLE_1 at the publisher and one at the
subscriber,
i get the following merge_status messages: (i use sqlMerge object at the
subscriber.)
-Uploading data changes to the Publisher
-Processing article 'TABLE_1'
-Uploaded 1 data changes (1 inserts, 0 updates, 0 deletes, 0 conflicts)
-Downloading data changes to the Subscriber
-Processing article 'TABLE_1'
-Downloaded 1 data changes (0 inserts, 0 updates, 1 deletes, 0 conflicts)
-Merged 2 data changes (1 inserts, 0 updates, 1 deletes, 0 resolved
conflicts).
Changes at the Subscriber are deleted automatically , therefore subscriber
always has the data when the last snapshot was applied..
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OLlM4hCWFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Basically what happens is changes at the subscriber are merge with
changes
> at the publisher. If the changes are to the same row, it is termed a
> conflict, and by default the publisher's changes will replace the changes
on
> the subscriber. If there are no conflicts, changes which occur on the
> publisher are applied on the subscriber, and vice versa.
> Use the conflict viewer to view any conflicts.
> Only reinitialize if you want to propagate changes. You do not have to
> reinitialize your subscription to get your publisher and subscriber to
have[vbcol=seagreen]
> the same data.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "uykusuz" <ad> wrote in message
> news:O3K86PBWFHA.2984@.tk2msftngp13.phx.gbl...
publisher[vbcol=seagreen]
[vbcol=seagreen]
fact,[vbcol=seagreen]
are[vbcol=seagreen]
has
> done
> after
>
|||I found this on Paul Ibison's site:
How to...... avoid subscriber deletes? - when I use filtered articles and
add a record on a subscriber, it is later deleted?
Filtering works by creating a view on the publisher. Initialisation
doesn't send this view to the subscriber, and it is only checked on the
publisher. So, you can add a record on the subscriber and on synchronisation
this record will be sent as an insert to the publisher. here, the filter is
checked, and a delete is downloaded to the subscriber. If this is not the
way you want it to work you have 2 choices:
(a) modify the insert trigger on the subscriber to ensure it doesn't write
the changed record to MSmerge_contents.
(b) use partitioned views to partition your data based on the original merge
replication filter and only replicate one table.
if we modify the insert trigger on the subscriber to ensure it
doesn't write the changed record to MSmerge_contents ,
then this record would not be send to Publisher , am i wrong or miss
something?
i did not understand b. can someone explain it?
and isn't there a formal way to prevent subscriber deletes?
thnx so much...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OLlM4hCWFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Basically what happens is changes at the subscriber are merge with
changes
> at the publisher. If the changes are to the same row, it is termed a
> conflict, and by default the publisher's changes will replace the changes
on
> the subscriber. If there are no conflicts, changes which occur on the
> publisher are applied on the subscriber, and vice versa.
> Use the conflict viewer to view any conflicts.
> Only reinitialize if you want to propagate changes. You do not have to
> reinitialize your subscription to get your publisher and subscriber to
have[vbcol=seagreen]
> the same data.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "uykusuz" <ad> wrote in message
> news:O3K86PBWFHA.2984@.tk2msftngp13.phx.gbl...
publisher[vbcol=seagreen]
fact,[vbcol=seagreen]
are[vbcol=seagreen]
has
> done
> after
>
|||Hi,
the method off my site is an unsupported hack to conveniently avoid the
proper working of merge filters. If you want a more 'standard' way of doing
things, you could have a view with an 'instead of' trigger. Inserts that
violate the filter go to a separate table with an identical structure. The
view amalgamates the records. This way, synchronization proceeds as per
usual.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thnks Paul,
i have to do some research on these issues , i would probably be here soon
then.
bye...
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oji8AKKWFHA.2424@.TK2MSFTNGP10.phx.gbl...
> Hi,
> the method off my site is an unsupported hack to conveniently avoid the
> proper working of merge filters. If you want a more 'standard' way of
doing
> things, you could have a view with an 'instead of' trigger. Inserts that
> violate the filter go to a separate table with an identical structure. The
> view amalgamates the records. This way, synchronization proceeds as per
> usual.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Sunday, March 11, 2012
8 hours to add a bit column
I ran the following:
ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0
On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.
Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID
Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?You won't like any of my answers.
Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?
My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.
Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?
-PatP|||If your answers give me any ideas and any closer to figuring these kinds of problems out, I like them. The business that I'm working for is very data driven and we are moving quickly in a database direction and we need this knowledge.
I've been watching CPU and disc levels in perfmon all day. The system has been steadily disc bound.
sp_who 'active' returns 17 rows.
How do I look at the page counters?
A RAM bound system exhibits itself as disc bound, correct? With lots of paging to/from disc.
The data files and temp db are on a SCSI RAID 5. The main log file is on a separate 250 GB IDE disc (it has grown to 150 GB in the past which wouldn't fit on the RAID)
You won't like any of my answers.
Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?
My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.
Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?
-PatP|||if you trace an alter table statement it will show you the issue i think you are hitting.
alter table creates a new table in temp space with the new column, inserts all the data from the old into the new temp, then swaps the names around.
with 80million rows, i'd be willing to bet your are being I/O thottled either creating the temp (which using a low logged select..into..) or the insert, (which is using a fully logged insert into..select).
either way, 8 hours sux but doesnt really suprise me.|||I'v never had to wait 8 hours to add a column to a big table. Besides, I thought that the EM did the swap-trick and the alter table did not.|||i'd agree...8 hours is way out there.
you are correct...EM does the old swap'r'roo trick. that was an assumption on my part - uber apologies - that you were using the Enterprise Mangler.
i would suspect the same thing is happening underneath an alter, though.
(i'm moving into the 'out of my ass' realm so i'm going to qualify that statement as an 'idea'- not something i claim to know.)
the page counters are under the Memory object with some useful other SQL specifc page counters SQLServer:BufferManager.
i wonder if your disk queues are backing up for reads or writes?
perhaps that could help understand if a large read/write is actually occuring underneath your alter?|||hm, here's another though. Perhaps the Analyzer is waiting for the table to be freed from a lock. What does sysprocesses say?|||Just curious as to why you made it NOT NULL?
USE Northwind
GO
CREATE TABLE myTable99 (Col1 int)
GO
INSERT INTO myTable99(Col1) SELECT 1
GO
SELECT * FROM myTable99
ALTER TABLE myTable99 ADD Obscene BIT NOT NULL DEFAULT 0
GO
SELECT * FROM myTable99
GO
ALTER TABLE myTable99 ADD Obscene2 BIT DEFAULT 0
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO
If you needed it null maybe you caould have performed batch updates after the fact, the changed ALTERed the column to make it NOT NULL...
Is it still running?|||OK, here comes Robert with his BCP again...But it's true, no matter how you look at it! Non-logged data load would beat "in-line" DDL+DML (because this is exactly what happens when you add a new NON-NULLable column with default - the only way to add a new non-nullable column) If you added the same column but made it nullable, - you'd be onto something else 7 hours and 59 minutes ago. At this point though you can't even interrupt this operation because ALTER TABLE is a fully logged operation. It means that every 0 that came from your DEFAULT is logged in your transaction log (thus its size is very explainable). If you decide to kill the process you'll be looking at 8+ hours of rollback. If you stop the service you'll be looking at "Recovering database x..." for probably as much.|||Just curious as to why you made it NOT NULL?
That's an application issue, right? We are trying to mark certain records as "obscene", so every record should be obscene or not. There should be no NULL. I didn't realize that this would be a large performance issue. If I knew in advance I could have dealt with NULL values in one way or another.
It finished overnight but as of 12:30 AM last night, it was running for 13 hours and still going.
I've previously added datetime columns to the same table with EM (this was added via QA with a ALTER TABLE statement) and it took less than an hour. I was really surprised that this took so long. Ideally, I know what to look for to remedy such an issue and how to prevent such things from happening.
thanks guys!|||Did you look at the code that em scripted for you?
Just make sure you don't have a table called tmp_yourtable..
WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's good|||WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's goodTricky little devils, ain't they ?
-PatP|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?
They defaulted to NULL. And it was added through EM as opposed to an ALTER TABLE statement.
The exact time on adding the bit column was 17 hours and 23 minutes.
ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0
On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.
Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID
Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?You won't like any of my answers.
Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?
My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.
Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?
-PatP|||If your answers give me any ideas and any closer to figuring these kinds of problems out, I like them. The business that I'm working for is very data driven and we are moving quickly in a database direction and we need this knowledge.
I've been watching CPU and disc levels in perfmon all day. The system has been steadily disc bound.
sp_who 'active' returns 17 rows.
How do I look at the page counters?
A RAM bound system exhibits itself as disc bound, correct? With lots of paging to/from disc.
The data files and temp db are on a SCSI RAID 5. The main log file is on a separate 250 GB IDE disc (it has grown to 150 GB in the past which wouldn't fit on the RAID)
You won't like any of my answers.
Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?
My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.
Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?
-PatP|||if you trace an alter table statement it will show you the issue i think you are hitting.
alter table creates a new table in temp space with the new column, inserts all the data from the old into the new temp, then swaps the names around.
with 80million rows, i'd be willing to bet your are being I/O thottled either creating the temp (which using a low logged select..into..) or the insert, (which is using a fully logged insert into..select).
either way, 8 hours sux but doesnt really suprise me.|||I'v never had to wait 8 hours to add a column to a big table. Besides, I thought that the EM did the swap-trick and the alter table did not.|||i'd agree...8 hours is way out there.
you are correct...EM does the old swap'r'roo trick. that was an assumption on my part - uber apologies - that you were using the Enterprise Mangler.
i would suspect the same thing is happening underneath an alter, though.
(i'm moving into the 'out of my ass' realm so i'm going to qualify that statement as an 'idea'- not something i claim to know.)
the page counters are under the Memory object with some useful other SQL specifc page counters SQLServer:BufferManager.
i wonder if your disk queues are backing up for reads or writes?
perhaps that could help understand if a large read/write is actually occuring underneath your alter?|||hm, here's another though. Perhaps the Analyzer is waiting for the table to be freed from a lock. What does sysprocesses say?|||Just curious as to why you made it NOT NULL?
USE Northwind
GO
CREATE TABLE myTable99 (Col1 int)
GO
INSERT INTO myTable99(Col1) SELECT 1
GO
SELECT * FROM myTable99
ALTER TABLE myTable99 ADD Obscene BIT NOT NULL DEFAULT 0
GO
SELECT * FROM myTable99
GO
ALTER TABLE myTable99 ADD Obscene2 BIT DEFAULT 0
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO
If you needed it null maybe you caould have performed batch updates after the fact, the changed ALTERed the column to make it NOT NULL...
Is it still running?|||OK, here comes Robert with his BCP again...But it's true, no matter how you look at it! Non-logged data load would beat "in-line" DDL+DML (because this is exactly what happens when you add a new NON-NULLable column with default - the only way to add a new non-nullable column) If you added the same column but made it nullable, - you'd be onto something else 7 hours and 59 minutes ago. At this point though you can't even interrupt this operation because ALTER TABLE is a fully logged operation. It means that every 0 that came from your DEFAULT is logged in your transaction log (thus its size is very explainable). If you decide to kill the process you'll be looking at 8+ hours of rollback. If you stop the service you'll be looking at "Recovering database x..." for probably as much.|||Just curious as to why you made it NOT NULL?
That's an application issue, right? We are trying to mark certain records as "obscene", so every record should be obscene or not. There should be no NULL. I didn't realize that this would be a large performance issue. If I knew in advance I could have dealt with NULL values in one way or another.
It finished overnight but as of 12:30 AM last night, it was running for 13 hours and still going.
I've previously added datetime columns to the same table with EM (this was added via QA with a ALTER TABLE statement) and it took less than an hour. I was really surprised that this took so long. Ideally, I know what to look for to remedy such an issue and how to prevent such things from happening.
thanks guys!|||Did you look at the code that em scripted for you?
Just make sure you don't have a table called tmp_yourtable..
WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's good|||WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's goodTricky little devils, ain't they ?
-PatP|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?|||curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?
They defaulted to NULL. And it was added through EM as opposed to an ALTER TABLE statement.
The exact time on adding the bit column was 17 hours and 23 minutes.
Thursday, March 8, 2012
701 error
We are experiencing the same errors and same "lock out" behavior. We have the same version/patch level of SQL installed but are in a 32 bit env. The box has 32GB of memory. Nether the bPool or VAS show any pressure when the "out of system memory error" occurs. Since this error usually indicates a VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no difference. PSS has been working on the case for 3 weeks. They dont seem to be finding any evidince of memory pressure either. When I last spole to the escalation engineer yesterday it seemed that they are going to recommend reducing the number of databases on the server. I asked for clarification as to whether we are hitting a 32 bit barrior, an instance limitation, or both. I am awaiting the answer. How many databases do you have on your server? We had between 1700 and 1900 (the number varies) at times when the error occured. We are now at 1500, and have not had the error in the 2 days since reducing the number of databases...
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
Steve,
Further comment.
I have been having the problem about once a month for several months and
have not had any joy in discovering what to do about it. Other notes on my
case:
- Once this starts, my CPU utilization will go to very close to 100%. If I
log out some heavy users, my CPU will dip to 70% or 80% for about 20
seconds, then climb back to near 100%.
- At this point, spid 1 is the busiest of the spids. (Which makes it seem
that the SQL Server is eating up its resources trying to find resources for
users.)
- Very Strange Discovery. One time I found that one workstation which had
been running a very large transaction, would, if turned off, drop the server
CPU to normal. But if I turned that workstation back on, even though I did
not access the server, the server workload would go back to 100%. It was as
if the SQL Server remembered the workstation and would try to do something
for it. (The next day this workstation did not have such an effect.) As I
said, Very Strange.
We also are on 32-bit using AWE memory, but only 12 GB. (We also only have
about 60 databases on the machine, nothing like your 1500+.) This weekend,
we are upgrading to 64-bit Windows 2003/SQL Server 2005. We will see if
this recurs, or whether it is an AWE flaw.
This thread from a Red Gate user suggests the problem is not free memory,
but free contiguous memory.
[url]http://www.red-gate.com/MessageBoard/viewtopic.php?p=15220&sid=9c1df0b8ddef4fc6745b1ce1 33f1e6b1[/url]
So, if very large queries and cursor usage are responsible for fragmenting
memory (per something I read elsewhere) I don't know what to do except to
schedule periodic restarts of the the SQL Server.
RLF
<steve> wrote in message news:200722311948sgoodwin@.autotask.com...
> We are experiencing the same errors and same "lock out" behavior. We have
> the same version/patch level of SQL installed but are in a 32 bit env. The
> box has 32GB of memory. Nether the bPool or VAS show any pressure when the
> "out of system memory error" occurs. Since this error usually indicates a
> VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no
> difference. PSS has been working on the case for 3 weeks. They dont seem
> to be finding any evidince of memory pressure either. When I last spole to
> the escalation engineer yesterday it seemed that they are going to
> recommend reducing the number of databases on the server. I asked for
> clarification as to whether we are hitting a 32 bit barrior, an instance
> limitation, or both. I am awaiting the answer. How many databases do you
> have on your server? We had between 1700 and 1900 (the number varies) at
> times when the error occured. We are now at 1500, and have not had the
> error in the 2 days since reducing the number of databases...
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
Steve,
Further comment.
I have been having the problem about once a month for several months and
have not had any joy in discovering what to do about it. Other notes on my
case:
- Once this starts, my CPU utilization will go to very close to 100%. If I
log out some heavy users, my CPU will dip to 70% or 80% for about 20
seconds, then climb back to near 100%.
- At this point, spid 1 is the busiest of the spids. (Which makes it seem
that the SQL Server is eating up its resources trying to find resources for
users.)
- Very Strange Discovery. One time I found that one workstation which had
been running a very large transaction, would, if turned off, drop the server
CPU to normal. But if I turned that workstation back on, even though I did
not access the server, the server workload would go back to 100%. It was as
if the SQL Server remembered the workstation and would try to do something
for it. (The next day this workstation did not have such an effect.) As I
said, Very Strange.
We also are on 32-bit using AWE memory, but only 12 GB. (We also only have
about 60 databases on the machine, nothing like your 1500+.) This weekend,
we are upgrading to 64-bit Windows 2003/SQL Server 2005. We will see if
this recurs, or whether it is an AWE flaw.
This thread from a Red Gate user suggests the problem is not free memory,
but free contiguous memory.
[url]http://www.red-gate.com/MessageBoard/viewtopic.php?p=15220&sid=9c1df0b8ddef4fc6745b1ce1 33f1e6b1[/url]
So, if very large queries and cursor usage are responsible for fragmenting
memory (per something I read elsewhere) I don't know what to do except to
schedule periodic restarts of the the SQL Server.
RLF
<steve> wrote in message news:200722311948sgoodwin@.autotask.com...
> We are experiencing the same errors and same "lock out" behavior. We have
> the same version/patch level of SQL installed but are in a 32 bit env. The
> box has 32GB of memory. Nether the bPool or VAS show any pressure when the
> "out of system memory error" occurs. Since this error usually indicates a
> VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no
> difference. PSS has been working on the case for 3 weeks. They dont seem
> to be finding any evidince of memory pressure either. When I last spole to
> the escalation engineer yesterday it seemed that they are going to
> recommend reducing the number of databases on the server. I asked for
> clarification as to whether we are hitting a 32 bit barrior, an instance
> limitation, or both. I am awaiting the answer. How many databases do you
> have on your server? We had between 1700 and 1900 (the number varies) at
> times when the error occured. We are now at 1500, and have not had the
> error in the 2 days since reducing the number of databases...
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
701 error
We are experiencing the same errors and same "lock out" behavior. We have th
e same version/patch level of SQL installed but are in a 32 bit env. The box
has 32GB of memory. Nether the bPool or VAS show any pressure when the "out
of system memory error" oc
curs. Since this error usually indicates a VAS problem we tried increasing V
AS to 1GB w/the -g flag. It made no difference. PSS has been working on the
case for 3 weeks. They dont seem to be finding any evidince of memory pressu
re either. When I last spol
e to the escalation engineer yesterday it seemed that they are going to reco
mmend reducing the number of databases on the server. I asked for clarificat
ion as to whether we are hitting a 32 bit barrior, an instance limitation, o
r both. I am awaiting the a
nswer. How many databases do you have on your server? We had between 1700 an
d 1900 (the number varies) at times when the error occured. We are now at 15
00, and have not had the error in the 2 days since reducing the number of da
tabases...
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.comSteve,
Further comment.
I have been having the problem about once a month for several months and
have not had any joy in discovering what to do about it. Other notes on my
case:
- Once this starts, my CPU utilization will go to very close to 100%. If I
log out some heavy users, my CPU will dip to 70% or 80% for about 20
seconds, then climb back to near 100%.
- At this point, spid 1 is the busiest of the spids. (Which makes it seem
that the SQL Server is eating up its resources trying to find resources for
users.)
- Very Strange Discovery. One time I found that one workstation which had
been running a very large transaction, would, if turned off, drop the server
CPU to normal. But if I turned that workstation back on, even though I did
not access the server, the server workload would go back to 100%. It was as
if the SQL Server remembered the workstation and would try to do something
for it. (The next day this workstation did not have such an effect.) As I
said, Very Strange.
We also are on 32-bit using AWE memory, but only 12 GB. (We also only have
about 60 databases on the machine, nothing like your 1500+.) This weekend,
we are upgrading to 64-bit Windows 2003/SQL Server 2005. We will see if
this recurs, or whether it is an AWE flaw.
This thread from a Red Gate user suggests the problem is not free memory,
but free contiguous memory.
http://www.red-gate.com/MessageBoar...
b1ce133f1e6b1
So, if very large queries and cursor usage are responsible for fragmenting
memory (per something I read elsewhere) I don't know what to do except to
schedule periodic restarts of the the SQL Server.
RLF
<steve> wrote in message news:200722311948sgoodwin@.autotask.com...
> We are experiencing the same errors and same "lock out" behavior. We have
> the same version/patch level of SQL installed but are in a 32 bit env. The
> box has 32GB of memory. Nether the bPool or VAS show any pressure when the
> "out of system memory error" occurs. Since this error usually indicates a
> VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no
> difference. PSS has been working on the case for 3 weeks. They dont seem
> to be finding any evidince of memory pressure either. When I last spole to
> the escalation engineer yesterday it seemed that they are going to
> recommend reducing the number of databases on the server. I asked for
> clarification as to whether we are hitting a 32 bit barrior, an instance
> limitation, or both. I am awaiting the answer. How many databases do you
> have on your server? We had between 1700 and 1900 (the number varies) at
> times when the error occured. We are now at 1500, and have not had the
> error in the 2 days since reducing the number of databases...
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
e same version/patch level of SQL installed but are in a 32 bit env. The box
has 32GB of memory. Nether the bPool or VAS show any pressure when the "out
of system memory error" oc
curs. Since this error usually indicates a VAS problem we tried increasing V
AS to 1GB w/the -g flag. It made no difference. PSS has been working on the
case for 3 weeks. They dont seem to be finding any evidince of memory pressu
re either. When I last spol
e to the escalation engineer yesterday it seemed that they are going to reco
mmend reducing the number of databases on the server. I asked for clarificat
ion as to whether we are hitting a 32 bit barrior, an instance limitation, o
r both. I am awaiting the a
nswer. How many databases do you have on your server? We had between 1700 an
d 1900 (the number varies) at times when the error occured. We are now at 15
00, and have not had the error in the 2 days since reducing the number of da
tabases...
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.comSteve,
Further comment.
I have been having the problem about once a month for several months and
have not had any joy in discovering what to do about it. Other notes on my
case:
- Once this starts, my CPU utilization will go to very close to 100%. If I
log out some heavy users, my CPU will dip to 70% or 80% for about 20
seconds, then climb back to near 100%.
- At this point, spid 1 is the busiest of the spids. (Which makes it seem
that the SQL Server is eating up its resources trying to find resources for
users.)
- Very Strange Discovery. One time I found that one workstation which had
been running a very large transaction, would, if turned off, drop the server
CPU to normal. But if I turned that workstation back on, even though I did
not access the server, the server workload would go back to 100%. It was as
if the SQL Server remembered the workstation and would try to do something
for it. (The next day this workstation did not have such an effect.) As I
said, Very Strange.
We also are on 32-bit using AWE memory, but only 12 GB. (We also only have
about 60 databases on the machine, nothing like your 1500+.) This weekend,
we are upgrading to 64-bit Windows 2003/SQL Server 2005. We will see if
this recurs, or whether it is an AWE flaw.
This thread from a Red Gate user suggests the problem is not free memory,
but free contiguous memory.
http://www.red-gate.com/MessageBoar...
b1ce133f1e6b1
So, if very large queries and cursor usage are responsible for fragmenting
memory (per something I read elsewhere) I don't know what to do except to
schedule periodic restarts of the the SQL Server.
RLF
<steve> wrote in message news:200722311948sgoodwin@.autotask.com...
> We are experiencing the same errors and same "lock out" behavior. We have
> the same version/patch level of SQL installed but are in a 32 bit env. The
> box has 32GB of memory. Nether the bPool or VAS show any pressure when the
> "out of system memory error" occurs. Since this error usually indicates a
> VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no
> difference. PSS has been working on the case for 3 weeks. They dont seem
> to be finding any evidince of memory pressure either. When I last spole to
> the escalation engineer yesterday it seemed that they are going to
> recommend reducing the number of databases on the server. I asked for
> clarification as to whether we are hitting a 32 bit barrior, an instance
> limitation, or both. I am awaiting the answer. How many databases do you
> have on your server? We had between 1700 and 1900 (the number varies) at
> times when the error occured. We are now at 1500, and have not had the
> error in the 2 days since reducing the number of databases...
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
6G Ram but SQL doesn't make use of it by default
If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
I have made a new fresh setup (everything is in 64 bit version) on a brand
new machine with 6Gigs of RAM, still when I display server's properties, in
the memory section, in the textbox under Max Server memory
I have 2147483647, which I think is 2 G
Thanks
You shouldn't have to do any config on a 64 bit SS on a 64 bit machine for using > 2GB memory.
> I have 2147483647, which I think is 2 G
No, that number is in MB:
2147483647 MB
= 2097151 GB
= 2047 TB
= approx 2 PB (it is Peta or Exa?)
No discussion on memory and 64 bit is complete without referring to Slava Oks blogs. In short,
consider using lock pages in memory also on 64 bit (if my memory serves me):
http://blogs.msdn.com/slavao/archive/category/9043.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties, in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks
|||2147483647 is much more than 2GB... 1024 = 1GB
Keith Kratochvil
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties,
> in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks
|||Hi Salam,
As other members have mentioned, the 2147483647 default value is in MB, so
it is a quite large value( you need to reduce it according to your
machine's physical memory).
As for AWE, it is an operating system feature and is only useful when the
virtual address space(of OS process) is less than the available physical
memory. For SQL server 2005 64 bit on windows 2003 64 bit, there is no
such virtual address space limitation(normally virtual address space is
much larger than available physical memory space). also, it is recommended
that we adjust the SQL Server instance's max server memory to a proper
value so that other applications on the server can also get sufficient
physical memory for paging file switch.
#Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Salam,
Have you got any further idea on this issue? Please feel free to post here
if there is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
I have made a new fresh setup (everything is in 64 bit version) on a brand
new machine with 6Gigs of RAM, still when I display server's properties, in
the memory section, in the textbox under Max Server memory
I have 2147483647, which I think is 2 G
Thanks
You shouldn't have to do any config on a 64 bit SS on a 64 bit machine for using > 2GB memory.
> I have 2147483647, which I think is 2 G
No, that number is in MB:
2147483647 MB
= 2097151 GB
= 2047 TB
= approx 2 PB (it is Peta or Exa?)
No discussion on memory and 64 bit is complete without referring to Slava Oks blogs. In short,
consider using lock pages in memory also on 64 bit (if my memory serves me):
http://blogs.msdn.com/slavao/archive/category/9043.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties, in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks
|||2147483647 is much more than 2GB... 1024 = 1GB
Keith Kratochvil
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties,
> in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks
|||Hi Salam,
As other members have mentioned, the 2147483647 default value is in MB, so
it is a quite large value( you need to reduce it according to your
machine's physical memory).
As for AWE, it is an operating system feature and is only useful when the
virtual address space(of OS process) is less than the available physical
memory. For SQL server 2005 64 bit on windows 2003 64 bit, there is no
such virtual address space limitation(normally virtual address space is
much larger than available physical memory space). also, it is recommended
that we adjust the SQL Server instance's max server memory to a proper
value so that other applications on the server can also get sufficient
physical memory for paging file switch.
#Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Salam,
Have you got any further idea on this issue? Please feel free to post here
if there is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
6G Ram but SQL doesn't make use of it by default
If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
I have made a new fresh setup (everything is in 64 bit version) on a brand
new machine with 6Gigs of RAM, still when I display server's properties, in
the memory section, in the textbox under Max Server memory
I have 2147483647, which I think is 2 G
ThanksYou shouldn't have to do any config on a 64 bit SS on a 64 bit machine for u
sing > 2GB memory.
> I have 2147483647, which I think is 2 G
No, that number is in MB:
2147483647 MB
= 2097151 GB
= 2047 TB
= approx 2 PB (it is Peta or Exa?)
No discussion on memory and 64 bit is complete without referring to Slava Ok
s blogs. In short,
consider using lock pages in memory also on 64 bit (if my memory serves me):
http://blogs.msdn.com/slavao/archive/category/9043.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties, i
n
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||2147483647 is much more than 2GB... 1024 = 1GB
Keith Kratochvil
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties,
> in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||Hi Salam,
As other members have mentioned, the 2147483647 default value is in MB, so
it is a quite large value( you need to reduce it according to your
machine's physical memory).
As for AWE, it is an operating system feature and is only useful when the
virtual address space(of OS process) is less than the available physical
memory. For SQL server 2005 64 bit on windows 2003 64 bit, there is no
such virtual address space limitation(normally virtual address space is
much larger than available physical memory space). also, it is recommended
that we adjust the SQL Server instance's max server memory to a proper
value so that other applications on the server can also get sufficient
physical memory for paging file switch.
#Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Salam,
Have you got any further idea on this issue? Please feel free to post here
if there is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
I have made a new fresh setup (everything is in 64 bit version) on a brand
new machine with 6Gigs of RAM, still when I display server's properties, in
the memory section, in the textbox under Max Server memory
I have 2147483647, which I think is 2 G
ThanksYou shouldn't have to do any config on a 64 bit SS on a 64 bit machine for u
sing > 2GB memory.
> I have 2147483647, which I think is 2 G
No, that number is in MB:
2147483647 MB
= 2097151 GB
= 2047 TB
= approx 2 PB (it is Peta or Exa?)
No discussion on memory and 64 bit is complete without referring to Slava Ok
s blogs. In short,
consider using lock pages in memory also on 64 bit (if my memory serves me):
http://blogs.msdn.com/slavao/archive/category/9043.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties, i
n
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||2147483647 is much more than 2GB... 1024 = 1GB
Keith Kratochvil
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties,
> in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||Hi Salam,
As other members have mentioned, the 2147483647 default value is in MB, so
it is a quite large value( you need to reduce it according to your
machine's physical memory).
As for AWE, it is an operating system feature and is only useful when the
virtual address space(of OS process) is less than the available physical
memory. For SQL server 2005 64 bit on windows 2003 64 bit, there is no
such virtual address space limitation(normally virtual address space is
much larger than available physical memory space). also, it is recommended
that we adjust the SQL Server instance's max server memory to a proper
value so that other applications on the server can also get sufficient
physical memory for paging file switch.
#Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Salam,
Have you got any further idea on this issue? Please feel free to post here
if there is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
6G Ram but SQL doesn't make use of it by default
If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
I have made a new fresh setup (everything is in 64 bit version) on a brand
new machine with 6Gigs of RAM, still when I display server's properties, in
the memory section, in the textbox under Max Server memory
I have 2147483647, which I think is 2 G
ThanksYou shouldn't have to do any config on a 64 bit SS on a 64 bit machine for using > 2GB memory.
> I have 2147483647, which I think is 2 G
No, that number is in MB:
2147483647 MB
= 2097151 GB
= 2047 TB
= approx 2 PB (it is Peta or Exa?)
No discussion on memory and 64 bit is complete without referring to Slava Oks blogs. In short,
consider using lock pages in memory also on 64 bit (if my memory serves me):
http://blogs.msdn.com/slavao/archive/category/9043.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties, in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||2147483647 is much more than 2GB... 1024 = 1GB
--
Keith Kratochvil
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties,
> in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||Hi Salam,
As other members have mentioned, the 2147483647 default value is in MB, so
it is a quite large value( you need to reduce it according to your
machine's physical memory).
As for AWE, it is an operating system feature and is only useful when the
virtual address space(of OS process) is less than the available physical
memory. For SQL server 2005 64 bit on windows 2003 64 bit, there is no
such virtual address space limitation(normally virtual address space is
much larger than available physical memory space). also, it is recommended
that we adjust the SQL Server instance's max server memory to a proper
value so that other applications on the server can also get sufficient
physical memory for paging file switch.
#Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Salam,
Have you got any further idea on this issue? Please feel free to post here
if there is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
I have made a new fresh setup (everything is in 64 bit version) on a brand
new machine with 6Gigs of RAM, still when I display server's properties, in
the memory section, in the textbox under Max Server memory
I have 2147483647, which I think is 2 G
ThanksYou shouldn't have to do any config on a 64 bit SS on a 64 bit machine for using > 2GB memory.
> I have 2147483647, which I think is 2 G
No, that number is in MB:
2147483647 MB
= 2097151 GB
= 2047 TB
= approx 2 PB (it is Peta or Exa?)
No discussion on memory and 64 bit is complete without referring to Slava Oks blogs. In short,
consider using lock pages in memory also on 64 bit (if my memory serves me):
http://blogs.msdn.com/slavao/archive/category/9043.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties, in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||2147483647 is much more than 2GB... 1024 = 1GB
--
Keith Kratochvil
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties,
> in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||Hi Salam,
As other members have mentioned, the 2147483647 default value is in MB, so
it is a quite large value( you need to reduce it according to your
machine's physical memory).
As for AWE, it is an operating system feature and is only useful when the
virtual address space(of OS process) is less than the available physical
memory. For SQL server 2005 64 bit on windows 2003 64 bit, there is no
such virtual address space limitation(normally virtual address space is
much larger than available physical memory space). also, it is recommended
that we adjust the SQL Server instance's max server memory to a proper
value so that other applications on the server can also get sufficient
physical memory for paging file switch.
#Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Salam,
Have you got any further idea on this issue? Please feel free to post here
if there is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
Tuesday, March 6, 2012
64-bit SQL Server for Xeon
Hello,
Is there a SQL Server 2000 64-bit Edition for XEON processors?
I saw that there are two different versions of SQL 2005 (64 Bit Extended and
64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
for Itanium processors.
Thank youHi
SQL Server 2000 has only a full 64 bit edition of Itaniums.
SQL Server 2000 with SP4 supports EM64T and x64.
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/
"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> Hello,
> Is there a SQL Server 2000 64-bit Edition for XEON processors?
> I saw that there are two different versions of SQL 2005 (64 Bit Extended
> and
> 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> for Itanium processors.
> Thank you|||So how do I install SQL 64-bit on a Xeon processor based server? Is there a
release "SQL 2000 with SP4"? And if so, is 64-bit an option during
installation?
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server 2000 has only a full 64 bit edition of Itaniums.
> SQL Server 2000 with SP4 supports EM64T and x64.
> 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/
> "Vi" <Vi@.discussions.microsoft.com> wrote in message
> news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> > Hello,
> > Is there a SQL Server 2000 64-bit Edition for XEON processors?
> > I saw that there are two different versions of SQL 2005 (64 Bit Extended
> > and
> > 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> > for Itanium processors.
> >
> > Thank you
>
>|||"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:A329E111-A514-4860-9697-64F947441955@.microsoft.com...
> So how do I install SQL 64-bit on a Xeon processor based server? Is there
> a
> release "SQL 2000 with SP4"? And if so, is 64-bit an option during
> installation?
>
It's 32-bit SQL Server 2000 running in WOW32 mode on 64-bit windows. You
get a 4gig user-mode address space because of WOW64, but it's still a 32-bit
application.
David
Is there a SQL Server 2000 64-bit Edition for XEON processors?
I saw that there are two different versions of SQL 2005 (64 Bit Extended and
64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
for Itanium processors.
Thank youHi
SQL Server 2000 has only a full 64 bit edition of Itaniums.
SQL Server 2000 with SP4 supports EM64T and x64.
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/
"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> Hello,
> Is there a SQL Server 2000 64-bit Edition for XEON processors?
> I saw that there are two different versions of SQL 2005 (64 Bit Extended
> and
> 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> for Itanium processors.
> Thank you|||So how do I install SQL 64-bit on a Xeon processor based server? Is there a
release "SQL 2000 with SP4"? And if so, is 64-bit an option during
installation?
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server 2000 has only a full 64 bit edition of Itaniums.
> SQL Server 2000 with SP4 supports EM64T and x64.
> 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/
> "Vi" <Vi@.discussions.microsoft.com> wrote in message
> news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> > Hello,
> > Is there a SQL Server 2000 64-bit Edition for XEON processors?
> > I saw that there are two different versions of SQL 2005 (64 Bit Extended
> > and
> > 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> > for Itanium processors.
> >
> > Thank you
>
>|||"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:A329E111-A514-4860-9697-64F947441955@.microsoft.com...
> So how do I install SQL 64-bit on a Xeon processor based server? Is there
> a
> release "SQL 2000 with SP4"? And if so, is 64-bit an option during
> installation?
>
It's 32-bit SQL Server 2000 running in WOW32 mode on 64-bit windows. You
get a 4gig user-mode address space because of WOW64, but it's still a 32-bit
application.
David
64-bit SQL Server for Xeon
Hello,
Is there a SQL Server 2000 64-bit Edition for XEON processors?
I saw that there are two different versions of SQL 2005 (64 Bit Extended and
64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
for Itanium processors.
Thank you
Hi
SQL Server 2000 has only a full 64 bit edition of Itaniums.
SQL Server 2000 with SP4 supports EM64T and x64.
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/
"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> Hello,
> Is there a SQL Server 2000 64-bit Edition for XEON processors?
> I saw that there are two different versions of SQL 2005 (64 Bit Extended
> and
> 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> for Itanium processors.
> Thank you
|||So how do I install SQL 64-bit on a Xeon processor based server? Is there a
release "SQL 2000 with SP4"? And if so, is 64-bit an option during
installation?
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server 2000 has only a full 64 bit edition of Itaniums.
> SQL Server 2000 with SP4 supports EM64T and x64.
> 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/
> "Vi" <Vi@.discussions.microsoft.com> wrote in message
> news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
>
>
|||"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:A329E111-A514-4860-9697-64F947441955@.microsoft.com...
> So how do I install SQL 64-bit on a Xeon processor based server? Is there
> a
> release "SQL 2000 with SP4"? And if so, is 64-bit an option during
> installation?
>
It's 32-bit SQL Server 2000 running in WOW32 mode on 64-bit windows. You
get a 4gig user-mode address space because of WOW64, but it's still a 32-bit
application.
David
Is there a SQL Server 2000 64-bit Edition for XEON processors?
I saw that there are two different versions of SQL 2005 (64 Bit Extended and
64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
for Itanium processors.
Thank you
Hi
SQL Server 2000 has only a full 64 bit edition of Itaniums.
SQL Server 2000 with SP4 supports EM64T and x64.
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/
"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> Hello,
> Is there a SQL Server 2000 64-bit Edition for XEON processors?
> I saw that there are two different versions of SQL 2005 (64 Bit Extended
> and
> 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> for Itanium processors.
> Thank you
|||So how do I install SQL 64-bit on a Xeon processor based server? Is there a
release "SQL 2000 with SP4"? And if so, is 64-bit an option during
installation?
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server 2000 has only a full 64 bit edition of Itaniums.
> SQL Server 2000 with SP4 supports EM64T and x64.
> 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/
> "Vi" <Vi@.discussions.microsoft.com> wrote in message
> news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
>
>
|||"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:A329E111-A514-4860-9697-64F947441955@.microsoft.com...
> So how do I install SQL 64-bit on a Xeon processor based server? Is there
> a
> release "SQL 2000 with SP4"? And if so, is 64-bit an option during
> installation?
>
It's 32-bit SQL Server 2000 running in WOW32 mode on 64-bit windows. You
get a 4gig user-mode address space because of WOW64, but it's still a 32-bit
application.
David
64-bit SQL Server for Xeon
Hello,
Is there a SQL Server 2000 64-bit Edition for XEON processors?
I saw that there are two different versions of SQL 2005 (64 Bit Extended and
64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
for Itanium processors.
Thank youHi
SQL Server 2000 has only a full 64 bit edition of Itaniums.
SQL Server 2000 with SP4 supports EM64T and x64.
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/
"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> Hello,
> Is there a SQL Server 2000 64-bit Edition for XEON processors?
> I saw that there are two different versions of SQL 2005 (64 Bit Extended
> and
> 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> for Itanium processors.
> Thank you|||So how do I install SQL 64-bit on a Xeon processor based server? Is there a
release "SQL 2000 with SP4"? And if so, is 64-bit an option during
installation?
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server 2000 has only a full 64 bit edition of Itaniums.
> SQL Server 2000 with SP4 supports EM64T and x64.
> 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/
> "Vi" <Vi@.discussions.microsoft.com> wrote in message
> news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
>
>|||"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:A329E111-A514-4860-9697-64F947441955@.microsoft.com...
> So how do I install SQL 64-bit on a Xeon processor based server? Is there
> a
> release "SQL 2000 with SP4"? And if so, is 64-bit an option during
> installation?
>
It's 32-bit SQL Server 2000 running in WOW32 mode on 64-bit windows. You
get a 4gig user-mode address space because of WOW64, but it's still a 32-bit
application.
David
Is there a SQL Server 2000 64-bit Edition for XEON processors?
I saw that there are two different versions of SQL 2005 (64 Bit Extended and
64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
for Itanium processors.
Thank youHi
SQL Server 2000 has only a full 64 bit edition of Itaniums.
SQL Server 2000 with SP4 supports EM64T and x64.
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/
"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
> Hello,
> Is there a SQL Server 2000 64-bit Edition for XEON processors?
> I saw that there are two different versions of SQL 2005 (64 Bit Extended
> and
> 64 Bit Itanium), but for SQL 2000 I found only one, and it seems that it's
> for Itanium processors.
> Thank you|||So how do I install SQL 64-bit on a Xeon processor based server? Is there a
release "SQL 2000 with SP4"? And if so, is 64-bit an option during
installation?
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL Server 2000 has only a full 64 bit edition of Itaniums.
> SQL Server 2000 with SP4 supports EM64T and x64.
> 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/
> "Vi" <Vi@.discussions.microsoft.com> wrote in message
> news:8B3CC9AB-8025-4CDB-9B1D-4F0AC5DFFB8E@.microsoft.com...
>
>|||"Vi" <Vi@.discussions.microsoft.com> wrote in message
news:A329E111-A514-4860-9697-64F947441955@.microsoft.com...
> So how do I install SQL 64-bit on a Xeon processor based server? Is there
> a
> release "SQL 2000 with SP4"? And if so, is 64-bit an option during
> installation?
>
It's 32-bit SQL Server 2000 running in WOW32 mode on 64-bit windows. You
get a 4gig user-mode address space because of WOW64, but it's still a 32-bit
application.
David
64bit SQL
Hi,
Can anyone confirm that the 64bit edition on SQL 2000 will only run on
Itanium 64 bit machines & not Xeon 64 bit processors?
Is there an Microsoft article on this?
Regards
SteveConfirmed.
(Don't know about MS article, haven't searched KB..)
On EM64T and 64 bit OS, you can run 32 bit SQL Server with sp4 in WOW, which
is supported, but
probably not what you want.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Hi
Yes , 64bit runs on Itanium , but I don't remember the specific article
about that
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Thank you both.
We have a 32 bit application that is supposed be getting installed onto
64bit Server 2003, seeing as that we are still going to be using 32bit
SQL 2000, do you think it would be better for us to use the 32bit
version of Windows 2003?
Regards,
Steve|||IAE64 supports fully 64 bit and partially 32. Before this there was x64
allowing both architectures. As far as I know benefits (64) are visible when
you have more than 4 Gb RAM or 16 processors.
--
current location: alicante (es)
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You should really look into using SQL2005 since you can run the 64 bit
edition of 2005 on Win2003 X64. But if you stay with 2000 then the 32 bit
is the only choice and it will run just fine on X64.
Andrew J. Kelly SQL MVP
"Stevan" <stevanflores@.gmail.com> wrote in message
news:1142415651.698599.131360@.e56g2000cwe.googlegroups.com...
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You can certainly run SQL2000 SP4 on Windows 2003 x64. And there are some
benefits right away. For instance, SQL2000 gets to use 4GB by default.
Whether these benefits will bobble up to your app level is a different story
.
The only way to determine whether using Windows 2003 x64 will provide better
performance for your app is for you to conduct some tests. For the workloads
I have tested, I get better performance on Windows 2003 x64 for some
workloads and no change in performance for the others >with Windows 2003 x86
vs x64 being the only variable<.
Linchi
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||stevanflores@.gmail.com wrote:
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
64-bit IA64 Itanium is your only 64-bit option for SQL Server 2000 and
it must run on equivalent Itanium hardware.
SQL Server 2005 supports three platforms: x86, x64 (AMD64 and Intel
EM64T), and Itanium
You can run SQL Server 2000 SP4 x86 and SQL Server 2005 x86 on Windows
Server 2003 x64 Edition.
David Gugick - SQL Server MVP
Quest Software
Can anyone confirm that the 64bit edition on SQL 2000 will only run on
Itanium 64 bit machines & not Xeon 64 bit processors?
Is there an Microsoft article on this?
Regards
SteveConfirmed.
(Don't know about MS article, haven't searched KB..)
On EM64T and 64 bit OS, you can run 32 bit SQL Server with sp4 in WOW, which
is supported, but
probably not what you want.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Hi
Yes , 64bit runs on Itanium , but I don't remember the specific article
about that
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Thank you both.
We have a 32 bit application that is supposed be getting installed onto
64bit Server 2003, seeing as that we are still going to be using 32bit
SQL 2000, do you think it would be better for us to use the 32bit
version of Windows 2003?
Regards,
Steve|||IAE64 supports fully 64 bit and partially 32. Before this there was x64
allowing both architectures. As far as I know benefits (64) are visible when
you have more than 4 Gb RAM or 16 processors.
--
current location: alicante (es)
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You should really look into using SQL2005 since you can run the 64 bit
edition of 2005 on Win2003 X64. But if you stay with 2000 then the 32 bit
is the only choice and it will run just fine on X64.
Andrew J. Kelly SQL MVP
"Stevan" <stevanflores@.gmail.com> wrote in message
news:1142415651.698599.131360@.e56g2000cwe.googlegroups.com...
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You can certainly run SQL2000 SP4 on Windows 2003 x64. And there are some
benefits right away. For instance, SQL2000 gets to use 4GB by default.
Whether these benefits will bobble up to your app level is a different story
.
The only way to determine whether using Windows 2003 x64 will provide better
performance for your app is for you to conduct some tests. For the workloads
I have tested, I get better performance on Windows 2003 x64 for some
workloads and no change in performance for the others >with Windows 2003 x86
vs x64 being the only variable<.
Linchi
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||stevanflores@.gmail.com wrote:
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
64-bit IA64 Itanium is your only 64-bit option for SQL Server 2000 and
it must run on equivalent Itanium hardware.
SQL Server 2005 supports three platforms: x86, x64 (AMD64 and Intel
EM64T), and Itanium
You can run SQL Server 2000 SP4 x86 and SQL Server 2005 x86 on Windows
Server 2003 x64 Edition.
David Gugick - SQL Server MVP
Quest Software
64bit SQL
Hi,
Can anyone confirm that the 64bit edition on SQL 2000 will only run on
Itanium 64 bit machines & not Xeon 64 bit processors?
Is there an Microsoft article on this?
Regards
SteveConfirmed.
(Don't know about MS article, haven't searched KB..)
On EM64T and 64 bit OS, you can run 32 bit SQL Server with sp4 in WOW, which is supported, but
probably not what you want.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Hi
Yes , 64bit runs on Itanium , but I don't remember the specific article
about that
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Thank you both.
We have a 32 bit application that is supposed be getting installed onto
64bit Server 2003, seeing as that we are still going to be using 32bit
SQL 2000, do you think it would be better for us to use the 32bit
version of Windows 2003?
Regards,
Steve|||IAE64 supports fully 64 bit and partially 32. Before this there was x64
allowing both architectures. As far as I know benefits (64) are visible when
you have more than 4 Gb RAM or 16 processors.
--
current location: alicante (es)
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You should really look into using SQL2005 since you can run the 64 bit
edition of 2005 on Win2003 X64. But if you stay with 2000 then the 32 bit
is the only choice and it will run just fine on X64.
--
Andrew J. Kelly SQL MVP
"Stevan" <stevanflores@.gmail.com> wrote in message
news:1142415651.698599.131360@.e56g2000cwe.googlegroups.com...
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You can certainly run SQL2000 SP4 on Windows 2003 x64. And there are some
benefits right away. For instance, SQL2000 gets to use 4GB by default.
Whether these benefits will bobble up to your app level is a different story.
The only way to determine whether using Windows 2003 x64 will provide better
performance for your app is for you to conduct some tests. For the workloads
I have tested, I get better performance on Windows 2003 x64 for some
workloads and no change in performance for the others >with Windows 2003 x86
vs x64 being the only variable<.
Linchi
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||stevanflores@.gmail.com wrote:
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
64-bit IA64 Itanium is your only 64-bit option for SQL Server 2000 and
it must run on equivalent Itanium hardware.
SQL Server 2005 supports three platforms: x86, x64 (AMD64 and Intel
EM64T), and Itanium
You can run SQL Server 2000 SP4 x86 and SQL Server 2005 x86 on Windows
Server 2003 x64 Edition.
David Gugick - SQL Server MVP
Quest Software
Can anyone confirm that the 64bit edition on SQL 2000 will only run on
Itanium 64 bit machines & not Xeon 64 bit processors?
Is there an Microsoft article on this?
Regards
SteveConfirmed.
(Don't know about MS article, haven't searched KB..)
On EM64T and 64 bit OS, you can run 32 bit SQL Server with sp4 in WOW, which is supported, but
probably not what you want.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Hi
Yes , 64bit runs on Itanium , but I don't remember the specific article
about that
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegroups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>|||Thank you both.
We have a 32 bit application that is supposed be getting installed onto
64bit Server 2003, seeing as that we are still going to be using 32bit
SQL 2000, do you think it would be better for us to use the 32bit
version of Windows 2003?
Regards,
Steve|||IAE64 supports fully 64 bit and partially 32. Before this there was x64
allowing both architectures. As far as I know benefits (64) are visible when
you have more than 4 Gb RAM or 16 processors.
--
current location: alicante (es)
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You should really look into using SQL2005 since you can run the 64 bit
edition of 2005 on Win2003 X64. But if you stay with 2000 then the 32 bit
is the only choice and it will run just fine on X64.
--
Andrew J. Kelly SQL MVP
"Stevan" <stevanflores@.gmail.com> wrote in message
news:1142415651.698599.131360@.e56g2000cwe.googlegroups.com...
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||You can certainly run SQL2000 SP4 on Windows 2003 x64. And there are some
benefits right away. For instance, SQL2000 gets to use 4GB by default.
Whether these benefits will bobble up to your app level is a different story.
The only way to determine whether using Windows 2003 x64 will provide better
performance for your app is for you to conduct some tests. For the workloads
I have tested, I get better performance on Windows 2003 x64 for some
workloads and no change in performance for the others >with Windows 2003 x86
vs x64 being the only variable<.
Linchi
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>|||stevanflores@.gmail.com wrote:
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
64-bit IA64 Itanium is your only 64-bit option for SQL Server 2000 and
it must run on equivalent Itanium hardware.
SQL Server 2005 supports three platforms: x86, x64 (AMD64 and Intel
EM64T), and Itanium
You can run SQL Server 2000 SP4 x86 and SQL Server 2005 x86 on Windows
Server 2003 x64 Edition.
David Gugick - SQL Server MVP
Quest Software
64bit SQL
Hi,
Can anyone confirm that the 64bit edition on SQL 2000 will only run on
Itanium 64 bit machines & not Xeon 64 bit processors?
Is there an Microsoft article on this?
Regards
Steve
Confirmed.
(Don't know about MS article, haven't searched KB..)
On EM64T and 64 bit OS, you can run 32 bit SQL Server with sp4 in WOW, which is supported, but
probably not what you want.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegr oups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>
|||Hi
Yes , 64bit runs on Itanium , but I don't remember the specific article
about that
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegr oups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>
|||Thank you both.
We have a 32 bit application that is supposed be getting installed onto
64bit Server 2003, seeing as that we are still going to be using 32bit
SQL 2000, do you think it would be better for us to use the 32bit
version of Windows 2003?
Regards,
Steve
|||IAE64 supports fully 64 bit and partially 32. Before this there was x64
allowing both architectures. As far as I know benefits (64) are visible when
you have more than 4 Gb RAM or 16 processors.
current location: alicante (es)
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>
|||You should really look into using SQL2005 since you can run the 64 bit
edition of 2005 on Win2003 X64. But if you stay with 2000 then the 32 bit
is the only choice and it will run just fine on X64.
Andrew J. Kelly SQL MVP
"Stevan" <stevanflores@.gmail.com> wrote in message
news:1142415651.698599.131360@.e56g2000cwe.googlegr oups.com...
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>
|||You can certainly run SQL2000 SP4 on Windows 2003 x64. And there are some
benefits right away. For instance, SQL2000 gets to use 4GB by default.
Whether these benefits will bobble up to your app level is a different story.
The only way to determine whether using Windows 2003 x64 will provide better
performance for your app is for you to conduct some tests. For the workloads
I have tested, I get better performance on Windows 2003 x64 for some
workloads and no change in performance for the others >with Windows 2003 x86
vs x64 being the only variable<.
Linchi
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>
|||stevanflores@.gmail.com wrote:
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
64-bit IA64 Itanium is your only 64-bit option for SQL Server 2000 and
it must run on equivalent Itanium hardware.
SQL Server 2005 supports three platforms: x86, x64 (AMD64 and Intel
EM64T), and Itanium
You can run SQL Server 2000 SP4 x86 and SQL Server 2005 x86 on Windows
Server 2003 x64 Edition.
David Gugick - SQL Server MVP
Quest Software
Can anyone confirm that the 64bit edition on SQL 2000 will only run on
Itanium 64 bit machines & not Xeon 64 bit processors?
Is there an Microsoft article on this?
Regards
Steve
Confirmed.
(Don't know about MS article, haven't searched KB..)
On EM64T and 64 bit OS, you can run 32 bit SQL Server with sp4 in WOW, which is supported, but
probably not what you want.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegr oups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>
|||Hi
Yes , 64bit runs on Itanium , but I don't remember the specific article
about that
<stevanflores@.gmail.com> wrote in message
news:1142414594.177552.235650@.z34g2000cwc.googlegr oups.com...
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
>
|||Thank you both.
We have a 32 bit application that is supposed be getting installed onto
64bit Server 2003, seeing as that we are still going to be using 32bit
SQL 2000, do you think it would be better for us to use the 32bit
version of Windows 2003?
Regards,
Steve
|||IAE64 supports fully 64 bit and partially 32. Before this there was x64
allowing both architectures. As far as I know benefits (64) are visible when
you have more than 4 Gb RAM or 16 processors.
current location: alicante (es)
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>
|||You should really look into using SQL2005 since you can run the 64 bit
edition of 2005 on Win2003 X64. But if you stay with 2000 then the 32 bit
is the only choice and it will run just fine on X64.
Andrew J. Kelly SQL MVP
"Stevan" <stevanflores@.gmail.com> wrote in message
news:1142415651.698599.131360@.e56g2000cwe.googlegr oups.com...
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>
|||You can certainly run SQL2000 SP4 on Windows 2003 x64. And there are some
benefits right away. For instance, SQL2000 gets to use 4GB by default.
Whether these benefits will bobble up to your app level is a different story.
The only way to determine whether using Windows 2003 x64 will provide better
performance for your app is for you to conduct some tests. For the workloads
I have tested, I get better performance on Windows 2003 x64 for some
workloads and no change in performance for the others >with Windows 2003 x86
vs x64 being the only variable<.
Linchi
"Stevan" wrote:
> Thank you both.
> We have a 32 bit application that is supposed be getting installed onto
> 64bit Server 2003, seeing as that we are still going to be using 32bit
> SQL 2000, do you think it would be better for us to use the 32bit
> version of Windows 2003?
> Regards,
> Steve
>
|||stevanflores@.gmail.com wrote:
> Hi,
> Can anyone confirm that the 64bit edition on SQL 2000 will only run on
> Itanium 64 bit machines & not Xeon 64 bit processors?
> Is there an Microsoft article on this?
> Regards
> Steve
64-bit IA64 Itanium is your only 64-bit option for SQL Server 2000 and
it must run on equivalent Itanium hardware.
SQL Server 2005 supports three platforms: x86, x64 (AMD64 and Intel
EM64T), and Itanium
You can run SQL Server 2000 SP4 x86 and SQL Server 2005 x86 on Windows
Server 2003 x64 Edition.
David Gugick - SQL Server MVP
Quest Software
Saturday, February 25, 2012
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
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
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
Subscribe to:
Posts (Atom)