My SQL server fact tables are incrementally being updated with reference to a linked server. The queries run routinely on a regular basis and have been executing successfully for a long time. The execution time for all queries combined is below 5 minutes. Recently the updates failed because the primary file group reached its limit, even though I set file growth to 'unlimited'.
I thought it would be helpful to move my fact tables to the secondary file in the same file group of my database to allow for growth. I also shrank the database which reduced file sizes (especially the log file) substantially.
I found that my queries were now running extremely slow, some taking up to 2 hours where they had before run in 5 minutes.
I have now moved them back to the database's primary file and completion time is now back to normal. I do not however understand what went wrong with the distribution of the tables among the database files.
My queries are running because the log file is smaller, but I need to allow for future growth and would like to place some tables in the secondary file if I run out of space again.
Best Answer
The problem was not on my database as I thought, but on the system's tempdb. I added some additional files that point to a different location to the tempdb. This resolved the space problem. Thanks to Denis for resolving the performance issue for me.