Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Modifying Column Size Expand / Collapse
Author
Message
Posted Monday, July 23, 2012 4:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 11:45 AM
Points: 9, Visits: 39
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
Posted Monday, July 23, 2012 5:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 20,462, Visits: 14,089
Which part do you need help with?



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


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
Posted Monday, July 23, 2012 6:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 11:33 PM
Points: 1,789, Visits: 1,013
the table will be locked while the conversion occurs. How big the the table ?

Jayanth Kurup
Post #1334154
Posted Monday, July 23, 2012 6:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 20,462, Visits: 14,089
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


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 #1334155
Posted Tuesday, July 24, 2012 6:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 11:45 AM
Points: 9, Visits: 39
15.7 gb
Post #1334403
Posted Tuesday, July 24, 2012 6:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 11:45 AM
Points: 9, Visits: 39
Yes I have a dev box that I can test on.
Post #1334404
Posted Tuesday, July 24, 2012 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 11:45 AM
Points: 9, Visits: 39
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
Posted Tuesday, July 24, 2012 10:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 20,462, Visits: 14,089
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


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 #1334628
Posted Tuesday, July 24, 2012 10:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 11:45 AM
Points: 9, Visits: 39
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
Posted Tuesday, July 24, 2012 11:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 11:33 PM
Points: 1,789, Visits: 1,013
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 »

Add to briefcase 12»»

Permissions Expand / Collapse