Hi everybody,
I'm having a big problem when I want to rename my Failover Clustered SQL
Server 2005 Virtual Server. I've followed the indications given in this
how-to: http://msdn2.microsoft.com/en-us/library/ms178083.aspx but when I
try to bring my SQL Server online (after changing the network name, of
course...) I get this error:
[sqsrvres] GetRegKeyAccessMask: Could not get registry access mask for
registry key Software\Microsoft\Microsoft SQL Server\MSSQL.3\Cluster (status
0))
If I return back to the Virtual Server's old name I can bring SQL Server
online allright...
I've found no information about this error. Can anybody help me?
Thanks in advance,
Petrus
Create the following regkey:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server\MSSQL.3\Cluster]
"ClusterName"="<NewVirtualServerName>"
I had the same problem, and MSSQL.3 referred to Reporting Services. I'm guessing that since Reporting Services isn't cluster aware, the parameters aren't updated (or even created). But when SQL starts all its instances up, it gets confused that the parameter isn't there to change.
However, this still didn't have the desired result for me. The database engine and the OLAP services were MSSQL.4 and .5 respectively. The cooresponding Cluster keys as above continually renamed themselves back to the old name.
-Tom Kretzmer
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
|||Sorry to post an update so soon. Just update MSSQL.4 and .5 on both nodes of the cluster and restart resources. For some reason "select @.@.servername" still comes up as the old instance name, but everything else works as it should.
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
Showing posts with label clustered. Show all posts
Showing posts with label clustered. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Removing rows from very large table with indices
Hello,
I've got a table with a few million rows in it. This table has one clustere
d index and four other indices. When I try to delete records from it on a d
ay-by-day basis (it has a date column), it typically takes between 2 and 5 m
inutes to remove just one d
ay worth. I need to purge about 90 days worth of data ...
Is there a way to shut off index recomputation or whatever it is that takes
so long for the delete to occur? This is an operational data table that rea
lly only needs about 7 days worth of data in it (not 90).
Thanks
-- JakeHi Jake.
How are you performing the delete statement - row by row or by a single
statement supported by a where clause?
It's important to understand that the indexes will actually help the delete
to identify the rows that meet the criteria for deletion, so "shutting" down
the index during deletion would cause the unenviable effect that a table
scan would be required to identify the rows for deletion...
Regards,
Greg Linwood
SQL Server MVP
"javatopia" <anonymous@.discussions.microsoft.com> wrote in message
news:8D297AF5-9D9A-438F-9A76-971F9DF2D0C1@.microsoft.com...
> Hello,
> I've got a table with a few million rows in it. This table has one
clustered index and four other indices. When I try to delete records from
it on a day-by-day basis (it has a date column), it typically takes between
2 and 5 minutes to remove just one day worth. I need to purge about 90 days
worth of data ...
> Is there a way to shut off index recomputation or whatever it is that
takes so long for the delete to occur? This is an operational data table
that really only needs about 7 days worth of data in it (not 90).
> Thanks
> -- Jake
>|||Hi,
I don't recommend you to delete indexes because proper indexes will help you
to locate the records that you want to delete more quickly . (You have to
find the records first before you delete them.)
If you want to delete records in a single transaction (and if you are deleti
ng large amount of data) your log file will grow fast no matter what you're
recovery model is.
I'll prefer to set rowcount and delete records in a while loop until @.@.rowco
unt = 0. This will delete you're records in smaller transactions.
Also selecting the records that you like to keep in a new table and dropping
the old table and renaming the new table is generally less expensive then d
elete. (Depends the percentage of tha data that you want to keep and you wan
t to delete.)
And partitioned tables are very helpfull for archiving and deleting..
Regards..
Umut Nazl?ca, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)|||I like the suggestion of selecting INTO a new table, dropping the old table,
and then renaming the new table to the old one. Seems like that could be d
one in an automated manner as well, right? Is it possible to put the DB int
o single-user mode via a st
ored procedure, then do all of this (select into, drop old, rename table)?
Your help is very much appreciated!!
-- Jake|||Hi,
Yes you can put db into single user mode using alter database and terminatio
n options like:
ALTER DATABASE XXX
SET SINGLE_USER WITH ROLLBACK AFTER X (See Books Online ..)
and you can schedule a job to automate it.
My suggestion is first script your table then create your new table using th
is script (at this point do not create the indexes on new table), transfer
data, create indexes on new table and so on.
But test first to see if it works the way you want..
Regards..
I've got a table with a few million rows in it. This table has one clustere
d index and four other indices. When I try to delete records from it on a d
ay-by-day basis (it has a date column), it typically takes between 2 and 5 m
inutes to remove just one d
ay worth. I need to purge about 90 days worth of data ...
Is there a way to shut off index recomputation or whatever it is that takes
so long for the delete to occur? This is an operational data table that rea
lly only needs about 7 days worth of data in it (not 90).
Thanks
-- JakeHi Jake.
How are you performing the delete statement - row by row or by a single
statement supported by a where clause?
It's important to understand that the indexes will actually help the delete
to identify the rows that meet the criteria for deletion, so "shutting" down
the index during deletion would cause the unenviable effect that a table
scan would be required to identify the rows for deletion...
Regards,
Greg Linwood
SQL Server MVP
"javatopia" <anonymous@.discussions.microsoft.com> wrote in message
news:8D297AF5-9D9A-438F-9A76-971F9DF2D0C1@.microsoft.com...
> Hello,
> I've got a table with a few million rows in it. This table has one
clustered index and four other indices. When I try to delete records from
it on a day-by-day basis (it has a date column), it typically takes between
2 and 5 minutes to remove just one day worth. I need to purge about 90 days
worth of data ...
> Is there a way to shut off index recomputation or whatever it is that
takes so long for the delete to occur? This is an operational data table
that really only needs about 7 days worth of data in it (not 90).
> Thanks
> -- Jake
>|||Hi,
I don't recommend you to delete indexes because proper indexes will help you
to locate the records that you want to delete more quickly . (You have to
find the records first before you delete them.)
If you want to delete records in a single transaction (and if you are deleti
ng large amount of data) your log file will grow fast no matter what you're
recovery model is.
I'll prefer to set rowcount and delete records in a while loop until @.@.rowco
unt = 0. This will delete you're records in smaller transactions.
Also selecting the records that you like to keep in a new table and dropping
the old table and renaming the new table is generally less expensive then d
elete. (Depends the percentage of tha data that you want to keep and you wan
t to delete.)
And partitioned tables are very helpfull for archiving and deleting..
Regards..
Umut Nazl?ca, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)|||I like the suggestion of selecting INTO a new table, dropping the old table,
and then renaming the new table to the old one. Seems like that could be d
one in an automated manner as well, right? Is it possible to put the DB int
o single-user mode via a st
ored procedure, then do all of this (select into, drop old, rename table)?
Your help is very much appreciated!!
-- Jake|||Hi,
Yes you can put db into single user mode using alter database and terminatio
n options like:
ALTER DATABASE XXX
SET SINGLE_USER WITH ROLLBACK AFTER X (See Books Online ..)
and you can schedule a job to automate it.
My suggestion is first script your table then create your new table using th
is script (at this point do not create the indexes on new table), transfer
data, create indexes on new table and so on.
But test first to see if it works the way you want..
Regards..
Saturday, February 25, 2012
Removing clustered index on Primary key.
Hi,
I want to remove clustering of the Primary Key and make a foreign key in the
table the clustered index. Can I do this with a replicated database? I
suppose I hav to do this then on the Publisher and Subscriber database. Does
anybody has any experience with this?
TIA,
Stefan
You have to:
1. exclude this table from publication
2. do the required manipulations of table schema on publisher and
subscribers
3. add table back to publication.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:uA3bA6LZEHA.728@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to remove clustering of the Primary Key and make a foreign key in
the
> table the clustered index. Can I do this with a replicated database? I
> suppose I hav to do this then on the Publisher and Subscriber database.
Does
> anybody has any experience with this?
> TIA,
> Stefan
>
|||Stefan,
when I tested this, I found it was possible in merge but not in
transactional replication. Using merge, you should be able to do it directly
on the publisher and using sp_addscriptexec on the subscribers. For
transactional, you'll need to drop then recreate the publication after
making the changes.
HTH,
Paul Ibison
|||I was able to do it by dropping the publication modifying the table and then
replicating it.
Not sure if this is what you want or not.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBMjuFQZEHA.3112@.tk2msftngp13.phx.gbl...
> Stefan,
> when I tested this, I found it was possible in merge but not in
> transactional replication. Using merge, you should be able to do it
directly
> on the publisher and using sp_addscriptexec on the subscribers. For
> transactional, you'll need to drop then recreate the publication after
> making the changes.
> HTH,
> Paul Ibison
>
I want to remove clustering of the Primary Key and make a foreign key in the
table the clustered index. Can I do this with a replicated database? I
suppose I hav to do this then on the Publisher and Subscriber database. Does
anybody has any experience with this?
TIA,
Stefan
You have to:
1. exclude this table from publication
2. do the required manipulations of table schema on publisher and
subscribers
3. add table back to publication.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:uA3bA6LZEHA.728@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to remove clustering of the Primary Key and make a foreign key in
the
> table the clustered index. Can I do this with a replicated database? I
> suppose I hav to do this then on the Publisher and Subscriber database.
Does
> anybody has any experience with this?
> TIA,
> Stefan
>
|||Stefan,
when I tested this, I found it was possible in merge but not in
transactional replication. Using merge, you should be able to do it directly
on the publisher and using sp_addscriptexec on the subscribers. For
transactional, you'll need to drop then recreate the publication after
making the changes.
HTH,
Paul Ibison
|||I was able to do it by dropping the publication modifying the table and then
replicating it.
Not sure if this is what you want or not.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBMjuFQZEHA.3112@.tk2msftngp13.phx.gbl...
> Stefan,
> when I tested this, I found it was possible in merge but not in
> transactional replication. Using merge, you should be able to do it
directly
> on the publisher and using sp_addscriptexec on the subscribers. For
> transactional, you'll need to drop then recreate the publication after
> making the changes.
> HTH,
> Paul Ibison
>
Subscribe to:
Posts (Atom)