Modifying Column Size

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » Modifying Column Size

11 posts, Page 1 of 2

1 2 »»

Modifying Column Size

Rate Topic

Display Mode

Topic Options

Author

Message

gbloom-998285

Posted Monday, July 23, 2012 4:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 08, 2012 2:41 PM
Points: 9, Visits: 34

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!

Post #1334115

SQLRNNR

Posted Monday, July 23, 2012 5:53 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

Which part do you need help with?

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1334149

Jayanth_Kurup

Posted Monday, July 23, 2012 6:10 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008

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

Jayanth Kurup

Post #1334154

SQLRNNR

Posted Monday, July 23, 2012 6:11 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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

Post #1334155

gbloom-998285

Posted Tuesday, July 24, 2012 6:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 08, 2012 2:41 PM
Points: 9, Visits: 34

15.7 gb

Post #1334403

gbloom-998285

Posted Tuesday, July 24, 2012 6:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 08, 2012 2:41 PM
Points: 9, Visits: 34

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

Post #1334404

gbloom-998285

Posted Tuesday, July 24, 2012 6:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 08, 2012 2:41 PM
Points: 9, Visits: 34

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.

Post #1334406

SQLRNNR

Posted Tuesday, July 24, 2012 10:10 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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)

Post #1334628

gbloom-998285

Posted Tuesday, July 24, 2012 10:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 08, 2012 2:41 PM
Points: 9, Visits: 34

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.

Post #1334664

Jayanth_Kurup

Posted Tuesday, July 24, 2012 11:23 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008

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

Post #1334674

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions