add 2nd data file and limit the size of 1st datafile

Question

add 2nd data file and limit the size of 1st datafile

sqlguy80

SSCrazy

Points: 2765
More actions
May 20, 2017 at 9:58 pm

#323707

Hi,
We have two drives for data files and I've been asked to go to each db, limit the max size of primary data file so that it stops using it and then starts using a new data file with unlimited max size.
Any script to restrict the db from using primary data file ? I can use the below to add secondary data file, but what should I see so that there is no maxsize?
ALTER DATABASE dbname
ADD FILE
(
    NAME = dat2,
    FILENAME = 'E:\DATA\t1dat3.ndf',
    SIZE = 1024,
    MAXSIZE = ??,
    FILEGROWTH = 1024
)
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you don't set maxsize, then the size is unlimited. To restrict the size of the first file, you can set a max size on the existing file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sreejith! SSCrazy Points: 2401 More actions · Answer 2

Expand Databases; right click thedatabase and select Properties .Go to Files & change theAutogrowth/Maxsize settings to Limited
You will be able to script this Action to new query window as well.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

sqlguy80 - Saturday, May 20, 2017 9:58 PM
Hi,
We have two drives for data files and I've been asked to go to each db, limit the max size of primary data file so that it stops using it and then starts using a new data file with unlimited max size.
Any script to restrict the db from using primary data file ? I can use the below to add secondary data file, but what should I see so that there is no maxsize?
ALTER DATABASE dbname
ADD FILE
(
    NAME = dat2,
    FILENAME = 'E:\DATA\t1dat3.ndf',
    SIZE = 1024,
    MAXSIZE = ??,
    FILEGROWTH = 1024
)
Thanks,

Be aware that there are certain objects that always hit the primary file of the primary filegroup, so it will need some growth. Put a new file into the same filegroup and the round robin algorithm will kick in.
It will spread data between all files in the filegroup.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉