Altering SQL Server 2000 table design

[Dan Williams]

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.

[Simon Hayes]

Re: Altering SQL Server 2000 table design

"Dan Williams" <dan_williams@n ewcross-nursing.com> wrote in message
news:2eac5d02.0 406040735.5d88d 033@posting.goo gle.com...[color=blue]
> 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.[/color]

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]

Re: Altering SQL Server 2000 table design

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/Article/ArticleID/22080/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.

[color=blue]
> 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[/color]

[Simon Hayes]

Re: Altering SQL Server 2000 table design


"Dan Williams" <dan_williams@n ewcross-nursing.com> wrote in message
news:2eac5d02.0 406041501.37b74 d00@posting.goo gle.com...[color=blue]
> 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/A...080/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.
>[/color]

<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('job s')

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