Alter table that is masked for replication

Hi,
I have a snapshot replication between two SQL server 2000 (Main Svr & Backup
Svr). At present the replication is working well but now due enhancement i
need to alter soem of the table. When I perform the alteration to the 'Main
Svr' I get a error message 'Cannot delete and create table and it is used by
replication'.
How can I alter the table?
After alteration will this be reflected back to 'Backup Svr'?
The 'Backup Svr' is hosted as another SQL instance on a Win2k server. How
can I connect to this second instance using ADODB (V2.8) from visual basic.
Thanks
Hari
Hi Paul,
Thanks for that let me try it.
Hope you could also help me on this.
How can i connect to secodn sql instance using ADODB in VB.
Thanks,
Hari
"Paul Ibison" wrote:

> To add a column, use sp_repladdcolumn. To drop one use
> sp_repldropcolumn. To change an existing column, you
> could add a new column with the new datatype
> (sp_repladdcolumn), do an update on the table to populate
> the column, then drop the column (sp_repldropcolumn). Do
> this again to create the column having the same original
> name. Alternatively you could use:
> sp_dropsubscription @.publication = 'northwindxxx'
> , @.article = 'region'
> , @.subscriber = 'pll-lt-16'
> sp_droparticle @.publication = 'northwindxxx'
> , @.article = 'region'
> sp_refreshsubscriptions @.publication ='northwindxxx'
> And do the opposite to add back in once the change has
> been made.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Alter table impossible due to replication

Hi !
I can't alter a table because of an old problematical replication.
I've already tried to clean the replication informations with
sp_removedbreplication and sp_msunmarkreplinfo. I've already update
sysobjects to set replinfo to 0 for the table that I try to update. I've
also the database from the replication configuration.
When I try to alter the table, I receive this message in french :
table 'Espaces'
- Impossible de modifier la table.
Erreur ODBC : [Microsoft][ODBC SQL Server Driver][SQL Server]chec de ALTER
TABLE DROP COLUMN car 'Esp_Ceremonie' est actuellement rpliqu.
In english, I think it would be : Error ALTER TABLE DROP COLUMN because
'xxxx' is at the moment replicated.
I hope someone can help me !
Thanks in advance !!!!
Bernard
bernard.borsu@.odysseos.net
can you try this:
EXEC sp_configure 'allow',1
go
reconfigure with override
go
use your_database_name
go
update sysobjects set replinfo = 0 where name = 'your_table_name'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go
(thanks to Vyas http://vyaskn.tripod.com/repl_ans3.htm#replinfo for this)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Bernard Borsu" <bernard.borsu@.odysseos.net> wrote in message
news:ulGiJaA%23EHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi !
> I can't alter a table because of an old problematical replication.
> I've already tried to clean the replication informations with
> sp_removedbreplication and sp_msunmarkreplinfo. I've already update
> sysobjects to set replinfo to 0 for the table that I try to update. I've
> also the database from the replication configuration.
> When I try to alter the table, I receive this message in french :
> table 'Espaces'
> - Impossible de modifier la table.
> Erreur ODBC : [Microsoft][ODBC SQL Server Driver][SQL Server]chec de
ALTER
> TABLE DROP COLUMN car 'Esp_Ceremonie' est actuellement rpliqu.
> In english, I think it would be : Error ALTER TABLE DROP COLUMN because
> 'xxxx' is at the moment replicated.
> I hope someone can help me !
> Thanks in advance !!!!
> --
> Bernard
> bernard.borsu@.odysseos.net
>
|||Thanks for your solution, but i've already tried this solution and the error
message is always the same.
"Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de news:
Odw1pwB%23EHA.1296@.TK2MSFTNGP10.phx.gbl...
> can you try this:
> EXEC sp_configure 'allow',1
> go
> reconfigure with override
> go
> use your_database_name
> go
> update sysobjects set replinfo = 0 where name = 'your_table_name'
> go
> EXEC sp_configure 'allow',0
> go
> reconfigure with override
> go
> (thanks to Vyas http://vyaskn.tripod.com/repl_ans3.htm#replinfo for this)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Bernard Borsu" <bernard.borsu@.odysseos.net> wrote in message
> news:ulGiJaA%23EHA.2680@.TK2MSFTNGP09.phx.gbl...
> ALTER
>

Alter table errors due to statistics

I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
GaryRun:
drop statistics hind_61_3
and then do your ALTER TABLE.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>> Run:
>> drop statistics hind_61_3
>> and then do your ALTER TABLE.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
>> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
>> I'm attempting to change a column data type from int to nvarchar(16) on a
>> production database. When executing:
>> alter table x alter column y nvarchar(16)
>> I get the error:
>> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
>> this
>> column
>>
>> I would be forever grateful if someone could tell me how to get around
>> this
>> issue.
>> Thanks in advance,
>> Gary
>>
>

Alter table errors due to statistics

I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
Run:
drop statistics hind_61_3
and then do your ALTER TABLE.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>
sql

alter column fails due to statistics

Hello,

A script we run against the database as part of the upgrade of our product
is failing with the following message:

ALTER TABLE ALTER COLUMN EncodedID failed because STATISTICS hind_61_3
accesses this column

The line that fails is:

alter table Badge alter column EncodedID nvarchar(16)

It's clear that there's some kind of automatically generated statistics
object referencing the column that prevents us from changing it from an
int to an nvarchar. However, I have no idea how that got there - my best
guess would be that it has something to do with the auto generate
statistics option being set on the database. However that seems odd to me
because we've done lots and lots of work like this and not encountered the
problem. It also seems like a quick fix would be to perform:

Drop Statistics Badge.encodedid

However I am afraid subsequent statements might fail on this db since I
wasn't really expecting this in the first place. Does anyone have any
insight?

DaveMetal Dave (metal@.spam.spam) writes:
> A script we run against the database as part of the upgrade of our product
> is failing with the following message:
> ALTER TABLE ALTER COLUMN EncodedID failed because STATISTICS hind_61_3
> accesses this column
> The line that fails is:
> alter table Badge alter column EncodedID nvarchar(16)
> It's clear that there's some kind of automatically generated statistics
> object referencing the column that prevents us from changing it from an
> int to an nvarchar. However, I have no idea how that got there - my best
> guess would be that it has something to do with the auto generate
> statistics option being set on the database. However that seems odd to me
> because we've done lots and lots of work like this and not encountered the
> problem.

I played around a little, and not all table changes caused complaints
about statistics. But changing a column from varchar to nvarchar did.

The statistics in question is not a regular auto-statistics, their
names are different. My guess is that this could be something created
by the Index Tuning Wizard. "hind" makes me think of "hypothetical indexes".

>It also seems like a quick fix would be to perform:
> Drop Statistics Badge.encodedid

Well, "DROP STATISTICS Badge.hind_61_3" would be better.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 20 Dec 2004, Erland Sommarskog wrote:

> The statistics in question is not a regular auto-statistics, their
> names are different. My guess is that this could be something created
> by the Index Tuning Wizard. "hind" makes me think of "hypothetical indexes".

This makes a lot of sense. Thanks for the input. It also explains how this
might have gotten onto a customers database, if their dba started poking
around without us knowing about it.

Do you happen to know the naming scheme of the auto stats?

> >It also seems like a quick fix would be to perform:
> > Drop Statistics Badge.encodedid
> Well, "DROP STATISTICS Badge.hind_61_3" would be better.

Oops, that's what I intended to type. Thanks for the correction. I don't
even think mine would have run.

Dave|||Metal Dave (metal@.spam.spam) writes:
> Do you happen to know the naming scheme of the auto stats?

The ones I have seen are like _WA_Sys_columnname_1AD3FDA4.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

All schedulers on Node 0 appear deadlocked due to a large number o

Hi,
I've noticed the following error message in the event viewer being reported
by SQL Server for a site that we've had online for several months without
incident. However, every so often now, SQL server is locking up and it seems
to be occurring after this error message:
All schedulers on Node 0 appear deadlocked due to a large number of worker
threads waiting on MSSEARCH
I can't seem to find any information on the specifics of what this error
means (or even the error number) or any recommendations for resolving it.
I'm presuming that this could be caused by a code issue, however, since there
Full Text Search is being called by itself (in the application) when it is
used, I don't see how this could be causing a deadlock in the traditional
sense. It also is weird that this seems to happen all at once, go away when
the server is restarted and then come back after a period of time (usually
during a period of low usage, like at night or over a weekend) ...
Thanks,
JeremyHello Jeremy,
Based on my experience, this problem might be a known issue fixed in SP1.
some of xp(name started with sql_OA) in SQL has CoInitialize leak that
causes FTS related issue. Full Text threads all waiting to be signaled,
with no apparent thread to signal them, causing a SQL Server Scheduler to
become hung.
Do you have SQL 2005 sp2 installed? If not, please apply SP2 to see if the
issue is resolved. If you have any update, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Peter,
We do have SP2 installed, already ...
Thanks!
""Peter Yang[MSFT]"" wrote:
> Hello Jeremy,
> Based on my experience, this problem might be a known issue fixed in SP1.
> some of xp(name started with sql_OA) in SQL has CoInitialize leak that
> causes FTS related issue. Full Text threads all waiting to be signaled,
> with no apparent thread to signal them, causing a SQL Server Scheduler to
> become hung.
> Do you have SQL 2005 sp2 installed? If not, please apply SP2 to see if the
> issue is resolved. If you have any update, please feel free to let's know.
> Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jeremy,
Since you have SP2 installed, I suspect the problem might be caused by your
code related to FTS. Since FTS is deadlock due to this error, SQL Server
sessions waiting on MSSEARCH waittype, and some unresponsiveness in SQL
Server itself.
You need to restart FTS and/or SQL service to work around the problem. If
the issue persists, a system reboot is necessary. If you'd like to know the
root cause of the problem, you may need to analyze memory dumps, this work
has to be done by contacting Microsoft Product Support Services. Therefore,
we probably will not be able to resolve the issue through the newsgroups.
I recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Why wouldn't the deadlock manager be resolving the issue, if it is just a
deadlock issue?
Thanks,
Jeremy
""Peter Yang[MSFT]"" wrote:
> Hello Jeremy,
> Since you have SP2 installed, I suspect the problem might be caused by your
> code related to FTS. Since FTS is deadlock due to this error, SQL Server
> sessions waiting on MSSEARCH waittype, and some unresponsiveness in SQL
> Server itself.
> You need to restart FTS and/or SQL service to work around the problem. If
> the issue persists, a system reboot is necessary. If you'd like to know the
> root cause of the problem, you may need to analyze memory dumps, this work
> has to be done by contacting Microsoft Product Support Services. Therefore,
> we probably will not be able to resolve the issue through the newsgroups.
> I recommend that you open a Support incident with Microsoft Product Support
> Services so that a dedicated Support Professional can assist with this
> case. If you need any help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> When responding to posts, please "Reply to Group" via your
> newsreader so that others may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jeremy,
This is not a simple deadlock situation that could be resolved by SQL
engine itself.
When the lock monitor initiates deadlock search for a particular thread, it
identifies the resource on which the thread is waiting. The lock monitor
then finds the owner(s) for that particular resource and recursively
continues the deadlock search for those threads until it finds a cycle. A
cycle identified in this manner forms a deadlock.
However, the deadlock is caused by FTS which is not a component inside SQL
engine. Therefore, it's more like a distributed deadlock that could not be
resolved by SQL engine itself.
Please understand above is just a suspect and you need to contact MS PSS to
analyze memory dump so taht you may get more clues on this issue. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Peter,
Thanks for that clarification. Out of curiosity, since this appears to be
able to hang FTE for all databases (and the SQL server itself, in entirely),
do you know how hosting providers (with multiple clients each writing
uncontrollable queries) handle this issue?
Is there a particularly good way to detect this problem automatically and
restart FTE (since the process still looks to be in a normal state, even
while it is actually hung ...)
Thanks,
Jeremy
""Peter Yang[MSFT]"" wrote:
> Hello Jeremy,
> This is not a simple deadlock situation that could be resolved by SQL
> engine itself.
> When the lock monitor initiates deadlock search for a particular thread, it
> identifies the resource on which the thread is waiting. The lock monitor
> then finds the owner(s) for that particular resource and recursively
> continues the deadlock search for those threads until it finds a cycle. A
> cycle identified in this manner forms a deadlock.
> However, the deadlock is caused by FTS which is not a component inside SQL
> engine. Therefore, it's more like a distributed deadlock that could not be
> resolved by SQL engine itself.
> Please understand above is just a suspect and you need to contact MS PSS to
> analyze memory dump so taht you may get more clues on this issue. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> When responding to posts, please "Reply to Group" via your
> newsreader so that others may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jeremy,
Thank you for your reply.
When the issue occurs, you should be able to find Sysprocesses shows lots
of queries waiting on MSSEARCH. To temporarily work around the issue, you
may want to develope some monitor tools to check the status of
Sysprocesses. If you find too many Sysprocesses are waiting for MSSEARCH,
you may try to restart FTS service to see if the issue is not solved. If
not, it should notify admin to manually do some operations.
As I mention, to find the root cause of the issue, it's suggested that you
contact MS PSS for dump analysis. If you have any further qusetions or
concerns on this, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
<http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone>.
Feedback on your service and satisfaction can be posted to:
from the web interface: Partner Feedback
from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jeremy and Peter,
Any progress on this issue? One of my colleagues just stumbled upon
the same bug/feature. Should we open contact PSS?
Thanks!
On Mar 26, 5:54 am, pet...@.online.microsoft.com ("Peter Yang[MSFT]")
wrote:
> Hello Jeremy,
> Thank you for your reply.
> When the issue occurs, you should be able to find Sysprocesses shows lots
> of querieswaitingonMSSEARCH. To temporarily work around the issue, you
> may want to develope some monitor tools to check the status of
> Sysprocesses. If you find too many Sysprocesses arewaitingforMSSEARCH,
> you may try to restart FTS service to see if the issue is not solved. If
> not, it should notify admin to manually do some operations.
> As I mention, to find the root cause of the issue, it's suggested that you
> contact MS PSS for dump analysis. If you have any further qusetions or
> concerns on this, please feel free to let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> =====================================================> Please note that the newsgroups are staffed weekdays with a goal to provide
> ONE BUSINESS DAY RESPONSE toallposts.
> If this response time does not meet your needs, please contact CSS for more
> immediate assistance:http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone...
> <http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone>.
> Feedback on your service and satisfaction can be posted to:
> from the web interface: Partner Feedback
> from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your
> newsreader so that others may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.