Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    Washington DC Area
    Posts
    2

    Unanswered: Trouble changing design of large table

    I'm using Enterprise Manager with SQL Server 2000 on Windows 2000 Professional.

    I copied several tables from another database into a new database. Whenever I copy tables from one database to another, the ID column loses it's attribute as a primary key so then I have to go into the design view and set it to a primary key and also set the "Identity" and "Auto-increment" properties again.

    This time I copied a really big table with about 600,000 rows into a new database. I went into the design view for this table and set the primary key properties again. This time I got an error message:

    'MY_TABLE_NAME' table
    - Unable to modify table.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Tmp_MY_TABLE_NAME' in database 'MY_DB_NAME' because the 'PRIMARY' filegroup is full.

    I checked the properties of my database before trying to alter the table. The database is 390 MB and had 289 MB available. After trying to alter the table, I had 179 MB available. (Then I backed up the DB and my free space went back up to 289 MB.)

    I don't really want to make my database any bigger since I've had trouble shrinking databases in the past after making them too big.

    I'm not really sure what the problem is, but I have further plans to alter this table in my new database so ... any ideas for how I can alter this table?

    Thanks for any help!

  2. #2
    Join Date
    Feb 2003
    Posts
    15
    It probably needs the disk space, since you're dropping and reloading the table in question. That table probalby takes up most of the disk space for the entire database.

    How are you copying the tables, such that it doesn't preserve the primary key? Maybe you should take a different approach on the "copy", that could solve your problem.

  3. #3
    Join Date
    Feb 2003
    Location
    Washington DC Area
    Posts
    2
    Originally posted by strader
    How are you copying the tables, such that it doesn't preserve the primary key? Maybe you should take a different approach on the "copy", that could solve your problem.
    The way I've been copying is in Enterprise manager, I right click on a database and select All Tasks > Import Data. Then I click through selecting the source and destinations databases, and select "Copy table(s) form source database" or something like that, then select the tables I want to import. I haven't been doing this very long, so I the fact that the primary key isn't retained started seeming normal to be because it happens every time.

    I'll consider making the database bigger. I didn't really want to because I'm relatively new to SQL Server administration and have had a hard time shrinking databases, but I guess I need to work on that anyway... :-)

  4. #4
    Join Date
    Feb 2003
    Posts
    15
    Xiann -

    If you plan on "copying" the tables frequently, you should probably look into a different approach that's not so hand intensive. Another approach would also avoid the problem of having to add the primary key you're missing.

    For example, create the table in the new database:

    create table foo (fooid int identity primary key, fooval varchar(1))
    go

    then use something like bcp or dts to get the data from the old database to the new database. That way, when the data is loaded into the new table, the primary key is already there, and SQL Server doesn't have the overhead of dropping and recreating the table, which is likely what is causing you space usage.

  5. #5
    Join Date
    Feb 2003
    Posts
    109

    uh get more hd space

    its cheap these days..
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  6. #6
    Join Date
    Feb 2003
    Posts
    109

    truncate log files

    and turn on autoshrink on all your databases.

    how big is your tempDB?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    turning autoshrink on is a good way to kill performance when you least want it!

    Set up a maintance job to shrink your DB when the system is least likely to be used.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Feb 2003
    Posts
    109

    but the good thing about autoshrink is

    but it is a lot easier for beginners-- especially when you are dealing with a 2gb limit from the desktop edition of sql server.

    or whatever you're running.
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •