|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Sql Server 2005 - SMS "Modify Table option" times out when trying to 'alter' very large table
trying to insert a startcard_nbr INT column into the following table just above the last_updt_date column. It throws an error window with the following ... ----------------------------------------- Post-Save Notifications ! Errors were encountered during the save process. Some dtabase object were not saved. 'wl_startcard_image' table - Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. --------------------------------------- The table contains approximately 52,000 records with a .pdf image in the image column for each record (about 13.5G of disk space) We can SUCCESSFULLY run the actual Sql Script that the "Modify Table" action creates in the Query Analyzer. Takes about 25 minutes. The script creates a temp table with the new column, uses insert into to move the data from the original data across, drops the original table and renames the temp table. Is there some type of Time Limit on the SMS Table Modify Functions or is the clicky pointy interface simply limited to smaller "more normal" alter table operations ?? Just curious. Barry in Oregon == BEFORE == CREATE TABLE dbo.Tmp_wl_startcard_image ( sc_image_id int NOT NULL IDENTITY (1, 1), sc_image image NULL, last_updt_date datetime NULL, last_updt_userid varchar(20) NULL, rec_creation_date datetime NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] == AFTER == CREATE TABLE dbo.Tmp_wl_startcard_image ( sc_image_id int NOT NULL IDENTITY (1, 1), startcard_nbr int NULL, sc_image image NULL, last_updt_date datetime NULL, last_updt_userid varchar(20) NULL, rec_creation_date datetime NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] When I try to update the table SMS Query Analyzer running on the server console pauses for about 30 seconds and then returns an error window with the following information ... I suspect there is somewhere in the gui a connection with a timeout set
other than infinity. Operations like this should always be done with scripts that have been tested first anyway. As you can see the gui does not always do the best job when altering tables and such. If you didn't care about where the column is physically in the table (and data wise there is no benefit to that) youcould have done a simple alter table to add the column and it would have been a few second operation instead. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "frostbb" <frostbb@newsgroups.nospam> wrote in message news:OTAcV7SHGHA.516@TK2MSFTNGP15.phx.gbl... > We've found that Sql Server 2005 SMS "Modify Table option" 'croaks' when > trying to insert a startcard_nbr INT column into the following table just > above the last_updt_date column. It throws an error window with the > following ... > > ----------------------------------------- > Post-Save Notifications > > ! Errors were encountered during the save process. Some dtabase object > were not saved. > > 'wl_startcard_image' table > - Unable to modify table. > Timeout expired. The timeout period elapsed prior to completion of the > operation or the server is not responding. > --------------------------------------- > > The table contains approximately 52,000 records with a .pdf image in the > image column for each record (about 13.5G of disk space) > > We can SUCCESSFULLY run the actual Sql Script that the "Modify Table" > action creates in the Query Analyzer. Takes about 25 minutes. The script > creates a temp table with the new column, uses insert into to move the > data from the original data across, drops the original table and renames > the temp table. > > Is there some type of Time Limit on the SMS Table Modify Functions or is > the clicky pointy interface simply limited to smaller "more normal" alter > table operations ?? > > Just curious. > > Barry > in Oregon > > == BEFORE == > CREATE TABLE dbo.Tmp_wl_startcard_image > ( > sc_image_id int NOT NULL IDENTITY (1, 1), > sc_image image NULL, > last_updt_date datetime NULL, > last_updt_userid varchar(20) NULL, > rec_creation_date datetime NULL > ) ON [PRIMARY] > TEXTIMAGE_ON [PRIMARY] > > == AFTER == > CREATE TABLE dbo.Tmp_wl_startcard_image > ( > sc_image_id int NOT NULL IDENTITY (1, 1), > startcard_nbr int NULL, > sc_image image NULL, > last_updt_date datetime NULL, > last_updt_userid varchar(20) NULL, > rec_creation_date datetime NULL > ) ON [PRIMARY] > TEXTIMAGE_ON [PRIMARY] > > When I try to update the table SMS Query Analyzer running on the server > console pauses for about 30 seconds and then returns an error window with > the following information ... > > > > > Andrew,
Thanks for the quick response. Appreciate the info. I suspect you are correct about the "Table modify option" being limited in some way by the SMS connection to the db engine, even from the Server console. Could find no hint of a problem in the Server logs or elsewhere. We're migrating our enterprise db from Informix on UNIX to Sql Server 2005 and I'm constantly 'agog' at the capabilities of the clicky - pointy environment. Its truly mind blowing for someone used to the Unix command line environment. The last time I had any REAL dba experience with Sql Server was back in '96 / '97 with (I think) 6.5. The changes between 6.5 and 2005 are truely stunning. This is actually the first limitation I've really encountered with the 2005 SMS GUI and I've exercised it pretty hard while setting things up for the migration. If nothing else I find the GUIs are really nice for the purpose of learning Sql Server's particular brand of sql syntax 'lingo'. Again thanks for the reply. Barry in Oregon Show quoteHide quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:OX7$jdTHGHA.528@TK2MSFTNGP12.phx.gbl... >I suspect there is somewhere in the gui a connection with a timeout set >other than infinity. Operations like this should always be done with >scripts that have been tested first anyway. As you can see the gui does >not always do the best job when altering tables and such. If you didn't >care about where the column is physically in the table (and data wise there >is no benefit to that) youcould have done a simple alter table to add the >column and it would have been a few second operation instead. > > -- > Andrew J. Kelly SQL MVP > > > "frostbb" <frostbb@newsgroups.nospam> wrote in message > news:OTAcV7SHGHA.516@TK2MSFTNGP15.phx.gbl... >> We've found that Sql Server 2005 SMS "Modify Table option" 'croaks' when >> trying to insert a startcard_nbr INT column into the following table just >> above the last_updt_date column. It throws an error window with the >> following ... >> >> ----------------------------------------- >> Post-Save Notifications >> >> ! Errors were encountered during the save process. Some dtabase object >> were not saved. >> >> 'wl_startcard_image' table >> - Unable to modify table. >> Timeout expired. The timeout period elapsed prior to completion of the >> operation or the server is not responding. >> --------------------------------------- >> >> The table contains approximately 52,000 records with a .pdf image in the >> image column for each record (about 13.5G of disk space) >> >> We can SUCCESSFULLY run the actual Sql Script that the "Modify Table" >> action creates in the Query Analyzer. Takes about 25 minutes. The >> script creates a temp table with the new column, uses insert into to >> move the data from the original data across, drops the original table and >> renames the temp table. >> >> Is there some type of Time Limit on the SMS Table Modify Functions or is >> the clicky pointy interface simply limited to smaller "more normal" alter >> table operations ?? >> >> Just curious. >> >> Barry >> in Oregon >> >> == BEFORE == >> CREATE TABLE dbo.Tmp_wl_startcard_image >> ( >> sc_image_id int NOT NULL IDENTITY (1, 1), >> sc_image image NULL, >> last_updt_date datetime NULL, >> last_updt_userid varchar(20) NULL, >> rec_creation_date datetime NULL >> ) ON [PRIMARY] >> TEXTIMAGE_ON [PRIMARY] >> >> == AFTER == >> CREATE TABLE dbo.Tmp_wl_startcard_image >> ( >> sc_image_id int NOT NULL IDENTITY (1, 1), >> startcard_nbr int NULL, >> sc_image image NULL, >> last_updt_date datetime NULL, >> last_updt_userid varchar(20) NULL, >> rec_creation_date datetime NULL >> ) ON [PRIMARY] >> TEXTIMAGE_ON [PRIMARY] >> >> When I try to update the table SMS Query Analyzer running on the server >> console pauses for about 30 seconds and then returns an error window with >> the following information ... >> >> >> >> >> > > Yes and you have already found the ability to see the changes that the gui
will make. Although it sometimes does things the hard way it can be very helpful in seeing what it is doing. You can also use profiler to see how the gui does things as well. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "frostbb" <frostbb@newsgroups.nospam> wrote in message news:O1dffIUHGHA.1088@tk2msftngp13.phx.gbl... > Andrew, > > Thanks for the quick response. Appreciate the info. > > I suspect you are correct about the "Table modify option" being limited in > some way by the SMS connection to the db engine, even from the Server > console. Could find no hint of a problem in the Server logs or elsewhere. > > We're migrating our enterprise db from Informix on UNIX to Sql Server 2005 > and I'm constantly 'agog' at the capabilities of the clicky - pointy > environment. Its truly mind blowing for someone used to the Unix command > line environment. The last time I had any REAL dba experience with Sql > Server was back in '96 / '97 with (I think) 6.5. The changes between 6.5 > and 2005 are truely stunning. > > This is actually the first limitation I've really encountered with the > 2005 SMS GUI and I've exercised it pretty hard while setting things up for > the migration. If nothing else I find the GUIs are really nice for the > purpose of learning Sql Server's particular brand of sql syntax 'lingo'. > > Again thanks for the reply. > > Barry > in Oregon > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:OX7$jdTHGHA.528@TK2MSFTNGP12.phx.gbl... >>I suspect there is somewhere in the gui a connection with a timeout set >>other than infinity. Operations like this should always be done with >>scripts that have been tested first anyway. As you can see the gui does >>not always do the best job when altering tables and such. If you didn't >>care about where the column is physically in the table (and data wise >>there is no benefit to that) youcould have done a simple alter table to >>add the column and it would have been a few second operation instead. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "frostbb" <frostbb@newsgroups.nospam> wrote in message >> news:OTAcV7SHGHA.516@TK2MSFTNGP15.phx.gbl... >>> We've found that Sql Server 2005 SMS "Modify Table option" 'croaks' when >>> trying to insert a startcard_nbr INT column into the following table >>> just above the last_updt_date column. It throws an error window with >>> the following ... >>> >>> ----------------------------------------- >>> Post-Save Notifications >>> >>> ! Errors were encountered during the save process. Some dtabase object >>> were not saved. >>> >>> 'wl_startcard_image' table >>> - Unable to modify table. >>> Timeout expired. The timeout period elapsed prior to completion of the >>> operation or the server is not responding. >>> --------------------------------------- >>> >>> The table contains approximately 52,000 records with a .pdf image in the >>> image column for each record (about 13.5G of disk space) >>> >>> We can SUCCESSFULLY run the actual Sql Script that the "Modify Table" >>> action creates in the Query Analyzer. Takes about 25 minutes. The >>> script creates a temp table with the new column, uses insert into to >>> move the data from the original data across, drops the original table >>> and renames the temp table. >>> >>> Is there some type of Time Limit on the SMS Table Modify Functions or is >>> the clicky pointy interface simply limited to smaller "more normal" >>> alter table operations ?? >>> >>> Just curious. >>> >>> Barry >>> in Oregon >>> >>> == BEFORE == >>> CREATE TABLE dbo.Tmp_wl_startcard_image >>> ( >>> sc_image_id int NOT NULL IDENTITY (1, 1), >>> sc_image image NULL, >>> last_updt_date datetime NULL, >>> last_updt_userid varchar(20) NULL, >>> rec_creation_date datetime NULL >>> ) ON [PRIMARY] >>> TEXTIMAGE_ON [PRIMARY] >>> >>> == AFTER == >>> CREATE TABLE dbo.Tmp_wl_startcard_image >>> ( >>> sc_image_id int NOT NULL IDENTITY (1, 1), >>> startcard_nbr int NULL, >>> sc_image image NULL, >>> last_updt_date datetime NULL, >>> last_updt_userid varchar(20) NULL, >>> rec_creation_date datetime NULL >>> ) ON [PRIMARY] >>> TEXTIMAGE_ON [PRIMARY] >>> >>> When I try to update the table SMS Query Analyzer running on the server >>> console pauses for about 30 seconds and then returns an error window >>> with the following information ... >>> >>> >>> >>> >>> >> >> > >  
Other interesting topics
Cannot connect to remote SQL 2k5 server in local network
Linked server in sql 2005, Oracle and DB2 SQLExpress Rename Cluster Instance - New Hardware - Keep Name Microsoft doesnt really support anything over 32 Databases(??) Slow performance Can I use SQL Express database inside full blown SQL 2005? Error when restoring master database Database refresh SQL Server CPU Spikes |
|||||||||||||||||||||||
