November 19, 2013 at 4:02 pm
Need to clarify something.
I have this db about 90 gb data file sitting on a 100gb drive. And I am getting tickets daily, adding more space is not an option at the moment since this server is being upgraded in early 2014.
So, we have another 100 gb free drive and I was planning to create a secondary file(ndf) on that drive but my question is....if I create a ndf file on new drive ON PRIMARY file group, would I be able to shrink the data file(mdf) below the threshold sand stop the auto growth on the original data file and that way I can avoid getting tickts daily?
I hope my question makes sense. Let me know, thnx
Regards,
SQLisAwe5oMe.
November 20, 2013 at 4:29 am
One approach would be to create an new FILEGROUP on the spare disk, add the .ndf to that, and move over some of your tables to the new filegroup.
I'm not sure that just adding an extra file to the the primary would do it for you. The first file will continue to fill until it hits the MAX SIZE, and then it'll spill to the new .ndf.
You can move tables to the new filegroup by creating the clustered index on the new filegroup - this will physically move the data. If you're using Enterprise Edition, you can use the ONLINE index rebuild, but test test test.
Once complete, you should be able to reclaim space from your PRIMARY filegroup (the space vacated by the tables you moved). Whether you do that, or just increase the alert threshold and wait for other tables to fill the space is up to you.
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
November 20, 2013 at 4:31 am
You can just add the new file (with a decent size) and disable autogrow on the first one. With an empty new file and an almost full existing one, SQL will primarily write into the new 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
November 20, 2013 at 6:02 pm
GilaMonster (11/20/2013)
You can just add the new file (with a decent size) and disable autogrow on the first one. With an empty new file and an almost full existing one, SQL will primarily write into the new file
Thanks Gail and sqlmunkee.
Gail, does it really matter if I create the secondary file(ndf) in a PRIMARY filegroup or SECONDARY(UserDefined)? I am not exactly sure what's the advantages/disadvantages of creating it on PRIMARY or SECONDARY filegroups? Please advise.
Regards,
SQLisAwe5oMe.
November 20, 2013 at 11:01 pm
Yes, it makes a huge difference.
If you add it as as secondary filegroup, then it's a separate area of storage. Tables already in primary will continue to grow in primary, which is just your old file. Only new tables, explicitly created ON [Secondary] would be placed in the new filegroup.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply