Can't increase datafile size

Hello All, a peculiar problem. I have a SQL Server 2000 with SP3. The
database on it has several datafiles spread over two file groups
"primary" and "secondary". On one of the files, which is on the primary
file group, when I try to increase the space allocated from 2100MB to
3000 MB it won't do it. No error message is generated. I am doing this
on enterprise manager. There is plenty of space on the disk to allow
for this increase. Any ideas? I am able to increase the datafile by
100 MB. So I can go from 2100 to 2200 but not to 3000.
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Disk quota? Also, some confusion can sometimes be eliminated by
communication in the form of TSQL statements instead of "I do this in EM". I
suggest you construct a TSQL statement to do the file size change and post
here if you don't sort it out. ALTER DATABASE... MODIFY FILE...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:e5M%23LEVrDHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello All, a peculiar problem. I have a SQL Server 2000 with SP3. The
> database on it has several datafiles spread over two file groups
> "primary" and "secondary". On one of the files, which is on the primary
> file group, when I try to increase the space allocated from 2100MB to
> 3000 MB it won't do it. No error message is generated. I am doing this
> on enterprise manager. There is plenty of space on the disk to allow
> for this increase. Any ideas? I am able to increase the datafile by
> 100 MB. So I can go from 2100 to 2200 but not to 3000.
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Check your restricted file growth.. that one gets me all
the time..
>--Original Message--
>Hello All, a peculiar problem. I have a SQL Server 2000
with SP3. The
>database on it has several datafiles spread over two file
groups
>"primary" and "secondary". On one of the files, which is
on the primary
>file group, when I try to increase the space allocated
from 2100MB to
>3000 MB it won't do it. No error message is generated.
I am doing this
>on enterprise manager. There is plenty of space on the
disk to allow
>for this increase. Any ideas? I am able to increase the
datafile by
>100 MB. So I can go from 2100 to 2200 but not to 3000.
>Thanks,
>Raziq.
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

cant increase column size

hey

i've a db running sql server express sp2. the db size now is 1.1 gb
i've a table with a varchar column of size 20 . when i try to increase the column size to 50 i get a
timeout exception, and the the cloumn size is unchanged. this table has 2.5 million records

i use sql server management studio express to do the changes

is there a way to increase this timeout or whtever i can do to update this column size?

thx in advance

I am guessing you are using ssms and the GUI to edit this column. There are occasions when you use the GUI that sql server doesn't choose the most efficient way of executing a task. In this instance, i would hazard a guess that it will be creating a temp table with the new schema and then dumping the records in to it before dropping your table and renaming the new temp one to the original one. (stay with me!!)

If you just try,

ALTER TABLE tbl
ALTER COLUMN col1 VARCHAR(50)

Hopefully, that will be quicker.

|||well strangely thats actually works
weird how the gui can be soo stupid in such a simple task

thx man
|||

There is subtle difference doing this task from GUI and from T-SQL.

From T-SQL:

It alters the existing table to modify that column alone.

From GUI:

1) Creates a new tmp table with the new structure and inserts the data from the table you are modifying with insert select clause.

2) Drops the table you are modifying.

3) Renames the new tmp table to the old table's name.

You could see this for yourself if you turn on SQL Profiler and try to modify the table with T-SQL and from GUI.