Unable to increase initial file size in SQL2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Unable to increase initial file size in...

Unable to increase initial file size in SQL2000

Rate Topic

Display Mode

Topic Options

Author

Message

jql714

Posted Tuesday, September 04, 2012 9:12 AM

Forum 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! Smile

Post #1353981

durai nagarajan

Posted Tuesday, September 04, 2012 10:20 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:05 AM
Points: 856, Visits: 2,115

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

which is group is default?.

Regards
Durai Nagarajan

Post #1354028

jql714

Posted Tuesday, September 04, 2012 10:49 AM

Forum 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

jql714

Posted Thursday, September 13, 2012 4:16 PM

Forum 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

MVDBA

Posted Friday, September 14, 2012 8:04 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

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

MVDBA

Post #1359300

jql714

Posted Friday, September 14, 2012 9:03 AM

Forum 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 »

Permissions