Enterprise manager taking up to 10 minutes to save design changes

Hi,
I am running SQL 2000 with an 8 GB database. I have two relatively large
tables (each with about 2 million records in them), and I occasionally need
to make changes to the table design (add a column, increase the size of an
existing field, etc.) on these two tables through the Enterprise Manager.
However, saving these changes can take betwen 5-10 minutes to complete.
If I make changes to any of my other smaller tables, they are saved
instantly. Is it really supposed to take this long for larger tables to be
updated?
Any suggestions would be greatly appreciated.
Never make a change to a production db with EM. Most of the time it will
create a temp table, copy all the data over from the original, drop the
original and then rename the temp table to the original name. Most of the
time a simple ALTER TABLE is all you need to issue. If it is something
simple like increasing a varchar column it might just be a meta data change.
Andrew J. Kelly SQL MVP
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:BACB0B6C-FDA2-439A-BFA5-A5672AE7AE21@.microsoft.com...
> Hi,
> I am running SQL 2000 with an 8 GB database. I have two relatively large
> tables (each with about 2 million records in them), and I occasionally
> need
> to make changes to the table design (add a column, increase the size of an
> existing field, etc.) on these two tables through the Enterprise Manager.
> However, saving these changes can take betwen 5-10 minutes to complete.
> If I make changes to any of my other smaller tables, they are saved
> instantly. Is it really supposed to take this long for larger tables to
> be
> updated?
> Any suggestions would be greatly appreciated.

Enterprise manager taking up to 10 minutes to save design changes

Hi,
I am running SQL 2000 with an 8 GB database. I have two relatively large
tables (each with about 2 million records in them), and I occasionally need
to make changes to the table design (add a column, increase the size of an
existing field, etc.) on these two tables through the Enterprise Manager.
However, saving these changes can take betwen 5-10 minutes to complete.
If I make changes to any of my other smaller tables, they are saved
instantly. Is it really supposed to take this long for larger tables to be
updated?
Any suggestions would be greatly appreciated.Never make a change to a production db with EM. Most of the time it will
create a temp table, copy all the data over from the original, drop the
original and then rename the temp table to the original name. Most of the
time a simple ALTER TABLE is all you need to issue. If it is something
simple like increasing a varchar column it might just be a meta data change.
--
Andrew J. Kelly SQL MVP
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:BACB0B6C-FDA2-439A-BFA5-A5672AE7AE21@.microsoft.com...
> Hi,
> I am running SQL 2000 with an 8 GB database. I have two relatively large
> tables (each with about 2 million records in them), and I occasionally
> need
> to make changes to the table design (add a column, increase the size of an
> existing field, etc.) on these two tables through the Enterprise Manager.
> However, saving these changes can take betwen 5-10 minutes to complete.
> If I make changes to any of my other smaller tables, they are saved
> instantly. Is it really supposed to take this long for larger tables to
> be
> updated?
> Any suggestions would be greatly appreciated.

Enterprise manager taking up to 10 minutes to save design changes

Hi,
I am running SQL 2000 with an 8 GB database. I have two relatively large
tables (each with about 2 million records in them), and I occasionally need
to make changes to the table design (add a column, increase the size of an
existing field, etc.) on these two tables through the Enterprise Manager.
However, saving these changes can take betwen 5-10 minutes to complete.
If I make changes to any of my other smaller tables, they are saved
instantly. Is it really supposed to take this long for larger tables to be
updated?
Any suggestions would be greatly appreciated.Never make a change to a production db with EM. Most of the time it will
create a temp table, copy all the data over from the original, drop the
original and then rename the temp table to the original name. Most of the
time a simple ALTER TABLE is all you need to issue. If it is something
simple like increasing a varchar column it might just be a meta data change.
Andrew J. Kelly SQL MVP
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:BACB0B6C-FDA2-439A-BFA5-A5672AE7AE21@.microsoft.com...
> Hi,
> I am running SQL 2000 with an 8 GB database. I have two relatively large
> tables (each with about 2 million records in them), and I occasionally
> need
> to make changes to the table design (add a column, increase the size of an
> existing field, etc.) on these two tables through the Enterprise Manager.
> However, saving these changes can take betwen 5-10 minutes to complete.
> If I make changes to any of my other smaller tables, they are saved
> instantly. Is it really supposed to take this long for larger tables to
> be
> updated?
> Any suggestions would be greatly appreciated.sql

Enterprise Manager error when using WITH (NOLOCKS)

I have added WITH (NOLOCK) to a couple of views that were causing unecessary
blocking for MS Access end user reporting.
I can update edit and save the views fine with Query Analyzer, however when
I try and run the query from Enterprise Manager Design View I get this
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect Syntax
The views work fine in everywhere else. When in EM if I remove the WITH
(NOLOCK) I do not get the error.
It looks as if the alias is causing the issue.
select * from MyTable WITH (NOLOCK) works
however Select * from MyTable mt WITH (NOLOCK) causes the error. But only in
EM not in query analyzer.
Is anyone else experiencing this?
"lurdan" wrote:

> I have added WITH (NOLOCK) to a couple of views that were causing unecessary
> blocking for MS Access end user reporting.
> I can update edit and save the views fine with Query Analyzer, however when
> I try and run the query from Enterprise Manager Design View I get this
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect Syntax
> The views work fine in everywhere else. When in EM if I remove the WITH
> (NOLOCK) I do not get the error.
>
|||I got the same result. When I tried to run the query in EM, the query tool
inverted the alias to after the table hint, which is bad syntax.
Russel Loski, MCSD.Net
"lurdan" wrote:
[vbcol=seagreen]
> It looks as if the alias is causing the issue.
> select * from MyTable WITH (NOLOCK) works
> however Select * from MyTable mt WITH (NOLOCK) causes the error. But only in
> EM not in query analyzer.
> Is anyone else experiencing this?
> "lurdan" wrote:

Enterprise Manager error when using WITH (NOLOCKS)

I have added WITH (NOLOCK) to a couple of views that were causing unecessary
blocking for MS Access end user reporting.
I can update edit and save the views fine with Query Analyzer, however when
I try and run the query from Enterprise Manager Design View I get this
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
Syntax
The views work fine in everywhere else. When in EM if I remove the WITH
(NOLOCK) I do not get the error.It looks as if the alias is causing the issue.
select * from MyTable WITH (NOLOCK) works
however Select * from MyTable mt WITH (NOLOCK) causes the error. But only in
EM not in query analyzer.
Is anyone else experiencing this?
"lurdan" wrote:

> I have added WITH (NOLOCK) to a couple of views that were causing unecessa
ry
> blocking for MS Access end user reporting.
> I can update edit and save the views fine with Query Analyzer, however whe
n
> I try and run the query from Enterprise Manager Design View I get this
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorre
ct Syntax
> The views work fine in everywhere else. When in EM if I remove the WITH
> (NOLOCK) I do not get the error.
>|||I got the same result. When I tried to run the query in EM, the query tool
inverted the alias to after the table hint, which is bad syntax.
--
Russel Loski, MCSD.Net
"lurdan" wrote:
[vbcol=seagreen]
> It looks as if the alias is causing the issue.
> select * from MyTable WITH (NOLOCK) works
> however Select * from MyTable mt WITH (NOLOCK) causes the error. But only
in
> EM not in query analyzer.
> Is anyone else experiencing this?
> "lurdan" wrote:
>