March 27, 2011 at 10:41 am
Hi ,
On one of our database the data file is completely filled up. it is set to autogrow by 10% and a max size is set to 2000MB. We have enough disk space on the drive.
But when we manually try to add space by using alter database modify file, its not working, even though the command runs successfully.
Its sql server 2000
Pls help
March 27, 2011 at 11:16 am
Maybe post the command you're trying to run, it would help people to see what exactly you're trying to do.
ALTER DATABASE <dbname>
MODIFY FILE
(
NAME = '<logicalname>'
, MAXSIZE = <whatever>
, FILEGROWTH = <whatever>
)
The above will ensure that the maximum size is reset and the filegrowth is set.
I wouldn't expect to see it grow immediately unless you give it a new SIZE option (omitted from my example above) or if you've left the MAXSIZE at less than your new size. Also, FILEGROWTH may be set to nothing?
HTH
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
March 27, 2011 at 11:44 am
ALTER DATABASE [dbname] MODIFY FILE ( NAME = filename, SIZE = 900MB )
the above is the command. the current size of the file is 800MB. but even after running the above command the size dosent change.
March 28, 2011 at 3:16 pm
Do you have auto shrink turned on?
March 28, 2011 at 6:30 pm
As i recall size would be used to decrease the size of the database file to reclaim unused space, not increase the size of the file. As long as you have your max file size set to the size you want and it's not full the database will grow as needed, in your case it looks like you have your max size set to 2GB so if you're only at 800MB now you should have plenty of space.
If you really wanted to increase the size of the file with unused space you could just create a dummy table and fill it garbage records until you reach the desired file size then truncate the table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply