Our sql server migration was delayed (naturally) and it is going to run out of space before the new cut over date. In order to keep the database up, I need to give it another database file to start writing to.
I've got space allocated and ready to go, but when I create it, it SQL Server going to immediately start moving data over to the new file?
Question: Would I be better off creating two new files, then running
dbcc shrinkfile (livendb, emptyfile)
on the old one? Ensuring they become equal right away?
Best Answer
SQL Server uses a proportional fill algorithm when determining where to write data.
If you have one file that is 90% full, and add a similarly sized new, empty file to the same filegroup, the new file will get approximately 90% of the writes (because it is nearly empty, and the existing file is 90% full).
Set the new file to the same size as the existing file (naturally, this would be on a different drive, if the existing drive is out of space). Preallocating a good amount of file space is required for the proportional fill algorithm to actually write most of it's data to the new file.
Once you have the filegroups configured the way you want them, you can re-balance the data between the files by rebuilding tables and indexes, ensuring you specify the correct filegroup in the
ON ....clause for each table and index being rebuilt.As an aside, get in the habit of always specifying the maximum file size, and managing that purposefully. Setup alerts in SQL Server Agent so you get alerted as files fill up. When you're thinking about maximum file sizes, evaluate a sensible file growth increment; one that balances time required to grow the file against the desire to not be continuously growing files. Currently, if I think a filegroup needs 10 GB of disk, and I decide to split that into two files of 5GB each, I'd probably set the initial file size at 3GB for each file, with filegrowth increment set to 250MB or 500MB, and I'd set the maximum file size for each file at, say, 6GB just to give a bit of leeway. Then I'd setup an event to warn when any file goes over 80% full, to give you time to provision more disk space. My answer here shows an example of setting up an alert to monitor log file growth.