deleting records takes forever

hi there.
I've got a bit of a problem on a clustered sql server. I don't know
if the cluster has anything to do with it, this is an entirely new
configuration for our company. We've moved out of the days of using
"white-box" pcs for servers to two dl380s clustered with an msa 1000
via fibre.
the servers are win2000 ent
mssql 2000 ent sp 3 (iirc)
with the advent of all this great technology, the big-brains
determined an excellent way to recoup costs of such an expense is to
release our dba. nice.
anyway. I've never run into this so I'm posting to this group, musing
whether this is due to clustering, table indexes or something else.
The cluster appears to be set up fine and we (small-brains) even
decided it was preduent to invest the money in calling MS for support.
Here'e the heart of the issue...
When deleting records from tables with indexes (mostly), by the index,
it takes absolutely forever. It took 8.5 hours to delete 1300 rows.
These are simple indexes, one field each, two indexes per table.
I say mostly because of the following:
deleting by the index, long time
deleting not by the index, fine execution
some tables, not deleting by the index, long time
example, contract table has two indexes on enteredby and contractid
(there are other objects, sps, views, triggers, constraints, etc.)
delete * from contract where enteredby = 5 and contractid < 1000
extremely long time to execute
delete * from contract where enteredby = 5
and
delete from contract where contractid < 1000
extremely long time
delete from contract where program > 1
fine execution
in another table - reports - deleting anything from that table results
in a long execution time whether i'm using indexes or not.
Now, when I select into another table (iirc just copies the data) and
perform any of the above (even on the reports table) execution is
perfect. Deletes several hundred rows <1 second.
Can anyone give me some guidance here?
thanks
>delete * from contract where enteredby = 5 and contractid < 1000
yeah, I know i messed that up

> decided it was preduent to invest the money in calling MS for support.
that too if anyone else noticed
|||Thanks for the suggestions.
In the event this ever gets indexed, it turned out that there were
constraints on the Comments table that referenced the Contract table.

Deleted SQL Resouce

i was deleting a disk resource from our cluster, the sql resource depended on that disk, when i was asked about deleting the dependent resources i clicked the delete option, so now i dont have any sql resourse. now i can′t see my SQL anymore even whe i
try to create a sql resource manually.
What can i do?
Thanks
I think you should
- copy the mdf and ldf file off to a safe location
- uninstall sql server from the cluster
- reinstall sql server to the cluster
- attach back the copied mdf and ldf files.
I don't think you can create the sql server resource without going through
the setup, but I could be wrong.
Vikram
"SQL Resource Deleted... Help!!!!" <SQL Resource Deleted...
Help!!!!@.discussions.microsoft.com> wrote in message
news:65D535D5-599A-4568-9E68-BFD2C496F73D@.microsoft.com...
> i was deleting a disk resource from our cluster, the sql resource depended
on that disk, when i was asked about deleting the dependent resources i
clicked the delete option, so now i dont have any sql resourse. now i cant
see my SQL anymore even whe i try to create a sql resource manually.
> What can i do?
> Thanks
>
|||Hi...
create SQL resource using below scripts.
It will create 3 sql resources and set proper private property values.
before you execute scripts, you should change what i comment
It will help you. and really works!!!
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server" /Create /Group:"SQLVS1 Group" /Type:"SQL
Server" <= Change group name
cluster . Resource "SQL Server" /AddDependency:"SQL Network Name(SQLVS1)" <=
Change network name
cluster . Resource "SQL Server" /AddDependency:"Physical Disk(Disk S" <=
Change Disk name
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\Rep lication"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\SQL serverAgent"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\Clu ster"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\MSS QLSERVER"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\PRO VIDERS"
cluster . Resource "SQL Server" /Priv VirtualServerName="SQLVS1" <= Change
SQL Server VS name
cluster . Resource "SQL Server" /Priv InstanceName="MSSQLSERVER"
cluster . Resource "SQL Server" /Priv RestartAction="2"
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server Agent" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server Agent" /Create /Group:"SQLVS1 Group"
/Type:"SQL Server Agent" <= Change group name
cluster . Resource "SQL Server Agent" /AddDependency:"SQL Server"
cluster . Resource "SQL Server Agent" /Priv VirtualServerName="SQLVS1" <=
Change SQL Server VS name
cluster . Resource "SQL Server Agent" /Priv InstanceName="MSSQLSERVER"
cluster . Resource "SQL Server Agent" /Priv RestartAction="2"
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server Fulltext" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server Fulltext" /Create /Group:"SQLVS1 Group"
/Type:"Microsoft Search Service Instance" <= Change group name
cluster . Resource "SQL Server Fulltext" /AddDependency:"SQL Server"
cluster . Resource "SQL Server Fulltext" /Priv ApplicationName="SQLServer"
cluster . Resource "SQL Server Fulltext" /Priv ApplicationPath="S:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA" <= Change Drive and folder name
where SQL Database installs

Cheolwon Choi
Senior Consultant / Consulting Biz Unit / Feelanet Co., Ltd.
Microsoft MCSE/MCSA/MCT, HP MasterASE/ACI
"SQL Resource Deleted... Help!!!!" <SQL Resource Deleted...
Help!!!!@.discussions.microsoft.com> wrote in message
news:65D535D5-599A-4568-9E68-BFD2C496F73D@.microsoft.com...
> i was deleting a disk resource from our cluster, the sql resource depended
on that disk, when i was asked about deleting the dependent resources i
clicked the delete option, so now i dont have any sql resourse. now i
can′t see my SQL anymore even whe i try to create a sql resource manually.
> What can i do?
> Thanks
>

Deleted SQL registry keys on Passive side of Active\Passive cluster config.

Hi, one of our network technicians accidently deleted both the following
registry keys on the passive side of our Active\Passive cluster
configuration for SQL 2000:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ]
Since we haven't at this stage started backing up the Passive server, those
registry keys can't be recovered from a backup.
In trying to resolve this issue, I have exported those keys from the Active
server, and imported them onto the Passive server.
This obviously isn't working as when I try to start Enterprise Manager, I
get a dialog box saying that:
Snap-in failed to initialize.
Name: <unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}
Query Analyzer works however.
What is recommended in this situation? How can I be guaranteed that the
cluster will failover correctly? I havent tested failover yet.
Can anyone tell me what the specific differences are between registry keys
when comparing the Active server and the Passive server?
Any help most appreciated!
Many thanks,
John
Hi, I have made some progress in this.
We have another SQL Active/Passive cluster set up (call them Active1 and
Passive1) and I exported the two keys from both these Active and Passive
servers and compared them through file comparison. This would give me clues
as to what I should be looking for on my original Active0 and Passive0
servers.
It seems that the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ] only
sets up the parameters in the client network utility i.e. tcp/ip and named
pipe alias for virtual instance. There is no difference between this key on
both Active1 and Passive1 servers. So I left that key alone on Active0 and
Passive0.
In relation to EM not opening up, I searched for the
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}on the Active0 server and found
within it a reference to "Microsoft SQL Enterprise Manager SnapIn" details.
I searched for "Microsoft SQL Enterprise Manager SnapIn" on the Passive0
server but could not find it!!!
Why is that? When [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ] was
deleted on Passive0, did this cascade delete
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F} ?
Well I exported the CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}key from
Active0 server and imported it into the Passive0 server, and now EM works.
In relation to [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ], I
compared this key on both Active1 and Passive1. There doesnt seem to be much
of a difference. These are the differences:
Active1:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server]
"SsrpActiveServer"="JJJ"
Passive1:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server]
Note: Here I guess I just remove the "SsrpActiveServer" reference on
Passive0 ?
Active1:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL
Server\80\Registration]
"DigitalProductID"=<removed>
"ProductID"="<removed>"
Passive1:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL
Server\80\Registration]
"DigitalProductID"=<removed>
"ProductID"="<removed>"
Note: Will this cause issues? Licensing is different on both servers.
Active1:
"uptime_pid"=dword:00000d8c
"uptime_time_utc"=hex:b1,48,31,c7,c4,ff,c5,01
Passive1:
"uptime_pid"=dword:00000ff0
"uptime_time_utc"=hex:10,88,f5,ba,d0,ff,c5,01
Note: I guess these don't really matter so I'll leave them alone
Active1:
<nothing>
Passive1:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL
Server\JJJ\MSSQLServer\SuperSocketNetLib\Lpc]
"LPCVersion"=dword:080007f7
Note: What do I do here? What is this LCPVersion? I cant find a reference to
it on Google.
Any help most appreciated!
Cheers,
John
"john clarke" <jclarke@.nospam.com> wrote in message
news:uNifJp8$FHA.3568@.TK2MSFTNGP09.phx.gbl...
> Hi, one of our network technicians accidently deleted both the following
> registry keys on the passive side of our Active\Passive cluster
> configuration for SQL 2000:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server]
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ]
> Since we haven't at this stage started backing up the Passive server,
> those registry keys can't be recovered from a backup.
> In trying to resolve this issue, I have exported those keys from the
> Active server, and imported them onto the Passive server.
> This obviously isn't working as when I try to start Enterprise Manager, I
> get a dialog box saying that:
> Snap-in failed to initialize.
> Name: <unknown>
> CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}
> Query Analyzer works however.
> What is recommended in this situation? How can I be guaranteed that the
> cluster will failover correctly? I havent tested failover yet.
> Can anyone tell me what the specific differences are between registry keys
> when comparing the Active server and the Passive server?
> Any help most appreciated!
> Many thanks,
> John
>
|||Hi
You are missing the basics of clustering.
The active node of the cluster has the only set of "working" registry keys
on the cluster (subset). This gets written to the quorum drive on a regular
basis. If you were to fail the cluster over to the other node, the other
node reads the quorum drives registry settings in and starts up SQL Server.
Try failing the cluster over, but at worst case, you will need to re-install
SQL Server on the whole cluster. Might teach the technician to leave things
alone he does not understand.
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/
"john clarke" <jclarke@.nospam.com> wrote in message
news:ephevV9$FHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi, I have made some progress in this.
> We have another SQL Active/Passive cluster set up (call them Active1 and
> Passive1) and I exported the two keys from both these Active and Passive
> servers and compared them through file comparison. This would give me
> clues as to what I should be looking for on my original Active0 and
> Passive0 servers.
> It seems that the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ] only
> sets up the parameters in the client network utility i.e. tcp/ip and named
> pipe alias for virtual instance. There is no difference between this key
> on both Active1 and Passive1 servers. So I left that key alone on Active0
> and Passive0.
> In relation to EM not opening up, I searched for the
> CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}on the Active0 server and
> found within it a reference to "Microsoft SQL Enterprise Manager SnapIn"
> details. I searched for "Microsoft SQL Enterprise Manager SnapIn" on the
> Passive0 server but could not find it!!!
> Why is that? When [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ]
> was deleted on Passive0, did this cascade delete
> CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F} ?
> Well I exported the CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}key from
> Active0 server and imported it into the Passive0 server, and now EM works.
> In relation to [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer ], I
> compared this key on both Active1 and Passive1. There doesnt seem to be
> much of a difference. These are the differences:
> --
> Active1:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server]
> "SsrpActiveServer"="JJJ"
> Passive1:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server]
> Note: Here I guess I just remove the "SsrpActiveServer" reference on
> Passive0 ?
> --
> Active1:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL
> Server\80\Registration]
> "DigitalProductID"=<removed>
> "ProductID"="<removed>"
> Passive1:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL
> Server\80\Registration]
> "DigitalProductID"=<removed>
> "ProductID"="<removed>"
> Note: Will this cause issues? Licensing is different on both servers.
> --
> Active1:
> "uptime_pid"=dword:00000d8c
> "uptime_time_utc"=hex:b1,48,31,c7,c4,ff,c5,01
> Passive1:
> "uptime_pid"=dword:00000ff0
> "uptime_time_utc"=hex:10,88,f5,ba,d0,ff,c5,01
> Note: I guess these don't really matter so I'll leave them alone
> --
> Active1:
> <nothing>
> Passive1:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL
> Server\JJJ\MSSQLServer\SuperSocketNetLib\Lpc]
> "LPCVersion"=dword:080007f7
> Note: What do I do here? What is this LCPVersion? I cant find a reference
> to it on Google.
> --
> Any help most appreciated!
> Cheers,
> John
> "john clarke" <jclarke@.nospam.com> wrote in message
> news:uNifJp8$FHA.3568@.TK2MSFTNGP09.phx.gbl...
>

Delete SQL server registration

Hello!
I would like to set up replication (cluster) on my SQL server 2000.
Unfortunately the server name is local so I cant create the replication. I
have to delete the server registration and create a new one with the servers
actual name. This is a production server. If I delete the registration
server and create a new one, will all my databases bee gone? Do I have to
create them from scratch or restore them from backup?
Hi,
You can very well delete the registration and re-register again.
This deletion just removes the entries from Enterprise manager and will not
create any impact to SQL server database or objects.
Thanks
Hari
SQL Server MVP
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>
|||The registration is just a connection into the MMC. You can safely delete
the registration. Make sure you've got the sa password before doing this
just in case!
"Terje Sundb?e" wrote:

> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the servers
> actual name. This is a production server. If I delete the registration
> server and create a new one, will all my databases bee gone? Do I have to
> create them from scratch or restore them from backup?
>
>
|||Thank you for your swift reply
Can I set up a two node cluster with SQL server 200 enterprise edition and
windows server 2003 standard edition?
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>
|||Hi,
You need windows 2003 Enterprise Edition to setup the 2 node cluster.
Regards
Srini
"Terje Sundb?e" wrote:

> Thank you for your swift reply
> Can I set up a two node cluster with SQL server 200 enterprise edition and
> windows server 2003 standard edition?
> "Terje Sundb?e" <tsundboe@.msn.com> wrote in message
> news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
>
>

Delete SQL server registration

Hello!
I would like to set up replication (cluster) on my SQL server 2000.
Unfortunately the server name is local so I cant create the replication. I
have to delete the server registration and create a new one with the servers
actual name. This is a production server. If I delete the registration
server and create a new one, will all my databases bee gone? Do I have to
create them from scratch or restore them from backup?Hi,
You can very well delete the registration and re-register again.
This deletion just removes the entries from Enterprise manager and will not
create any impact to SQL server database or objects.
Thanks
Hari
SQL Server MVP
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||The registration is just a connection into the MMC. You can safely delete
the registration. Make sure you've got the sa password before doing this
just in case!
"Terje Sundb?e" wrote:

> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the serve
rs
> actual name. This is a production server. If I delete the registration
> server and create a new one, will all my databases bee gone? Do I have to
> create them from scratch or restore them from backup?
>
>|||Thank you for your swift reply
Can I set up a two node cluster with SQL server 200 enterprise edition and
windows server 2003 standard edition?
"Terje Sundbe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||Hi,
You need windows 2003 Enterprise Edition to setup the 2 node cluster.
Regards
Srini
"Terje Sundb?e" wrote:

> Thank you for your swift reply
> Can I set up a two node cluster with SQL server 200 enterprise edition and
> windows server 2003 standard edition?
> "Terje Sundb?e" <tsundboe@.msn.com> wrote in message
> news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
>
>

Delete SQL server registration

Hello!
I would like to set up replication (cluster) on my SQL server 2000.
Unfortunately the server name is local so I cant create the replication. I
have to delete the server registration and create a new one with the servers
actual name. This is a production server. If I delete the registration
server and create a new one, will all my databases bee gone? Do I have to
create them from scratch or restore them from backup?Hi,
You can very well delete the registration and re-register again.
This deletion just removes the entries from Enterprise manager and will not
create any impact to SQL server database or objects.
Thanks
Hari
SQL Server MVP
"Terje Sundbøe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||The registration is just a connection into the MMC. You can safely delete
the registration. Make sure you've got the sa password before doing this
just in case!
"Terje Sundbøe" wrote:
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the servers
> actual name. This is a production server. If I delete the registration
> server and create a new one, will all my databases bee gone? Do I have to
> create them from scratch or restore them from backup?
>
>|||Thank you for your swift reply
Can I set up a two node cluster with SQL server 200 enterprise edition and
windows server 2003 standard edition?
"Terje Sundbøe" <tsundboe@.msn.com> wrote in message
news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I would like to set up replication (cluster) on my SQL server 2000.
> Unfortunately the server name is local so I cant create the replication. I
> have to delete the server registration and create a new one with the
> servers actual name. This is a production server. If I delete the
> registration server and create a new one, will all my databases bee gone?
> Do I have to create them from scratch or restore them from backup?
>|||Hi,
You need windows 2003 Enterprise Edition to setup the 2 node cluster.
Regards
Srini
"Terje Sundbøe" wrote:
> Thank you for your swift reply
> Can I set up a two node cluster with SQL server 200 enterprise edition and
> windows server 2003 standard edition?
> "Terje Sundbøe" <tsundboe@.msn.com> wrote in message
> news:uy1cNjSuFHA.2592@.TK2MSFTNGP09.phx.gbl...
> > Hello!
> >
> > I would like to set up replication (cluster) on my SQL server 2000.
> > Unfortunately the server name is local so I cant create the replication. I
> > have to delete the server registration and create a new one with the
> > servers actual name. This is a production server. If I delete the
> > registration server and create a new one, will all my databases bee gone?
> > Do I have to create them from scratch or restore them from backup?
> >
>
>