SPREAD DATABASE ON MULTIPLE MDF FILE
-
Hello,
Actually, I have a big database about 500 Go localised on disk but I will have in few months not enought space. I can't extend the disk or move the database on another disk (too long and I can't stop SQL). Basically, I would like to add another disk, limite the size on my mdf file (primary) and add a new file on the same group files (primary).
USE [master]
GO
ALTER DATABASE [GULPER_FICHIER_EXP] MODIFY FILE ( NAME = N'gulper_fichier_exp_Data', MAXSIZE = 492446720KB )
GO
ALTER DATABASE [GULPER_FICHIER_EXP] ADD FILE ( NAME = N'gulper_fichier_exp_Data_1', FILENAME = N'Q:\CNET\EXP\DATA\GULPER_FICHIER_EXP_1.ndf' , SIZE = 10485760KB , FILEGROWTH = 1048576KB ) TO FILEGROUP [PRIMARY]
GO
Is it a good solution or not ?
What do you think about performance impact ?
Thanks for your help,
Eric
PS : WINDOWS SERVER 2003 64 / SQL SERVER 2005 SP3 64 Entreprise Edition
-
You can do that, but keep in mind that SQL will still write data on the current file.
-
But the main file can't grow up . Is it correct ?
Thanks,
Eric
-
If you set it to not autogrow or set a max size, then correct. With 2 files in the same filegroup, one mostly empty one mostly full, SQL will write the majority of new data to the mostly empty file. If tries to get the data balanced so that both files fill around the same time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gail is right (as always) :-). Just remember that if you set the current file not to autogrowth, when it becomes full you will get all kind of errors, so if you decide to do the file growth manually try to keep an eye on it. 😉
-
Which kind of error I can get ? :crying:
-
Ignacio A. Salom Rangel (1/26/2012)
Just remember that if you set the current file not to autogrowth, when it becomes full you will get all kind of errorsNo it won't. There will only be errors when all the files in the filegroup are full and, since the OP is adding a second file to primary, only if the mdf has reached max size and that second file fills the disk it is on will there be errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok.
Could I have a performance issues if I implement this solution ?
Thanks,
Eric
-
From simply having multiple files in Primary, no (unless you have several thousand).
From other things, sure. That DB's getting big so things like indexes, well performing SQL, stats, etc become more important.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (1/26/2012)
Ignacio A. Salom Rangel (1/26/2012)
Just remember that if you set the current file not to autogrowth, when it becomes full you will get all kind of errorsNo it won't. There will only be errors when all the files in the filegroup are full and, since the OP is adding a second file to primary, only if the mdf has reached max size and that second file fills the disk it is on will there be errors.
Ok, so when the first file is full, SQL will write only to the second file?
-
Yes, it's called proportional fill.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (1/26/2012)
Yes, it's called proportional fill.Thank you, I was indeed confusing the primary data file with the primary file group. 🙂
-
Another thing, after to implement this solution, I have to run a full backup because if I don't do that my diff backup doesn't work. Is it correct ?
Thanks,
Eric
-
Another thing you need to realize is that when you rebuild/reorganize an index - SQL Server will spread the data across both files according to the proportional fill algorithm.
After the index rebuild, you will end up with more space available in the first file - and more used in the second file. If your second file is added and sized the same as the first file - and you then rebuild all indexes (and don't have any heaps) you would then be split across both files fairly evenly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
ERIC CRUDELI (1/26/2012)
Another thing, after to implement this solution, I have to run a full backup because if I don't do that my diff backup doesn't work. Is it correct ?Thanks,
Eric
That's not true.. your diff backups will still be working.. However, when you are restoring from your full backups (taken earlier), you won't have this new data-file until you continue the restore using the diff backup that was taken after the new data-file is added.
If you have a weekly full backups, daily diff as a backup strategy, then the next weekly full backup will have the new data-file (when you restore from that full backup)..
Bru MedishettyBlog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply