Modifying Column Size

Question

Modifying Column Size

gbloom-998285

SSC Enthusiast

Points: 105
More actions
July 23, 2012 at 4:02 pm

#276573

I have a 22gb database with a column called [var]. I need to change this column from decimal(12,5) to decimal(22,7). The database must stay on-line and is collecting data 24/7. My overall free hard drive space is less than 20gb.
Looking for some help!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Which part do you need help with?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

the table will be locked while the conversion occurs. How big the the table ?

Jayanth Kurup[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Do you have a test environment to test the changes first with a table of the same size?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gbloom-998285 SSC Enthusiast Points: 105 More actions · Answer 4

gbloom-998285

SSC Enthusiast

Points: 105

July 24, 2012 at 6:01 am

#1516733

15.7 gb

gbloom-998285 SSC Enthusiast Points: 105 More actions · Answer 5

gbloom-998285

SSC Enthusiast

Points: 105

July 24, 2012 at 6:02 am

#1516734

Yes I have a dev box that I can test on.

gbloom-998285 SSC Enthusiast Points: 105 More actions · Answer 6

I need help with making the field change without doubling/tripling the size of the table to make the change. I have tried the alter table/ alter column and I ran out of disk space. So the real issue here is to be able to make the field size change when the database table is about the same size as my free space on the drive.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

What is the tsql you used to make the change? Are you dumping the table into a temp table and then renaming that temp table and replacing the actual table or are you doing something like the following?

Given a table such as this

CREATE TABLE dbo.Employee

(

EmployeeID INT IDENTITY (1,1) NOT NULL

,FirstName VARCHAR(50) NULL

,MiddleName VARCHAR(50) NULL

,LastName VARCHAR(50) NULL

,DateHired datetime NOT NULL

)

Use this to increase the size of the FirstName filed to 100 characters.

ALTER TABLE dbo.Employee

ALTER COLUMN FirstName VARCHAR(100)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gbloom-998285 SSC Enthusiast Points: 105 More actions · Answer 8

This is the exact code that I tried. The disk space started out at 20gb free and when it got down to 2gb free, I cancelled the job.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 9

I wud suggest adding a new column and moving the data in batches then. Once done u can simply rename the old column or drop it.

Jayanth Kurup[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Yup, add a new nullable column, update in small batches running log backups between the batches to prevent the log from blowing out (that's probably what used all the space).

Or create a new table, move the data over in chunks and delete the old table when done

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass