I wand to upgrade my varchar(8000) to more data(I mean , I want to increase 8000) what I will do for this?
Hoping for your support.
+7
A:
On SQL Server 2005 and up, you can upgrade to VARCHAR(MAX) (up to 2 GB of data):
ALTER TABLE dbo.YourTable
ALTER COLUMN YourColumn VARCHAR(MAX)
That should do it.
marc_s
2009-11-25 17:26:16
if i use this , exixting data will loss or no?
Alex
2009-11-25 17:30:14
The existing data will be fine :)
CodeByMoonlight
2009-11-25 17:33:21
No, you will not loose any data - your field just now supports 2 GB instead of 8000 characters of data.
marc_s
2009-11-25 17:33:40
No, data is not lost
yu_sha
2009-11-25 17:33:56
i getting an error The statement has been terminated. Msg 9002, Level 17, State 4, Line 1 The transaction log for database 'newdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Alex
2009-11-25 17:53:41
Your DB log file is full you can truncate it with BACKUP LOG <yourdatabasename> WITH TRUNCATE_ONLY if sql 2005; if 2008 see next comment... Before changing the table defn; how many records are in the table? It may be quicker to move the records to a new table with the new definition and then drop the original table; finally rename the new table to old.
u07ch
2009-11-25 18:12:31
alter database <mydb> set recovery simplegocheckpointgoalter database <mydb> set recovery fullgobackup database pubs to disk = 'c:\mydb.bak' with initgodbcc shrinkfile (N'mydb_log' , 1)go
u07ch
2009-11-25 18:13:11
Yup, that's *not* a problem with the statement, but a problem with your database transaction log. It fills up quite a bit when you do these kind of updates. Put your database into simple mode - do the updates - then go back to "Full" backup mode.
marc_s
2009-11-25 19:57:08