Unable to increase initial file size in SQL2000

  • I need some help on resize a ndf file in SQL2000. Some of our databases have reached over 1TB. I've added ndf files to these databases. Since we don't have much room on the SAN, the initial sizes for these files are not that big.

    I've found that SQL distributed the new data based on the initial size. That means the mdf file is still loaded with the majority data. So hoping more data will be allocated to the ndf files, I've tried to increase the ndf file size however unsuccessful. Every time, it reverts back to its original size??? The script I've been using is:

    ALTER DATABASE myDB

    MODIFY FILE

    (NAME = myDB_ndf,

    SIZE = 200GB)

    GO

    Can somebody enlight me? I will be very grateful!:-)

  • where is your ndf is it on primary group or secondary?

    which is group is default?.

    Regards
    Durai Nagarajan

  • The ndf files are in the primary filegroup.

  • I've tried the second time using Enterprise Manager in SQL2000. It is executed successfully and the size is seemingly change. But it reverted back again the next day?!!

    Any suggestions or ideas? Thanks a lot!

  • is auto shrink turned on ? is there a dbcc shrinkfile/shrinkdb command being issues?

    MVDBA

  • You are right! The Autoshrink is on for the database. The developers creaeted these databases using their script. I did not create the database but I should have checked it.

    I will turn it off and try it again. Thanks so much!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply