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

Altering objects

This is a question regarding a general problem of scripting DB changes\alteration:

I'm writing script which contain few (lets say 2) DDL statements, which depend one on the other.

Each statement has it own "Go" command afterwards, for executing it.

I'm writing a script to alter a field and make it not null:

"Alter <table name> alter column <column name> int not null"

, so I can define it afterwards as a primary key, and the next statement add a primary key constraint on this filed:

Alter table <table name>

ADD CONSTRAINT <name of constraint> PRIMARY KEY (name of field).

It all goes fine if I run the statements separatelly !!, but when I try to run them in a script, there's an error message saying that a nullable field cannot be defined as primary key (I don't have the exact syntax here, I'm on a few days vacation).

It seems like the sql server dosen't recognize "quick enough" the first alter of the field, to become not null, so it can allow the field to be primary key.

Is it a problem of unupdated system tables ?

It seems like this is a general problems, because it happens to me also ina nother case, when I try to disable a table trigger, so I can delete one of it's records (the trigger dosen't allow deleting records).

Please support.

Guy

Is it a case of a missing [ GO ] between the ALTER statements, or missing parentheses around the column name?

This seems to work without error.

Code Snippet

CREATE TABLE MyTable
( RowID int IDENTITY,
MyData varchar(20),
MyCol2 char(1)
)
GO

ALTER TABLE MyTable
ALTER COLUMN MyCol2 int NOT NULL
GO

ALTER TABLE MyTable
ADD CONSTRAINT PK_MyCol2 PRIMARY KEY ( MyCol2 )
GO

|||

Guy, this is interesting - particularly as you mention that you are including GO between each statement.

Could you post your complete script to the forum for review?

Thanks

Chris

|||

Arnie,

Thanks for your reply.

The code you published is almost the same I have, except for the create table which dosen't exists in my code (in my case the table is laready exists), and also - in my script, there are few more statements. Still - dosen't work.

I'm actually looking for maybe a DBCC\system command, which can update the status of the relevant sys tables, maybe something like the DBCC UPDATEUSAGE is doing to the info of the sp_spaceused, to make the sysindexes synch with the actual data in the table. Any other solution will be helpfull.

|||

Chris,

Thank you for your reply.

I don't have the exact code here (I'm on a few days vacation), but what I generally do is (pseudo code)

Start transaction

statement 1

Go

error handeling (if @.@.error<>0 goto errorhandle

GO)

statment 2

Go

error handeling (if @.@.error<>0 goto errorhandle

GO)

errorhandle:

rollback transction

commit transaction

As I mentioned, the code run with no problems if I run it step by step, and if I fix the is null statement separatelly (run it before, seperatelly, to make it not null), then the all script run with no problems.

|||

Since the table exists, it would be helpful to post both the table DDL as well as the actual procedure code.

No, there is not a DBCC command that would do what you are asking.

The problem apparantly exists in the information we can't see.

|||

The problem appears to be due to the following:

The GO immediately after each of your IF @.@.ERROR checks. You're trying to jump to a label (errorhandle) which is declared outside of the current batch. Labels can only be referenced within the batch in which they are declared.

Alteration date

THere's a create date stored in Sysobjects, but is it true that there's no track of alteration dates?I also have been unable to find one.
In my case I'm planning to use sp_table_validation to generate a checksum to determine whether a table has been altered.

- Andy Abel|||About sp_table_validation - is there anything similar for other objects than tables?

I thought about searching for a certain string in an SP's code, a string where the user that made the last change wrote his/her signature as a comment. Our developers are fairly disciplined...
But, the 'text' attribute of syscomments was hard to understand, since selecting it using left() and substr() functions gave very different results compared to doing just a select on the attribute. I need to use a string function to truncate the 'text' attribute because it's so wide.|||Rather than using substring() you could pattern matching if you know the form of the comment you're looking for. i.e. you could use:

where text like '%Version%'

or use patindex() with substr() to locate the beginning of your comment section and do a substring from that point.

substring(text,PATINDEX('%Version%', text),255)

- Andy Abel