Reindexing

I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>

Reindexing

I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John
Have you any other indexes on the table? If so, drop them first and add
them back last.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John
|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>
|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>
|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>
|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>

Reindexing

I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>

reindex or update stat

Hi,
I have a database that will have couple thousands row inserted normally, but
there will be 3 or 4 days in a year that will have 20000 row inserted daily
. I set up the Maintenance Plan to have Optimizations to reorganize the data
and index, and it runs onc
e a week. During that busy week how can I imporve the performance? ThanksJen,
20,000 rows is really not all that many and will most likely not cause a
change in the way the queries are run. It's possible but not enough to get
excited about. The once a week will most likely be fine.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
> Hi,
> I have a database that will have couple thousands row inserted normally,
but there will be 3 or 4 days in a year that will have 20000 row inserted
daily. I set up the Maintenance Plan to have Optimizations to reorganize the
data and index, and it runs once a week. During that busy week how can I
imporve the performance? Thanks|||Thanks. In the past we did experience some problem in that few days. actuall
y it's about 60,000 in one table, and every record in this table will be dou
bled in another table, that is 120,000. How much will be the red line?
-- Andrew J. Kelly wrote: --
Jen,
20,000 rows is really not all that many and will most likely not cause a
change in the way the queries are run. It's possible but not enough to get
excited about. The once a week will most likely be fine.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
> Hi,
> I have a database that will have couple thousands row inserted normally,
but there will be 3 or 4 days in a year that will have 20000 row inserted
daily. I set up the Maintenance Plan to have Optimizations to reorganize the
data and index, and it runs once a week. During that busy week how can I
imporve the performance? Thanks|||That's imposable to tell without a breakdown of the data. Essentially if
the selectivity of the new data is about the same as the existing data it
will most likely not be any different. If it is drastically different there
may be some changes in the plans. But in either case if you have auto
update statistics turned on sql server should deal with it appropriately.
If not or you just don't want to leave it to chance you can simply issue an
update stats when your done with the import. If the data being imported is
going to cause a lot of page splits you may just want to reindex afterwards.
The number of rows is not the main deciding factor in if it will affect
performance. It's more the type of data, the schema and the way it is
imported.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:DF02E261-55FC-4AB1-A170-FE1B51128260@.microsoft.com...
> Thanks. In the past we did experience some problem in that few days.
actually it's about 60,000 in one table, and every record in this table will
be doubled in another table, that is 120,000. How much will be the red line?
> -- Andrew J. Kelly wrote: --
> Jen,
> 20,000 rows is really not all that many and will most likely not
cause a
> change in the way the queries are run. It's possible but not enough
to get
> excited about. The once a week will most likely be fine.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jen" <anonymous@.discussions.microsoft.com> wrote in message
> news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
normally,
> but there will be 3 or 4 days in a year that will have 20000 row
inserted
> daily. I set up the Maintenance Plan to have Optimizations to
reorganize the
> data and index, and it runs once a week. During that busy week how
can I
> imporve the performance? Thanks
>
>

reindex or update stat

Hi
I have a database that will have couple thousands row inserted normally, but there will be 3 or 4 days in a year that will have 20000 row inserted daily. I set up the Maintenance Plan to have Optimizations to reorganize the data and index, and it runs once a week. During that busy week how can I imporve the performance? ThanksJen,
20,000 rows is really not all that many and will most likely not cause a
change in the way the queries are run. It's possible but not enough to get
excited about. The once a week will most likely be fine.
--
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
> Hi,
> I have a database that will have couple thousands row inserted normally,
but there will be 3 or 4 days in a year that will have 20000 row inserted
daily. I set up the Maintenance Plan to have Optimizations to reorganize the
data and index, and it runs once a week. During that busy week how can I
imporve the performance? Thanks|||Thanks. In the past we did experience some problem in that few days. actually it's about 60,000 in one table, and every record in this table will be doubled in another table, that is 120,000. How much will be the red line?
-- Andrew J. Kelly wrote: --
Jen
20,000 rows is really not all that many and will most likely not cause
change in the way the queries are run. It's possible but not enough to ge
excited about. The once a week will most likely be fine
--
Andrew J. Kell
SQL Server MV
"Jen" <anonymous@.discussions.microsoft.com> wrote in messag
news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com..
> Hi
> I have a database that will have couple thousands row inserted normally
but there will be 3 or 4 days in a year that will have 20000 row inserte
daily. I set up the Maintenance Plan to have Optimizations to reorganize th
data and index, and it runs once a week. During that busy week how can
imporve the performance? Thank|||That's imposable to tell without a breakdown of the data. Essentially if
the selectivity of the new data is about the same as the existing data it
will most likely not be any different. If it is drastically different there
may be some changes in the plans. But in either case if you have auto
update statistics turned on sql server should deal with it appropriately.
If not or you just don't want to leave it to chance you can simply issue an
update stats when your done with the import. If the data being imported is
going to cause a lot of page splits you may just want to reindex afterwards.
The number of rows is not the main deciding factor in if it will affect
performance. It's more the type of data, the schema and the way it is
imported.
--
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:DF02E261-55FC-4AB1-A170-FE1B51128260@.microsoft.com...
> Thanks. In the past we did experience some problem in that few days.
actually it's about 60,000 in one table, and every record in this table will
be doubled in another table, that is 120,000. How much will be the red line?
> -- Andrew J. Kelly wrote: --
> Jen,
> 20,000 rows is really not all that many and will most likely not
cause a
> change in the way the queries are run. It's possible but not enough
to get
> excited about. The once a week will most likely be fine.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jen" <anonymous@.discussions.microsoft.com> wrote in message
> news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
> > Hi,
> > I have a database that will have couple thousands row inserted
normally,
> but there will be 3 or 4 days in a year that will have 20000 row
inserted
> daily. I set up the Maintenance Plan to have Optimizations to
reorganize the
> data and index, and it runs once a week. During that busy week how
can I
> imporve the performance? Thanks
>
>

registering SQL Server not in domain

Hi all,
We have standardized on Hyperion as our reporting tool. So far I have
only set up a couple of Access databases as data sources for it. Now
there is a request to report off our eOn (telephony management) SQL
Server database using Hyperion. The eOn SQL Server box is in a
workgroup that is not part of the rest of our domain. (We only have
one domain because we don't have a "forest", whatever that means.) It
is behind a router owned by eOn along with a PBX and some other stuff.
Setting up a data source for Hyperion requires creating a special data
source file called an .oce on the box where the Hyperion fat client
(required for most administrative tasks) resides, and also setting up a
different special data source file called a .das on the server where
the Hyperion services run. (The analysts and end-users do not have the
fat client, their access is web-based.)
I have to register the eOn SQL Server by using the IP address and SQL
Server authentication. (I was told that I can't use Windows
authentication because it is not in the domain.) From the box on
which the Hyperion fat client resides, I cannot register the eOn SQL
Server. The error message is "timeout expired". Tracerting indicates
there are no intermediate hops when attempting to connect from this
VLAN. From my desktop, which is on a different VLAN, I can connect to
and register it. This trip includes one hop at our 6509. From one of
my servers which is on the same VLAN as the fat-client box, I am able
to connect and register. On the fat-client box I tried deleting and
re-registering another SQL Server and there was no problem.
The IP address I have to use to connect to the eOn SQL Server is *NOT*
the actual IP address of the box it resides on, but rather the eOn
router, which translates it to the address of the server. We have no
control over this, eOn creates this setup. I'm not sure how it knows
which of the devices behind it a given message is for.
Ideas?Hi
Which version of SQL Server is on the telephony management system? At a
guess it is MSDE with no network protocols installed! Use svrnetcn.exe to
enable the network protcols. If they are running you may want to use the
SQLRecon tool to see what servers are available on your network
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
John
"Ellen K" wrote:
> Hi all,
> We have standardized on Hyperion as our reporting tool. So far I have
> only set up a couple of Access databases as data sources for it. Now
> there is a request to report off our eOn (telephony management) SQL
> Server database using Hyperion. The eOn SQL Server box is in a
> workgroup that is not part of the rest of our domain. (We only have
> one domain because we don't have a "forest", whatever that means.) It
> is behind a router owned by eOn along with a PBX and some other stuff.
>
> Setting up a data source for Hyperion requires creating a special data
> source file called an .oce on the box where the Hyperion fat client
> (required for most administrative tasks) resides, and also setting up a
> different special data source file called a .das on the server where
> the Hyperion services run. (The analysts and end-users do not have the
> fat client, their access is web-based.)
> I have to register the eOn SQL Server by using the IP address and SQL
> Server authentication. (I was told that I can't use Windows
> authentication because it is not in the domain.) From the box on
> which the Hyperion fat client resides, I cannot register the eOn SQL
> Server. The error message is "timeout expired". Tracerting indicates
> there are no intermediate hops when attempting to connect from this
> VLAN. From my desktop, which is on a different VLAN, I can connect to
> and register it. This trip includes one hop at our 6509. From one of
> my servers which is on the same VLAN as the fat-client box, I am able
> to connect and register. On the fat-client box I tried deleting and
> re-registering another SQL Server and there was no problem.
> The IP address I have to use to connect to the eOn SQL Server is *NOT*
> the actual IP address of the box it resides on, but rather the eOn
> router, which translates it to the address of the server. We have no
> control over this, eOn creates this setup. I'm not sure how it knows
> which of the devices behind it a given message is for.
> Ideas?
>|||Hi John,
Thanks for your response. I will check the version and also check
which network protocols are enabled on it, assuming I can get to the
box. But if the problem were no network protocols installed on the eOn
box then how would the other two boxes that CAN register it be able to
do so?
Thanks,
Ellen|||Hi
I hadn't realised that it could be registered elsewhere! This indicates that
there are network protocols, but they still may not be the same as the ones
on the client that can't register!! You may still want to try running
SQLRecon and also try to telnet into the port being used.
John
"Ellen K" wrote:
> Hi John,
> Thanks for your response. I will check the version and also check
> which network protocols are enabled on it, assuming I can get to the
> box. But if the problem were no network protocols installed on the eOn
> box then how would the other two boxes that CAN register it be able to
> do so?
> Thanks,
> Ellen
>|||Hi John,
Yes, I mentioned in my original post that I could register it from both
my desktop and from one of "my" servers.
The network protocols are the same on the eOn box as on "my" servers:
named pipes and tcp. Since the fat client can connect to "my" servers,
I think it's safe to say this isn't the problem.
I have now succeeded in creating an ODBC connection, which would not
have been my first choice, but right now if it works I'm happy.
Thanks,
Ellen

registering SQL Server not in domain

Hi all,
We have standardized on Hyperion as our reporting tool. So far I have
only set up a couple of Access databases as data sources for it. Now
there is a request to report off our eOn (telephony management) SQL
Server database using Hyperion. The eOn SQL Server box is in a
workgroup that is not part of the rest of our domain. (We only have
one domain because we don't have a "forest", whatever that means.) It
is behind a router owned by eOn along with a PBX and some other stuff.
Setting up a data source for Hyperion requires creating a special data
source file called an .oce on the box where the Hyperion fat client
(required for most administrative tasks) resides, and also setting up a
different special data source file called a .das on the server where
the Hyperion services run. (The analysts and end-users do not have the
fat client, their access is web-based.)
I have to register the eOn SQL Server by using the IP address and SQL
Server authentication. (I was told that I can't use Windows
authentication because it is not in the domain.) From the box on
which the Hyperion fat client resides, I cannot register the eOn SQL
Server. The error message is "timeout expired". Tracerting indicates
there are no intermediate hops when attempting to connect from this
VLAN. From my desktop, which is on a different VLAN, I can connect to
and register it. This trip includes one hop at our 6509. From one of
my servers which is on the same VLAN as the fat-client box, I am able
to connect and register. On the fat-client box I tried deleting and
re-registering another SQL Server and there was no problem.
The IP address I have to use to connect to the eOn SQL Server is *NOT*
the actual IP address of the box it resides on, but rather the eOn
router, which translates it to the address of the server. We have no
control over this, eOn creates this setup. I'm not sure how it knows
which of the devices behind it a given message is for.
Ideas?
Hi
Which version of SQL Server is on the telephony management system? At a
guess it is MSDE with no network protocols installed! Use svrnetcn.exe to
enable the network protcols. If they are running you may want to use the
SQLRecon tool to see what servers are available on your network
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
John
"Ellen K" wrote:

> Hi all,
> We have standardized on Hyperion as our reporting tool. So far I have
> only set up a couple of Access databases as data sources for it. Now
> there is a request to report off our eOn (telephony management) SQL
> Server database using Hyperion. The eOn SQL Server box is in a
> workgroup that is not part of the rest of our domain. (We only have
> one domain because we don't have a "forest", whatever that means.) It
> is behind a router owned by eOn along with a PBX and some other stuff.
>
> Setting up a data source for Hyperion requires creating a special data
> source file called an .oce on the box where the Hyperion fat client
> (required for most administrative tasks) resides, and also setting up a
> different special data source file called a .das on the server where
> the Hyperion services run. (The analysts and end-users do not have the
> fat client, their access is web-based.)
> I have to register the eOn SQL Server by using the IP address and SQL
> Server authentication. (I was told that I can't use Windows
> authentication because it is not in the domain.) From the box on
> which the Hyperion fat client resides, I cannot register the eOn SQL
> Server. The error message is "timeout expired". Tracerting indicates
> there are no intermediate hops when attempting to connect from this
> VLAN. From my desktop, which is on a different VLAN, I can connect to
> and register it. This trip includes one hop at our 6509. From one of
> my servers which is on the same VLAN as the fat-client box, I am able
> to connect and register. On the fat-client box I tried deleting and
> re-registering another SQL Server and there was no problem.
> The IP address I have to use to connect to the eOn SQL Server is *NOT*
> the actual IP address of the box it resides on, but rather the eOn
> router, which translates it to the address of the server. We have no
> control over this, eOn creates this setup. I'm not sure how it knows
> which of the devices behind it a given message is for.
> Ideas?
>
|||Hi John,
Thanks for your response. I will check the version and also check
which network protocols are enabled on it, assuming I can get to the
box. But if the problem were no network protocols installed on the eOn
box then how would the other two boxes that CAN register it be able to
do so?
Thanks,
Ellen
|||Hi
I hadn't realised that it could be registered elsewhere! This indicates that
there are network protocols, but they still may not be the same as the ones
on the client that can't register!! You may still want to try running
SQLRecon and also try to telnet into the port being used.
John
"Ellen K" wrote:

> Hi John,
> Thanks for your response. I will check the version and also check
> which network protocols are enabled on it, assuming I can get to the
> box. But if the problem were no network protocols installed on the eOn
> box then how would the other two boxes that CAN register it be able to
> do so?
> Thanks,
> Ellen
>
|||Hi John,
Yes, I mentioned in my original post that I could register it from both
my desktop and from one of "my" servers.
The network protocols are the same on the eOn box as on "my" servers:
named pipes and tcp. Since the fat client can connect to "my" servers,
I think it's safe to say this isn't the problem.
I have now succeeded in creating an ODBC connection, which would not
have been my first choice, but right now if it works I'm happy.
Thanks,
Ellen

registering SQL Server not in domain

Hi all,

We have standardized on Hyperion as our reporting tool. So far I have
only set up a couple of Access databases as data sources for it. Now
there is a request to report off our eOn (telephony management) SQL
Server database using Hyperion. The eOn SQL Server box is in a
workgroup that is not part of the rest of our domain. (We only have
one domain because we don't have a "forest", whatever that means.) It
is behind a router owned by eOn along with a PBX and some other stuff.

Setting up a data source for Hyperion requires creating a special data
source file called an .oce on the box where the Hyperion fat client
(required for most administrative tasks) resides, and also setting up a
different special data source file called a .das on the server where
the Hyperion services run. (The analysts and end-users do not have the
fat client, their access is web-based.)

I have to register the eOn SQL Server by using the IP address and SQL
Server authentication. (I was told that I can't use Windows
authentication because it is not in the domain.) From the box on
which the Hyperion fat client resides, I cannot register the eOn SQL
Server. The error message is "timeout expired". Tracerting indicates
there are no intermediate hops when attempting to connect from this
VLAN. From my desktop, which is on a different VLAN, I can connect to
and register it. This trip includes one hop at our 6509. From one of
my servers which is on the same VLAN as the fat-client box, I am able
to connect and register. On the fat-client box I tried deleting and
re-registering another SQL Server and there was no problem.

The IP address I have to use to connect to the eOn SQL Server is *NOT*
the actual IP address of the box it resides on, but rather the eOn
router, which translates it to the address of the server. We have no
control over this, eOn creates this setup. I'm not sure how it knows
which of the devices behind it a given message is for.

Ideas?From your description I pulled this sentence.

"From my desktop, which is on a different VLAN, I can connect to
and register it."

This indicates that you have a valid SQL login, password, protocol, and ip
address. Analyse you desktop and figure out what these are. In particular
the protocol.
The protocol, Network routing, and possibly the firewall will be the issue.

The two most common protocols for SQL is Named Pipes and TCP.

If you can get to the SQL Server from your desktop, look at the SQL Server
Log for what protocols are supported.
From the Hyperion services server use this information to configure an ODBC
DSN and test the connection. You may want to install the SQL client only
option on your Hyperion server to give you more control over the connection
setup (AKA a SQL Alias)

"Ellen K" <ekaye2002@.yahoo.com> wrote in message
news:1129150328.960539.76600@.g47g2000cwa.googlegro ups.com...
> Hi all,
> We have standardized on Hyperion as our reporting tool. So far I have
> only set up a couple of Access databases as data sources for it. Now
> there is a request to report off our eOn (telephony management) SQL
> Server database using Hyperion. The eOn SQL Server box is in a
> workgroup that is not part of the rest of our domain. (We only have
> one domain because we don't have a "forest", whatever that means.) It
> is behind a router owned by eOn along with a PBX and some other stuff.
>
> Setting up a data source for Hyperion requires creating a special data
> source file called an .oce on the box where the Hyperion fat client
> (required for most administrative tasks) resides, and also setting up a
> different special data source file called a .das on the server where
> the Hyperion services run. (The analysts and end-users do not have the
> fat client, their access is web-based.)
> I have to register the eOn SQL Server by using the IP address and SQL
> Server authentication. (I was told that I can't use Windows
> authentication because it is not in the domain.) From the box on
> which the Hyperion fat client resides, I cannot register the eOn SQL
> Server. The error message is "timeout expired". Tracerting indicates
> there are no intermediate hops when attempting to connect from this
> VLAN. From my desktop, which is on a different VLAN, I can connect to
> and register it. This trip includes one hop at our 6509. From one of
> my servers which is on the same VLAN as the fat-client box, I am able
> to connect and register. On the fat-client box I tried deleting and
> re-registering another SQL Server and there was no problem.
> The IP address I have to use to connect to the eOn SQL Server is *NOT*
> the actual IP address of the box it resides on, but rather the eOn
> router, which translates it to the address of the server. We have no
> control over this, eOn creates this setup. I'm not sure how it knows
> which of the devices behind it a given message is for.
> Ideas?|||Hi,

Thanks very much for your response. :)

I think network routing has been eliminated as the cause because I was
able to register the eOn server from one of "my" SQL Servers which is
on the same VLAN as the fat client box that can't register it. And it
can't be a firewall issue because the eOn box is behind our firewall,
it's just not in our domain. I did not think to check the protocols,
that is definitely worth a try. It did occur to me to set up the eOn
database as an ODBC connection for Hyperion instead of using the SQL
Server option, which if it works would solve the current problem.
However, I am also concerned because I am meanwhile building a data
warehouse in SQL Server for which Hyperion is supposed to be the
reporting tool and I would hate to have make a choice that I know up
front is going to diminish performance.

Thanks again, I will report back.

Ellen|||Hi again,

Well, it's not the network protocols.

I did notice that the eOn box is running the original version of SQL
Server, i.e. no service packs have been applied. I will take care of
that, but would be surprised if it's causing the problem.

Meanwhile I did succeed in setting up an ODBC connection, so I've
solved my immediate problem.

Thanks again,

Ellen

registering SQL Server not in domain

Hi all,
We have standardized on Hyperion as our reporting tool. So far I have
only set up a couple of Access databases as data sources for it. Now
there is a request to report off our eOn (telephony management) SQL
Server database using Hyperion. The eOn SQL Server box is in a
workgroup that is not part of the rest of our domain. (We only have
one domain because we don't have a "forest", whatever that means.) It
is behind a router owned by eOn along with a PBX and some other stuff.
Setting up a data source for Hyperion requires creating a special data
source file called an .oce on the box where the Hyperion fat client
(required for most administrative tasks) resides, and also setting up a
different special data source file called a .das on the server where
the Hyperion services run. (The analysts and end-users do not have the
fat client, their access is web-based.)
I have to register the eOn SQL Server by using the IP address and SQL
Server authentication. (I was told that I can't use Windows
authentication because it is not in the domain.) From the box on
which the Hyperion fat client resides, I cannot register the eOn SQL
Server. The error message is "timeout expired". Tracerting indicates
there are no intermediate hops when attempting to connect from this
VLAN. From my desktop, which is on a different VLAN, I can connect to
and register it. This trip includes one hop at our 6509. From one of
my servers which is on the same VLAN as the fat-client box, I am able
to connect and register. On the fat-client box I tried deleting and
re-registering another SQL Server and there was no problem.
The IP address I have to use to connect to the eOn SQL Server is *NOT*
the actual IP address of the box it resides on, but rather the eOn
router, which translates it to the address of the server. We have no
control over this, eOn creates this setup. I'm not sure how it knows
which of the devices behind it a given message is for.
Ideas?Hi
Which version of SQL Server is on the telephony management system? At a
guess it is MSDE with no network protocols installed! Use svrnetcn.exe to
enable the network protcols. If they are running you may want to use the
SQLRecon tool to see what servers are available on your network
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
John
"Ellen K" wrote:

> Hi all,
> We have standardized on Hyperion as our reporting tool. So far I have
> only set up a couple of Access databases as data sources for it. Now
> there is a request to report off our eOn (telephony management) SQL
> Server database using Hyperion. The eOn SQL Server box is in a
> workgroup that is not part of the rest of our domain. (We only have
> one domain because we don't have a "forest", whatever that means.) It
> is behind a router owned by eOn along with a PBX and some other stuff.
>
> Setting up a data source for Hyperion requires creating a special data
> source file called an .oce on the box where the Hyperion fat client
> (required for most administrative tasks) resides, and also setting up a
> different special data source file called a .das on the server where
> the Hyperion services run. (The analysts and end-users do not have the
> fat client, their access is web-based.)
> I have to register the eOn SQL Server by using the IP address and SQL
> Server authentication. (I was told that I can't use Windows
> authentication because it is not in the domain.) From the box on
> which the Hyperion fat client resides, I cannot register the eOn SQL
> Server. The error message is "timeout expired". Tracerting indicates
> there are no intermediate hops when attempting to connect from this
> VLAN. From my desktop, which is on a different VLAN, I can connect to
> and register it. This trip includes one hop at our 6509. From one of
> my servers which is on the same VLAN as the fat-client box, I am able
> to connect and register. On the fat-client box I tried deleting and
> re-registering another SQL Server and there was no problem.
> The IP address I have to use to connect to the eOn SQL Server is *NOT*
> the actual IP address of the box it resides on, but rather the eOn
> router, which translates it to the address of the server. We have no
> control over this, eOn creates this setup. I'm not sure how it knows
> which of the devices behind it a given message is for.
> Ideas?
>|||Hi John,
Thanks for your response. I will check the version and also check
which network protocols are enabled on it, assuming I can get to the
box. But if the problem were no network protocols installed on the eOn
box then how would the other two boxes that CAN register it be able to
do so?
Thanks,
Ellen|||Hi
I hadn't realised that it could be registered elsewhere! This indicates that
there are network protocols, but they still may not be the same as the ones
on the client that can't register!! You may still want to try running
SQLRecon and also try to telnet into the port being used.
John
"Ellen K" wrote:

> Hi John,
> Thanks for your response. I will check the version and also check
> which network protocols are enabled on it, assuming I can get to the
> box. But if the problem were no network protocols installed on the eOn
> box then how would the other two boxes that CAN register it be able to
> do so?
> Thanks,
> Ellen
>|||Hi John,
Yes, I mentioned in my original post that I could register it from both
my desktop and from one of "my" servers.
The network protocols are the same on the eOn box as on "my" servers:
named pipes and tcp. Since the fat client can connect to "my" servers,
I think it's safe to say this isn't the problem.
I have now succeeded in creating an ODBC connection, which would not
have been my first choice, but right now if it works I'm happy.
Thanks,
Ellen

Registering SQL Server in Enterprise Manager

I have a couple of users that have been using the SQL Server 2000 Enterprise
Manager. They had our production server (as well as a test server)
registered on their machines as valid servers. Yesterday these users started
experiencing a problem with accessing the production server via Enterprise
Manager. When they tried to access the server they would receive the message
"Cannot open user default database. Login Failed". Each of these users is in
an AD group (defined as a user in SQL Server) that has access to our
production database. I verified the access of the group and everything looks
fine. The default database exists and the group is defined as a user. None
of these users are owners of objects in their default database.
The only way I have found to fix this was to go through security on our
production server and add their actual network user name. The whole reason I
have the group set up is so I don't have to add users individually. I also
verified the setup of our test server. This also uses a group instead of
individual network sign ons. They aren't having any problems accessing the
test server from Enterprise Manager.
How do I fix this so I don't have to have individual network sign ons?
Thanks for any help.
Nancy
That sounds pretty strange. Did you try deleting the group's login in SQL
and re-creating it?
-Argenis
"Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
news:ONbr4QOIFHA.3628@.TK2MSFTNGP15.phx.gbl...
> I have a couple of users that have been using the SQL Server 2000
Enterprise
> Manager. They had our production server (as well as a test server)
> registered on their machines as valid servers. Yesterday these users
started
> experiencing a problem with accessing the production server via Enterprise
> Manager. When they tried to access the server they would receive the
message
> "Cannot open user default database. Login Failed". Each of these users is
in
> an AD group (defined as a user in SQL Server) that has access to our
> production database. I verified the access of the group and everything
looks
> fine. The default database exists and the group is defined as a user. None
> of these users are owners of objects in their default database.
> The only way I have found to fix this was to go through security on our
> production server and add their actual network user name. The whole reason
I
> have the group set up is so I don't have to add users individually. I also
> verified the setup of our test server. This also uses a group instead of
> individual network sign ons. They aren't having any problems accessing the
> test server from Enterprise Manager.
> How do I fix this so I don't have to have individual network sign ons?
> Thanks for any help.
> Nancy
>
|||I thought about doing that but I hadn't yet because I didn't want to have to
recreate the security for the tables in the database. One thing I did do
though was added a new AD group with these users and then added that to the
SQL Server security. This still didn't fix the problem. I'm thinking the
only way to fix it may be to try deleting the original AD group from SQL
Server and then re-adding it.
Thanks.
Nancy
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ewrCUZQIFHA.1396@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> That sounds pretty strange. Did you try deleting the group's login in SQL
> and re-creating it?
> -Argenis
> "Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
> news:ONbr4QOIFHA.3628@.TK2MSFTNGP15.phx.gbl...
> Enterprise
> started
Enterprise[vbcol=seagreen]
> message
is[vbcol=seagreen]
> in
> looks
None[vbcol=seagreen]
reason[vbcol=seagreen]
> I
also[vbcol=seagreen]
the
>

Registering SQL Server in Enterprise Manager

I have a couple of users that have been using the SQL Server 2000 Enterprise
Manager. They had our production server (as well as a test server)
registered on their machines as valid servers. Yesterday these users started
experiencing a problem with accessing the production server via Enterprise
Manager. When they tried to access the server they would receive the message
"Cannot open user default database. Login Failed". Each of these users is in
an AD group (defined as a user in SQL Server) that has access to our
production database. I verified the access of the group and everything looks
fine. The default database exists and the group is defined as a user. None
of these users are owners of objects in their default database.
The only way I have found to fix this was to go through security on our
production server and add their actual network user name. The whole reason I
have the group set up is so I don't have to add users individually. I also
verified the setup of our test server. This also uses a group instead of
individual network sign ons. They aren't having any problems accessing the
test server from Enterprise Manager.
How do I fix this so I don't have to have individual network sign ons?
Thanks for any help.
NancyThat sounds pretty strange. Did you try deleting the group's login in SQL
and re-creating it?
-Argenis
"Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
news:ONbr4QOIFHA.3628@.TK2MSFTNGP15.phx.gbl...
> I have a couple of users that have been using the SQL Server 2000
Enterprise
> Manager. They had our production server (as well as a test server)
> registered on their machines as valid servers. Yesterday these users
started
> experiencing a problem with accessing the production server via Enterprise
> Manager. When they tried to access the server they would receive the
message
> "Cannot open user default database. Login Failed". Each of these users is
in
> an AD group (defined as a user in SQL Server) that has access to our
> production database. I verified the access of the group and everything
looks
> fine. The default database exists and the group is defined as a user. None
> of these users are owners of objects in their default database.
> The only way I have found to fix this was to go through security on our
> production server and add their actual network user name. The whole reason
I
> have the group set up is so I don't have to add users individually. I also
> verified the setup of our test server. This also uses a group instead of
> individual network sign ons. They aren't having any problems accessing the
> test server from Enterprise Manager.
> How do I fix this so I don't have to have individual network sign ons?
> Thanks for any help.
> Nancy
>|||I thought about doing that but I hadn't yet because I didn't want to have to
recreate the security for the tables in the database. One thing I did do
though was added a new AD group with these users and then added that to the
SQL Server security. This still didn't fix the problem. I'm thinking the
only way to fix it may be to try deleting the original AD group from SQL
Server and then re-adding it.
Thanks.
Nancy
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ewrCUZQIFHA.1396@.TK2MSFTNGP10.phx.gbl...
> That sounds pretty strange. Did you try deleting the group's login in SQL
> and re-creating it?
> -Argenis
> "Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
> news:ONbr4QOIFHA.3628@.TK2MSFTNGP15.phx.gbl...
> Enterprise
> started
Enterprise[vbcol=seagreen]
> message
is[vbcol=seagreen]
> in
> looks
None[vbcol=seagreen]
reason[vbcol=seagreen]
> I
also[vbcol=seagreen]
the[vbcol=seagreen]
>

Registering SQL Server in Enterprise Manager

I have a couple of users that have been using the SQL Server 2000 Enterprise
Manager. They had our production server (as well as a test server)
registered on their machines as valid servers. Yesterday these users started
experiencing a problem with accessing the production server via Enterprise
Manager. When they tried to access the server they would receive the message
"Cannot open user default database. Login Failed". Each of these users is in
an AD group (defined as a user in SQL Server) that has access to our
production database. I verified the access of the group and everything looks
fine. The default database exists and the group is defined as a user. None
of these users are owners of objects in their default database.
The only way I have found to fix this was to go through security on our
production server and add their actual network user name. The whole reason I
have the group set up is so I don't have to add users individually. I also
verified the setup of our test server. This also uses a group instead of
individual network sign ons. They aren't having any problems accessing the
test server from Enterprise Manager.
How do I fix this so I don't have to have individual network sign ons?
Thanks for any help.
NancyThat sounds pretty strange. Did you try deleting the group's login in SQL
and re-creating it?
-Argenis
"Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
news:ONbr4QOIFHA.3628@.TK2MSFTNGP15.phx.gbl...
> I have a couple of users that have been using the SQL Server 2000
Enterprise
> Manager. They had our production server (as well as a test server)
> registered on their machines as valid servers. Yesterday these users
started
> experiencing a problem with accessing the production server via Enterprise
> Manager. When they tried to access the server they would receive the
message
> "Cannot open user default database. Login Failed". Each of these users is
in
> an AD group (defined as a user in SQL Server) that has access to our
> production database. I verified the access of the group and everything
looks
> fine. The default database exists and the group is defined as a user. None
> of these users are owners of objects in their default database.
> The only way I have found to fix this was to go through security on our
> production server and add their actual network user name. The whole reason
I
> have the group set up is so I don't have to add users individually. I also
> verified the setup of our test server. This also uses a group instead of
> individual network sign ons. They aren't having any problems accessing the
> test server from Enterprise Manager.
> How do I fix this so I don't have to have individual network sign ons?
> Thanks for any help.
> Nancy
>|||I thought about doing that but I hadn't yet because I didn't want to have to
recreate the security for the tables in the database. One thing I did do
though was added a new AD group with these users and then added that to the
SQL Server security. This still didn't fix the problem. I'm thinking the
only way to fix it may be to try deleting the original AD group from SQL
Server and then re-adding it.
Thanks.
Nancy
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ewrCUZQIFHA.1396@.TK2MSFTNGP10.phx.gbl...
> That sounds pretty strange. Did you try deleting the group's login in SQL
> and re-creating it?
> -Argenis
> "Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
> news:ONbr4QOIFHA.3628@.TK2MSFTNGP15.phx.gbl...
> > I have a couple of users that have been using the SQL Server 2000
> Enterprise
> > Manager. They had our production server (as well as a test server)
> > registered on their machines as valid servers. Yesterday these users
> started
> > experiencing a problem with accessing the production server via
Enterprise
> > Manager. When they tried to access the server they would receive the
> message
> > "Cannot open user default database. Login Failed". Each of these users
is
> in
> > an AD group (defined as a user in SQL Server) that has access to our
> > production database. I verified the access of the group and everything
> looks
> > fine. The default database exists and the group is defined as a user.
None
> > of these users are owners of objects in their default database.
> >
> > The only way I have found to fix this was to go through security on our
> > production server and add their actual network user name. The whole
reason
> I
> > have the group set up is so I don't have to add users individually. I
also
> > verified the setup of our test server. This also uses a group instead of
> > individual network sign ons. They aren't having any problems accessing
the
> > test server from Enterprise Manager.
> >
> > How do I fix this so I don't have to have individual network sign ons?
> >
> > Thanks for any help.
> >
> > Nancy
> >
> >
>

registering new sql server

Hello,
I have SQL server developer version running on my local workstation.
I recently got a new Win 2003 Box.
Couple of things.
1) IT dept gave me 35 gigs are raid 5. Is this enough for a large DB?
It seems awfully small to me.
2) The SQL server is running all well. In my local Enterprise Manger.
I tried to register a sql server. But I can't get a connect() back.
What could be causing this? I can ping the SQL box because it's on the
same subnet.
Won Lee> 1) IT dept gave me 35 gigs are raid 5. Is this enough for a large DB?
> It seems awfully small to me.
Depends on what you consider a large db. We've got a sql database just over
1 million records spread across 100 tables with that many views and stored
procedures besides, and that's 2gb.
> 2) The SQL server is running all well. In my local Enterprise Manger.
> I tried to register a sql server. But I can't get a connect() back.
> What could be causing this? I can ping the SQL box because it's on the
> same subnet.
Did you set up an alias in the Client Network Utility?|||> 1) IT dept gave me 35 gigs are raid 5. Is this enough for a large DB?
> It seems awfully small to me.
Only you can tell us how large your database is going to be. You need to
research your business requirements and determine size and growth from that.
There's no such thing as a "typical" size for a database.
> I tried to register a sql server. But I can't get a connect() back.
Can you give some more detail. Do you get an error message? Can you verify
in Service Manager that the server is running? Did you supply a valid user
name and password?
This might also help:
http://support.microsoft.com/?id=328306
David Portas
--
Please reply only to the newsgroup
--|||William Morris wrote:
>>1) IT dept gave me 35 gigs are raid 5. Is this enough for a large DB?
>>It seems awfully small to me.
>
> Depends on what you consider a large db. We've got a sql database just over
> 1 million records spread across 100 tables with that many views and stored
> procedures besides, and that's 2gb.
>
Thanks. I looked at the old DB and realized that we only grew at a 3
meg per month rate. I guess the DB is good for awhile.
>>2) The SQL server is running all well. In my local Enterprise Manger.
>>I tried to register a sql server. But I can't get a connect() back.
>>What could be causing this? I can ping the SQL box because it's on the
>>same subnet.
>
> Did you set up an alias in the Client Network Utility?
I didn't do anything. My IT guys set up the box. The error message I'm
getting is, WINBOX-SQL server does not exist or access denied.
I created a user as a DBO owner. I also tried with the SA login
credentials. I can remote desktop into the server and login to the SQL
server through Enterprise Manager and Query Analyzer.
I have no idea what Client Network Utility is so I looked it up in Books
Online. I will give that a try.
BTW How do I find out if they installed the Developer version or the
client/server version of SQL Server 2000?|||> Can you give some more detail. Do you get an error message? Can you verify
> in Service Manager that the server is running? Did you supply a valid user
> name and password?
> This might also help:
> http://support.microsoft.com/?id=328306
>
David,
Yes the service is started. I did created a user and also used the SA
password to login locally.
Thanks for the KB article I will look through it.|||> This might also help:
> http://support.microsoft.com/?id=328306
>
Just in case anyone is insterested.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;306199
A link from the KB article David posted fixed the problem. Funny thing
is that the server is not on DHCP. It was when we first popped it in to
the rack but promptly changed it to a fixed IP address. Kinda funny.
But at least it works now.|||Thanks to William and David for helping me out.
Won Lee