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

Unable to increase initial file size in SQL2000 Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 3:56 PM
Points: 4, Visits: 64
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!
Post #1353981
Posted Tuesday, September 4, 2012 10:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
where is your ndf is it on primary group or secondary?

which is group is default?.



Regards
Durai Nagarajan
Post #1354028
Posted Tuesday, September 4, 2012 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 3:56 PM
Points: 4, Visits: 64
The ndf files are in the primary filegroup.
Post #1354050
Posted Thursday, September 13, 2012 4:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 3:56 PM
Points: 4, Visits: 64
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!
Post #1358950
Posted Friday, September 14, 2012 8:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
is auto shrink turned on ? is there a dbcc shrinkfile/shrinkdb command being issues?

MVDBA
Post #1359300
Posted Friday, September 14, 2012 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 3:56 PM
Points: 4, Visits: 64
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!
Post #1359380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse