Hello there
I have a created a puclision
When i create the subcription it tells me that the subscription was
succedded but it didn't faild creationg the agent. 5 - Access denied
What can cause the problem and how can i deal with it?
Did you configure an anonymous subscription? Was the publication configured
for anonymous subscriptions?
How are you deploying your snapshot through FTP or a UNC?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Oded Kovach" <roygoldh@.hotmail.com> wrote in message
news:eC5wsWzwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have a created a puclision
> When i create the subcription it tells me that the subscription was
> succedded but it didn't faild creationg the agent. 5 - Access denied
> What can cause the problem and how can i deal with it?
>
Showing posts with label didnt. Show all posts
Showing posts with label didnt. Show all posts
Tuesday, March 20, 2012
Sunday, February 19, 2012
Creating indexes with ONLINE = ON - failing
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Hi Andrew
Thank you for your time.
We tried the following:
1. Manually creating the index and checking the Allow online processing of DML statements...
2. The following DDL statements:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[table1]') AND name = N'index1')DROP INDEX [index1] ON [dbo].[table1]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]
([field1] ASC,[field2] ASC,[field3] ASC,[field4] ASC,[field5] ASC,[field6] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]
3. We tried the above statement with only ONLINE = ON option, to avoid possible conflicts with backward compatibility keywords (as stated in BOL).
4. We also tried ALTER INDEX ALL ON dbo.table1 REBUILD WITH (ONLINE = ON);
Database compatibility level is 90.
All of the above with the same result.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||We also have a clustered index on that table, we also tried to recreate that index with ONLINE = ON, BOL stating that if a clustered index is created with ONLINE = ON, all non-clustered indexes will be rebuilt with ONLINE = ON.
Same outcome.
Thx again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||I think there is a general bug somewhere... this is not the only option that
gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a while
ago, but at the time did not catch the online option as well. You've
spurred me to finally enter a bug report after retrieving my notes from back
then:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our
minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD
WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then
Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a
fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max),
nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
not sure if index is created with online = on, as it doesn't came up this
way when querying sys.indexes (with all the joins towards sys.index_columns
and sys.columns) or at least I didn't saw that option listed anywhere.
So my guess is that somehow that that option (along with many more, as you
also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
the index is created as stated in my statement. So I think that there is no
workaround available. One can only assume that the index is created as one
expected, but there is no way to get a confirmation.
I will also test on Katmai tomorrow and post my findings on connect.
Thanks for the heads up related to Katmai.
Regards,
Alin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23z9%23CgD2HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I think there is a general bug somewhere... this is not the only option
>that gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a
>while ago, but at the time did not catch the online option as well. You've
>spurred me to finally enter a bug report after retrieving my notes from
>back then:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> "Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
> news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi list
> Did a search on all SQL related newsgroups, but didn't find anything.
> So, here's my problem:
> We are running SQL 2005 Enterprise edition.
> SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
> (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
> 1)".
> We want to rebuild our indexes on 2 tables with the ONLINE option set to
> ON. They are currently set to OFF. We tried everything that came across
> our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename>
> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
> Apparently, the statement executes with no errors, but index is not
> created if ONLINE set to ON. When scritping the indexes or right-clicking
> and then Properties, they all appear with ONLINE = OFF.
> We also tried to isolate the problem, by creating a fresh new database, a
> fresh new table and then play around with indexes. Same outcome.
> Also tested this on a developer edition, with same outcome.
> Table doesn't have any LOB columns (text, ntext, image, varchar(max),
> nvarchar(max)).
> Any ideas, welcome.
> Thanks in advance for any thoughts.
> Regards,
> Alin
>
|||I guess having those options during the CREATE INDEX statement only affect
the initial creation of the index, and do not represent any long-term
setting that "sticks."
My guess is that it will come back as "by design." E.g. you can't specify
up front that every time you change the index, those settings will be true.
If you want to rebuild the index and rebuild it online, you will need to
specify that in the ALTER INDEX command.
Then again, if I want to script the exact CREATE INDEX statement that I ran
yesterday, I can't see what harm it would have to maintain the properties
with the index as it was created, even if you will still need to specify
those options explicitly when using ALTER. Right now it doesn't seem
possible using the tools to re-generate the exact same CREATE INDEX script
that I ran yesterday...
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
> Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
> not sure if index is created with online = on, as it doesn't came up this
> way when querying sys.indexes (with all the joins towards
> sys.index_columns and sys.columns) or at least I didn't saw that option
> listed anywhere.
> So my guess is that somehow that that option (along with many more, as you
> also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
> the index is created as stated in my statement. So I think that there is
> no workaround available. One can only assume that the index is created as
> one expected, but there is no way to get a confirmation.
> I will also test on Katmai tomorrow and post my findings on connect.
> Thanks for the heads up related to Katmai.
> Regards,
> Alin
|||I agree, but I still wondering why this option is not reflected in the index
properties, when right-click the index, then on properties.
My feeling is that this is a bug in the UI. Also, you cannot retrieve this
piece of information from any of the DMV's that shows any index options. Or
maybe it's just me
, I don't know. I will further test it tomorrow, but as
of today, these are my conclusions (including your opinions on this).
I will come back with more news tomorrow.
Again, thanks for your efforts.
Regards,
Alin Selicean
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eELWJeE2HHA.5772@.TK2MSFTNGP02.phx.gbl...
>I guess having those options during the CREATE INDEX statement only affect
>the initial creation of the index, and do not represent any long-term
>setting that "sticks."
> My guess is that it will come back as "by design." E.g. you can't specify
> up front that every time you change the index, those settings will be
> true. If you want to rebuild the index and rebuild it online, you will
> need to specify that in the ALTER INDEX command.
> Then again, if I want to script the exact CREATE INDEX statement that I
> ran yesterday, I can't see what harm it would have to maintain the
> properties with the index as it was created, even if you will still need
> to specify those options explicitly when using ALTER. Right now it
> doesn't seem possible using the tools to re-generate the exact same CREATE
> INDEX script that I ran yesterday...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
> news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>
|||>I agree, but I still wondering why this option is not reflected in the
>index properties, when right-click the index, then on properties.
Because I don't think it's a "permanent" property, it is merely describing
how it should initially be created. Once the index is created, they're
going to argue that whether it was created online or not last week is not a
permanent property of the index. It's kind of like driving a car off the
lot with premium fuel, and then later expecting the car to know it should
only accept premium fuel.
I agree somewhat with your sentiments that this information should be
preserved somehow, but it is more for the purposes of re-generation of
identical scripts, rather than any property I expect the index to maintain.
A
|||> permanent property of the index. It's kind of like driving a car off the
> lot with premium fuel, and then later expecting the car to know it should
> only accept premium fuel.
A better analogy would be expecting the car to somehow know that it at one
time used premium fuel.
A
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Hi Andrew
Thank you for your time.
We tried the following:
1. Manually creating the index and checking the Allow online processing of DML statements...
2. The following DDL statements:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[table1]') AND name = N'index1')DROP INDEX [index1] ON [dbo].[table1]
CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1]
([field1] ASC,[field2] ASC,[field3] ASC,[field4] ASC,[field5] ASC,[field6] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]
3. We tried the above statement with only ONLINE = ON option, to avoid possible conflicts with backward compatibility keywords (as stated in BOL).
4. We also tried ALTER INDEX ALL ON dbo.table1 REBUILD WITH (ONLINE = ON);
Database compatibility level is 90.
All of the above with the same result.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||We also have a clustered index on that table, we also tried to recreate that index with ONLINE = ON, BOL stating that if a clustered index is created with ONLINE = ON, all non-clustered indexes will be rebuilt with ONLINE = ON.
Same outcome.
Thx again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:etQdu%23C2HHA.5884@.TK2MSFTNGP02.phx.gbl...
Can you post the actual DDL (including indexes) and the ALTER INDEX statement you used?
Andrew J. Kelly SQL MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON. They are currently set to OFF. We tried everything that came across our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created if ONLINE set to ON. When scritping the indexes or right-clicking and then Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max), nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||I think there is a general bug somewhere... this is not the only option that
gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a while
ago, but at the time did not catch the online option as well. You've
spurred me to finally enter a bug report after retrieving my notes from back
then:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
Hi list
Did a search on all SQL related newsgroups, but didn't find anything.
So, here's my problem:
We are running SQL 2005 Enterprise edition.
SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
(Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
1)".
We want to rebuild our indexes on 2 tables with the ONLINE option set to ON.
They are currently set to OFF. We tried everything that came across our
minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename> REBUILD
WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
Apparently, the statement executes with no errors, but index is not created
if ONLINE set to ON. When scritping the indexes or right-clicking and then
Properties, they all appear with ONLINE = OFF.
We also tried to isolate the problem, by creating a fresh new database, a
fresh new table and then play around with indexes. Same outcome.
Also tested this on a developer edition, with same outcome.
Table doesn't have any LOB columns (text, ntext, image, varchar(max),
nvarchar(max)).
Any ideas, welcome.
Thanks in advance for any thoughts.
Regards,
Alin
|||Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
not sure if index is created with online = on, as it doesn't came up this
way when querying sys.indexes (with all the joins towards sys.index_columns
and sys.columns) or at least I didn't saw that option listed anywhere.
So my guess is that somehow that that option (along with many more, as you
also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
the index is created as stated in my statement. So I think that there is no
workaround available. One can only assume that the index is created as one
expected, but there is no way to get a confirmation.
I will also test on Katmai tomorrow and post my findings on connect.
Thanks for the heads up related to Katmai.
Regards,
Alin
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23z9%23CgD2HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I think there is a general bug somewhere... this is not the only option
>that gets forgotten (it also happens with SORT_IN_TEMPB). I noticed this a
>while ago, but at the time did not catch the online option as well. You've
>spurred me to finally enter a bug report after retrieving my notes from
>back then:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291288
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
> "Alin Selicean" <alin.nomail..selicean@.nospam.gmail.com> wrote in message
> news:%23f7250C2HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi list
> Did a search on all SQL related newsgroups, but didn't find anything.
> So, here's my problem:
> We are running SQL 2005 Enterprise edition.
> SELECT-ing @.@.VERSION returns "Microsoft SQL Server 2005 - 9.00.3042.00
> (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack
> 1)".
> We want to rebuild our indexes on 2 tables with the ONLINE option set to
> ON. They are currently set to OFF. We tried everything that came across
> our minds: DROP INDEX then CREATE INDEX, ALTER INDEX ALL ON <tablename>
> REBUILD WITH (ONLINE=ON), ALTER INDEX WITH DROP_EXISTING.
> Apparently, the statement executes with no errors, but index is not
> created if ONLINE set to ON. When scritping the indexes or right-clicking
> and then Properties, they all appear with ONLINE = OFF.
> We also tried to isolate the problem, by creating a fresh new database, a
> fresh new table and then play around with indexes. Same outcome.
> Also tested this on a developer edition, with same outcome.
> Table doesn't have any LOB columns (text, ntext, image, varchar(max),
> nvarchar(max)).
> Any ideas, welcome.
> Thanks in advance for any thoughts.
> Regards,
> Alin
>
|||I guess having those options during the CREATE INDEX statement only affect
the initial creation of the index, and do not represent any long-term
setting that "sticks."
My guess is that it will come back as "by design." E.g. you can't specify
up front that every time you change the index, those settings will be true.
If you want to rebuild the index and rebuild it online, you will need to
specify that in the ALTER INDEX command.
Then again, if I want to script the exact CREATE INDEX statement that I ran
yesterday, I can't see what harm it would have to maintain the properties
with the index as it was created, even if you will still need to specify
those options explicitly when using ALTER. Right now it doesn't seem
possible using the tools to re-generate the exact same CREATE INDEX script
that I ran yesterday...
Aaron Bertrand
SQL Server MVP
"Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
> Thank you, Aaron, for your feedback. Reviewing your post on connect, I am
> not sure if index is created with online = on, as it doesn't came up this
> way when querying sys.indexes (with all the joins towards
> sys.index_columns and sys.columns) or at least I didn't saw that option
> listed anywhere.
> So my guess is that somehow that that option (along with many more, as you
> also noticed) is not saved at all. Furthermore, I cannot be 100% sure that
> the index is created as stated in my statement. So I think that there is
> no workaround available. One can only assume that the index is created as
> one expected, but there is no way to get a confirmation.
> I will also test on Katmai tomorrow and post my findings on connect.
> Thanks for the heads up related to Katmai.
> Regards,
> Alin
|||I agree, but I still wondering why this option is not reflected in the index
properties, when right-click the index, then on properties.
My feeling is that this is a bug in the UI. Also, you cannot retrieve this
piece of information from any of the DMV's that shows any index options. Or
maybe it's just me
, I don't know. I will further test it tomorrow, but asof today, these are my conclusions (including your opinions on this).
I will come back with more news tomorrow.
Again, thanks for your efforts.
Regards,
Alin Selicean
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eELWJeE2HHA.5772@.TK2MSFTNGP02.phx.gbl...
>I guess having those options during the CREATE INDEX statement only affect
>the initial creation of the index, and do not represent any long-term
>setting that "sticks."
> My guess is that it will come back as "by design." E.g. you can't specify
> up front that every time you change the index, those settings will be
> true. If you want to rebuild the index and rebuild it online, you will
> need to specify that in the ALTER INDEX command.
> Then again, if I want to script the exact CREATE INDEX statement that I
> ran yesterday, I can't see what harm it would have to maintain the
> properties with the index as it was created, even if you will still need
> to specify those options explicitly when using ALTER. Right now it
> doesn't seem possible using the tools to re-generate the exact same CREATE
> INDEX script that I ran yesterday...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Alin Selicean" <alin.nomail.selicean@.nomail.gmail.com> wrote in message
> news:eZjKdUE2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>
|||>I agree, but I still wondering why this option is not reflected in the
>index properties, when right-click the index, then on properties.
Because I don't think it's a "permanent" property, it is merely describing
how it should initially be created. Once the index is created, they're
going to argue that whether it was created online or not last week is not a
permanent property of the index. It's kind of like driving a car off the
lot with premium fuel, and then later expecting the car to know it should
only accept premium fuel.
I agree somewhat with your sentiments that this information should be
preserved somehow, but it is more for the purposes of re-generation of
identical scripts, rather than any property I expect the index to maintain.
A
|||> permanent property of the index. It's kind of like driving a car off the
> lot with premium fuel, and then later expecting the car to know it should
> only accept premium fuel.
A better analogy would be expecting the car to somehow know that it at one
time used premium fuel.
A
Friday, February 17, 2012
Creating Diffent Account for replication
Hello there
I've tried relication many times before. It seems that it didn't always
worked ,because I still used System account instead of replacing it with
another account.
Now when i'm trying to give it another account, the agent become unuseable.
What i need to do to create another user account that i can use it by sql
server agent?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Roy,
set the sql server agent to use a domain account. Have this account in the
local adminsand in the sysadmin role on the publisher . The easiest method
is to use the same account with the same setup on both the
publisher/distributor and the Subscriber. If not, then refer to BOL for more
granular approaches
(http://msdn.microsoft.com/library/de...plsec_8dix.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Replication will never work under the system account - what exactly do you
mean by the agent has become unusable? Is it generating an error message?
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OYGqdp0$FHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I've tried relication many times before. It seems that it didn't always
> worked ,because I still used System account instead of replacing it with
> another account.
> Now when i'm trying to give it another account, the agent become
> unuseable.
> What i need to do to create another user account that i can use it by sql
> server agent?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary
After i changed the system account to my username as account, i couldn't run
the sql server agent again, until i return it to use the system account
The error i got: 1609 Sql server agent service could not run dut to logon
failure
How can i solve it?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Pp01C5$FHA.1676@.TK2MSFTNGP09.phx.gbl...
> Replication will never work under the system account - what exactly do you
> mean by the agent has become unusable? Is it generating an error message?
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:OYGqdp0$FHA.2652@.TK2MSFTNGP09.phx.gbl...
>
I've tried relication many times before. It seems that it didn't always
worked ,because I still used System account instead of replacing it with
another account.
Now when i'm trying to give it another account, the agent become unuseable.
What i need to do to create another user account that i can use it by sql
server agent?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Roy,
set the sql server agent to use a domain account. Have this account in the
local adminsand in the sysadmin role on the publisher . The easiest method
is to use the same account with the same setup on both the
publisher/distributor and the Subscriber. If not, then refer to BOL for more
granular approaches
(http://msdn.microsoft.com/library/de...plsec_8dix.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Replication will never work under the system account - what exactly do you
mean by the agent has become unusable? Is it generating an error message?
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OYGqdp0$FHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I've tried relication many times before. It seems that it didn't always
> worked ,because I still used System account instead of replacing it with
> another account.
> Now when i'm trying to give it another account, the agent become
> unuseable.
> What i need to do to create another user account that i can use it by sql
> server agent?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary
After i changed the system account to my username as account, i couldn't run
the sql server agent again, until i return it to use the system account
The error i got: 1609 Sql server agent service could not run dut to logon
failure
How can i solve it?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Pp01C5$FHA.1676@.TK2MSFTNGP09.phx.gbl...
> Replication will never work under the system account - what exactly do you
> mean by the agent has become unusable? Is it generating an error message?
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:OYGqdp0$FHA.2652@.TK2MSFTNGP09.phx.gbl...
>
Subscribe to:
Posts (Atom)