Primary file & tan Log grow crazy!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Primary file & tan Log grow crazy!!

Author

Topic

jasonfisher
Starting Member

7 Posts

Posted - 2005-10-14 : 01:07:14

HI:
I have a Table about 3GB in size. Whenever I try to add a column or set a primary key to this table from the Enterprise Manager, the operation would take a long time and the MDF and LOG file will just keep growing crazy (more then 3GB) that it finanlly eats up all my hard disk space.

So, how many HD space does it need to modify a 3GB Table (eg. add a column). Actually what is the ratio? Is there any better way to modify a large Table?

I have also enable the Auto-Shrink properties of the Table but still no use.

Much appreciate for any advise.

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 08:06:26

Have a look at the script that Enterprise Manager : Table : Design is using (There is a "Create Change Script" button in the Design Table tool)

Its most likely doing:

CREATE TempTable with the new columns and other changes

INSERT INTO TempTable
SELECT *
FROM OriginalTable

DELETE OriginalTable

RENAME TempTable to OriginalTable

thus needs approaching 2x the amount of disk space used by the original table, and a shed load of logging space!

Alternatives?

Add new columns on the end, not in the middle, which are set to NULL (or to NOT NULL and have a Default provided); SQL Server will just modify the schema to "append" these new columns. (However, this is not all plain-sailing as there is evidence that adding columns onto a table, rather than creating a new table and copying the data into it, leads to storage inefficiencies)

Create a temporary database
Copy the data in the table out to the temporary database.
Drop the table
Create the new table
Copy the data back
Drop the temporary database

(Or similarly but export the data to a file first, instead of a temporary database)

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-10-14 : 08:12:05

quote:
(However, this is not all plain-sailing as there is evidence that adding columns onto a table, rather than creating a new table and copying the data into it, leads to storage inefficiencies)

These can be easily remedied by reindexing the clustered index on the table, or dropping it and recreating it.

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 08:42:34

That's worth knowing Rob, thanks.

I was thinking specifically of the tests that Nigel has done

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html

which are not for quite the same scenario as "bunging a new column on the end" anyway

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-14 : 09:53:38

quote:
Originally posted by robvolk

These can be easily remedied by reindexing the clustered index on the table, or dropping it and recreating it.

It's possible to drop a clustered index and recreate it?

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 10:44:17

"It's possible to drop a clustered index and recreate it?"

If that's a question Sam! then Yes - but if you change the keys, and its a big database, then shuffling all the data around can be pretty slow.

There's a DROP EXISTING option to the CREATE INDEX command which means that you can Drop and Recreate in one strike - especially if you are recreating with the same key sequence

I presume this takes care of an issue which can be needed if you DO Drop and separately re-Create the Clustered index, which is:

It can be handy to pre-drop all the secondary indexes, and recreate them afterwards, otherwise they will get changed from using the Clustered Index Keys to using a pointer to the record instead (when you Drop the clustered, and back again when you Create it again!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources