Making changes to a table with large amounts of data. Timeout?!

Hello,

I have a table that is fairly large, and I need to make a change to one of the columns in the table. Namely I need to change the datatype and rename that column. When I try to save the updated table, I keep getting a timeout error that says.

'eligibility (dbo)' table
- Unable to create index 'PK_eligibility'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Any ideas on how to make the table change more efficient or change the timeout period. I have to keep the existing data in the table. I am using sql server managment studio(2005) connected to a sql server 2000 database.

Thanks!

this sort of thing always happens to me when using EM. I use only code now.

If you look at the code behind the scenes that EM uses, it is creating a temp table, shoving all the data into that table, re-creating the original table and pushing all the data back. (if you tell it to save the script when you make the change, you'll see what I mean)

For a large table, I would probably create a new column, update the values in the new column with what is in the old column, then drop the old column. The only time consuming step would be the updates, however, you can space those out and update based on a range of values from one of your other columns. (i.e., update table set newcolumn = oldcolumn where datefield between '1/1/2001' and '2/1/2001')

This way it shouldn't have to create the index.

If you are trying to do this on a column that has a constraint, you will have to drop the constraint first. If you are changing the PK, and your PK is the clustered index, it's probably going to be messy any way you go.

Using script, you can't put your columns in different orders. They will always be added at the end.

sql

Maintenance Plan woes

I am very new to SQL Server, and inherited the database administration
for my company. It was setup by a very competent admin and has been
fairly self-sufficient so far. I am having a problem with my main
maintenance plan on my primary SQL server. The plan starts the backup
of all of our databases on that server at 8pm. To complete all of the
tasks in the maintenance plan is taking about 12 hours lately,
spilling into the work day. The maintenance plan first makes a backup
of all databases to a local RAID. Then it does the same thing to a
network server, which is then copied to tape using our backup
software. The maintenance plan then deletes all backup files older
than 5 days. Then it Reorgs the all User DB Indexes, and Updates all
User DB Stats. There are about 40 DBs total on this server, and at
least one of them I know for a fact is very large (> 200GB) What is
best practice in this situation to make it so that all maintenance
plans finish before the work day starts'
Thanks in advance.>>...The maintenance plan first makes a backup
of all databases to a local RAID. Then it does the same thing to a
network server...<<
Why the double work? Redundancy is fine, but this seems like overkill.
You may also want to investigate Litespeed or a similar product for
compressed/faster backup and recovery...especially with that 200 GB
database.
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Matt" <matthewsatkins@.gmail.com> wrote in message
news:ac1d8d14-018c-4f3d-96c9-1870873024c1@.m34g2000hsf.googlegroups.com...
>I am very new to SQL Server, and inherited the database administration
> for my company. It was setup by a very competent admin and has been
> fairly self-sufficient so far. I am having a problem with my main
> maintenance plan on my primary SQL server. The plan starts the backup
> of all of our databases on that server at 8pm. To complete all of the
> tasks in the maintenance plan is taking about 12 hours lately,
> spilling into the work day. The maintenance plan first makes a backup
> of all databases to a local RAID. Then it does the same thing to a
> network server, which is then copied to tape using our backup
> software. The maintenance plan then deletes all backup files older
> than 5 days. Then it Reorgs the all User DB Indexes, and Updates all
> User DB Stats. There are about 40 DBs total on this server, and at
> least one of them I know for a fact is very large (> 200GB) What is
> best practice in this situation to make it so that all maintenance
> plans finish before the work day starts'
> Thanks in advance.|||Hi,
Firts, Do you need the backups in local disk for 5 days, if you have one
copy in tape?
second. You software backup is not possible to integrate with SQL?
anyware,
The better is develop all tasks in differents plans, this mean
1.Perform backups every days (1 plan)
2. Reorg indexes, once per week or per month it isn't necessary reorg.
Indexes every day. (1 or more plan)
But not all in the same day for exampl, Yor will reorg. indexes in 5 or 6
databases per day, another day others databases.
3. You can Perform Update statistics every day (if you want) (1 or more plan)
4. Create Jobs with t-sql to perform maintenance on your databases, one Job
for database or task. in this way you can to take control of your maintenance
plans.
Regards,
"Matt" wrote:
> I am very new to SQL Server, and inherited the database administration
> for my company. It was setup by a very competent admin and has been
> fairly self-sufficient so far. I am having a problem with my main
> maintenance plan on my primary SQL server. The plan starts the backup
> of all of our databases on that server at 8pm. To complete all of the
> tasks in the maintenance plan is taking about 12 hours lately,
> spilling into the work day. The maintenance plan first makes a backup
> of all databases to a local RAID. Then it does the same thing to a
> network server, which is then copied to tape using our backup
> software. The maintenance plan then deletes all backup files older
> than 5 days. Then it Reorgs the all User DB Indexes, and Updates all
> User DB Stats. There are about 40 DBs total on this server, and at
> least one of them I know for a fact is very large (> 200GB) What is
> best practice in this situation to make it so that all maintenance
> plans finish before the work day starts'
> Thanks in advance.
>