Changing field properties locks up server.

[cstewart75]

Hello,

I'm running SQL Server 2000 SP3a on an NT 4 machine with 2GB of RAM, 130GB free disk space, and dual 700Mhz Xeon processors. Whenever I change one of the field properties to allow nulls, it causes NT to hang and the only fix is to reboot. The table change affects about 60,000 records.

My question to the group; is this an NT/hardware problem, or could it possibly be related to SQL Server? The only recent change is the installation of SP3a. When NT hangs, we start seeing a lot of SCSI timeouts in the event logs pointing to the server's RAID controller. There are no events pointing to SQL Server. We have tested the same table change on a mirrored drive set on the same server, and the problem didn't occur. I'm trying to rule out software problems and narrow it down to hardware, but want to make sure nobody else has experienced problems like this after applying SP3a. Thanks in advance for your help.

 

[JayKusch]

How long are you waiting for the change to take place before you reboot the server?

Thanks

J. Kusch

 

[cstewart75]

After a minute or two the server completely freezes (i.e. clients cannot access shares, mouse doesn't respond, Ctl+Alt+Del doesn't work). However,when the database was moved to the mirrored driveset and the same process was run the server stayed up and it worked fine and it only took around 30 seconds.

Thank you

 

[Corran007]

Id say its hardware. I havnt heard of anything like that from sqlserver. It may have a bad part of a disk. If the database is trying to update that part that it cannot get to, it may be freezing. Had something like this about a month ago.

 

[MDXer]

Are you attempting to make the change using T-SQL or through EM? If your using EM you may have better luck doing it using T-SQL. When you modify table definitions in EM and save the changes what happens isn't a matter of just applying the change to the existing table but rather it creates a new table with the updated definition, copies the data from the exisiting table into the new table, Drops the old table and finally renames the new table to that of the old.

Below is a script that EM generates when changing a a single column to allow nulls. This is a small table but demonstartes what takes place.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_lvlAge
	(
	Years int NOT NULL,
	AgeDescription varchar(25) NULL
	)  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.lvlAge)
	 EXEC('INSERT INTO dbo.Tmp_lvlAge (Years, AgeDescription)
		SELECT Years, AgeDescription FROM dbo.lvlAge TABLOCKX')
GO
DROP TABLE dbo.lvlAge
GO
EXECUTE sp_rename N'dbo.Tmp_lvlAge', N'lvlAge', 'OBJECT'
GO
CREATE NONCLUSTERED INDEX IX_lvlAge ON dbo.lvlAge
	(
	Years
	) WITH PAD_INDEX,  FILLFACTOR = 90 ON [PRIMARY]
GO
COMMIT

You would be better off using T-SQL like in the following

ALTER TABLE lvlAge
	ALTER COLUMN AgeDescription varchar(25) Null

Both have the same end result but as you can see the server does much less work using the T-SQL

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck