Altering SQL Server 2000 table design

I'm trying to do a simple alteration to the table design of one of our
SQL 2k tables, simply changing an identity row so that its not 'not
for replication', and its taking absolutely ages to do so, and stops
the sql server from working.

Whilst it's attempting the update, no one can access the database, the
sqlservr.exe memory usage shoots up and enterprise manager reports a
not responding status. Eventually after about 10 minutes, it bombs out
reporting,

Unable to modify table
Could not allocate space for object 'Tmp_TableName' in database
'DBNAME' because the 'PRIMARY' filegroup is full.

The table i'm attempting to change has only about 4000 records so
there's not a huge amount of data.

Any ideas what's causing this and how i can get around it?

A similar thing happens when i attempt to change the length of a
varchar too.

Thanks in advance for any suggestions

Dan Williams."Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406040735.5d88d033@.posting.google.c om...
> I'm trying to do a simple alteration to the table design of one of our
> SQL 2k tables, simply changing an identity row so that its not 'not
> for replication', and its taking absolutely ages to do so, and stops
> the sql server from working.
> Whilst it's attempting the update, no one can access the database, the
> sqlservr.exe memory usage shoots up and enterprise manager reports a
> not responding status. Eventually after about 10 minutes, it bombs out
> reporting,
> Unable to modify table
> Could not allocate space for object 'Tmp_TableName' in database
> 'DBNAME' because the 'PRIMARY' filegroup is full.
> The table i'm attempting to change has only about 4000 records so
> there's not a huge amount of data.
> Any ideas what's causing this and how i can get around it?
> A similar thing happens when i attempt to change the length of a
> varchar too.
> Thanks in advance for any suggestions
> Dan Williams.

Unfortunately, ALTER TABLE doesn't allow you to modify IDENTITY columns, so
there's no way to remove the NOT FOR REPLICATION option without recreating
the table. Behind the scenes, Enterprise Manager will create a new table,
set IDENTITY_INSERT ON, INSERT the rows from the existing table, drop the
original table, then rename the new one. Tmp_TableName is the 'working'
table that will be renamed after the existing TableName is dropped.

With a large table, this can be a slow process requiring a lot of disk
space, but 4000 rows doesn't sound like much data (unless you have
text/image columns perhaps). Anyway, the error message is clear - no more
space in the filegroup. So you need to add space by expanding the existing
database file(s). If you can't do this for some reason, then one solution
might be to use bcp.exe or DTS to export the data to a flat file, drop and
recreate the table yourself, then import the data.

Finally, as a general remark, Enterprise Manager hides a lot of what it's
really doing from you, so many people prefer to use Query Analyzer as much
as possible, since then you have complete control over what you're doing.

Simon|||Thanks for the response.

Having done a bit more research on Google i managed to find this:-

run this in your publication database.
Here I am setting the identity column for the jobs table to NFR

sp configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat &
0x0001 <> 0 and colstat & 0x0008 = 0 and id=object id('jobs')
GO
sp configure 'allow updates', 0

Anyone know the value to set colstat too, so as to disable the NFR,
and just make it a normal IDENTITY value?

I also found this web site which was a good reference.

http://www.winnetmag.com/SQLServer/...2080/22080.html

Having clicked on the 'Save Change Script' button of Enterprise
Manager when attempting to do this, I see what you mean about the
amount of work that EM actually does.

Thanks again

Dan.

> Unfortunately, ALTER TABLE doesn't allow you to modify IDENTITY columns, so
> there's no way to remove the NOT FOR REPLICATION option without recreating
> the table. Behind the scenes, Enterprise Manager will create a new table,
> set IDENTITY_INSERT ON, INSERT the rows from the existing table, drop the
> original table, then rename the new one. Tmp_TableName is the 'working'
> table that will be renamed after the existing TableName is dropped.
> With a large table, this can be a slow process requiring a lot of disk
> space, but 4000 rows doesn't sound like much data (unless you have
> text/image columns perhaps). Anyway, the error message is clear - no more
> space in the filegroup. So you need to add space by expanding the existing
> database file(s). If you can't do this for some reason, then one solution
> might be to use bcp.exe or DTS to export the data to a flat file, drop and
> recreate the table yourself, then import the data.
> Finally, as a general remark, Enterprise Manager hides a lot of what it's
> really doing from you, so many people prefer to use Query Analyzer as much
> as possible, since then you have complete control over what you're doing.
> Simon|||"Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406041501.37b74d00@.posting.google.c om...
> Thanks for the response.
> Having done a bit more research on Google i managed to find this:-
> run this in your publication database.
> Here I am setting the identity column for the jobs table to NFR
> sp configure 'allow updates', 1
> GO
> reconfigure with override
> GO
> update syscolumns set colstat = colstat | 0x0008 where colstat &
> 0x0001 <> 0 and colstat & 0x0008 = 0 and id=object id('jobs')
> GO
> sp configure 'allow updates', 0
>
> Anyone know the value to set colstat too, so as to disable the NFR,
> and just make it a normal IDENTITY value?
> I also found this web site which was a good reference.
> http://www.winnetmag.com/SQLServer/...2080/22080.html
> Having clicked on the 'Save Change Script' button of Enterprise
> Manager when attempting to do this, I see what you mean about the
> amount of work that EM actually does.
> Thanks again
> Dan.

<snip
Based on the query above, you need an XOR operation to remove NOT FOR
REPLICATION:

update syscolumns
set colstat = colstat ^ 8
where colstat & 1 <> 0
and colstat & 8 <> 0
and id =object_id('jobs')

But be very careful with this - Microsoft does not support modifications to
system tables (see "System Tables" in Books Online), and the colstat column
is not documented (see "syscolumns"). So if you have problems, then you're
on your own - dropping and recreating the table is the supported, reliable
method.

Simon

ALTER COLUMN NAME?

I'm starting to think that there is no way to simply ALTER a column name with
T-SQL. Is that true?
That means I need to do it in EM, where it will do all that ugly
drop/re-create table stuff?
Any help would be appreciated.
Hi
Yes, that is true.
Cheers
Mike
"Steve Z" wrote:

> I'm starting to think that there is no way to simply ALTER a column name with
> T-SQL. Is that true?
> That means I need to do it in EM, where it will do all that ugly
> drop/re-create table stuff?
> Any help would be appreciated.
|||Thanks...
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Yes, that is true.
> Cheers
> Mike
> "Steve Z" wrote:
|||> I'm starting to think that there is no way to simply ALTER a column name
with
> T-SQL. Is that true?
NO!
EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
http://www.aspfaq.com/
(Reverse address to reply.)
|||Thank you very, very much - that is much neater...
"Aaron [SQL Server MVP]" wrote:

> with
> NO!
> EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

ALTER COLUMN NAME?

I'm starting to think that there is no way to simply ALTER a column name with
T-SQL. Is that true?
That means I need to do it in EM, where it will do all that ugly
drop/re-create table stuff?
Any help would be appreciated.Hi
Yes, that is true.
Cheers
Mike
"Steve Z" wrote:
> I'm starting to think that there is no way to simply ALTER a column name with
> T-SQL. Is that true?
> That means I need to do it in EM, where it will do all that ugly
> drop/re-create table stuff?
> Any help would be appreciated.|||Thanks...
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Yes, that is true.
> Cheers
> Mike
> "Steve Z" wrote:
> > I'm starting to think that there is no way to simply ALTER a column name with
> > T-SQL. Is that true?
> >
> > That means I need to do it in EM, where it will do all that ugly
> > drop/re-create table stuff?
> >
> > Any help would be appreciated.|||> I'm starting to think that there is no way to simply ALTER a column name
with
> T-SQL. Is that true?
NO!
EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Thank you very, very much - that is much neater...
"Aaron [SQL Server MVP]" wrote:
> > I'm starting to think that there is no way to simply ALTER a column name
> with
> > T-SQL. Is that true?
> NO!
> EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

Alter column causing log to fill

I'm trying to simply change a column definition from Null to Not Null. It's
a multi million row table. I've already checked to make sure there are no
nulls for any rows and a default has been created for the column. My log is
set to autogrow and as the alter column colname char(6) Not Null runs the
log begins to grow. If I use no check BOL say the optimizer won't consider
the change. How can I change the nullability of a column that currently
contains no nulls without using up extreme amounts of log space?

DannyDanny (istdrs@.flash.net) writes:
> I'm trying to simply change a column definition from Null to Not Null.
> It's a multi million row table. I've already checked to make sure there
> are no nulls for any rows and a default has been created for the column.
> My log is set to autogrow and as the alter column colname char(6) Not
> Null runs the log begins to grow. If I use no check BOL say the
> optimizer won't consider the change. How can I change the nullability
> of a column that currently contains no nulls without using up extreme
> amounts of log space?

I guess the reason that the log grows, is that SQL Server needs to update
internal data structures in each page on the table. For each row there
is a bitmap that specifies which columns in the row that have the NULL
value. If you take make one column NOT NULL, then the bitmap is affected,
at least if it is not the last column in the map.

CHECK/NOCHECK has nothing to do with it, verifying the constraint does not
take any log space. (And SQL Server won't let you to say that a column is
NOT NULL without checking it, to save its own sanity.)

So the only other option to ALTER TABLE, is to take the long way: Rename
the table, create a new table, insert over, restore indexes, triggers and
constraints, move referencing foreign keys and drop the old table. When
you insert data over, you can do it in batches, and with the database
in simple recovery, the log growth will not be equally excessive. A
variation of this with even less log usage may be to bulk out the data,
and load the new table with BCP.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Allocate space

Hi,
I want to allocate 20 GB for a new database, what is the
best way of do it'
I simply allocate a .mdf file of 20 GB or create 4 of 5
GB ? i just have one RAID with 140 GB,the storage is
shared with other hosts, i have the servers's H:\ drive
pointing the storage with 70 GB for my databases in my
instance.
Thanks a lot
Miguel CorreiaI think it doesn't make much of a difference with 1 file or 4 files on the
same Raid disk ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Miguel" <cmlcorreia@.netcabo.pt> wrote in message
news:0ab001c3a398$b410eb30$a501280a@.phx.gbl...
> Hi,
> I want to allocate 20 GB for a new database, what is the
> best way of do it'
> I simply allocate a .mdf file of 20 GB or create 4 of 5
> GB ? i just have one RAID with 140 GB,the storage is
> shared with other hosts, i have the servers's H:\ drive
> pointing the storage with 70 GB for my databases in my
> instance.
> Thanks a lot
> Miguel Correia