Share via

Split sqlserver database in 2012

Avyayah 1,291 Reputation points
2022-04-11T17:06:36.927+00:00

What is the process to split sqlserver database to multiple files. Our sqlserver database is 444 GB and need to split to improve performance issue

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments No comments

Answer accepted by question author

AmeliaGu-MSFT 14,016 Reputation points Microsoft External Staff
2022-04-12T03:00:40.137+00:00

Hi SahaSaha-5270,

In addition, please also check:
https://www.mssqltips.com/sqlservertip/6183/splitting-a-sql-server-table-over-multiple-files/
https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/split-a-file-group-into-multiple-data-files/ba-p/371344

Best Regards,
Amelia


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Was this answer helpful?

0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator
    2022-04-11T21:45:50.503+00:00

    If you simply want add more files to the primary file group, it would be sufficient to rebuild the indexes, I think. Possibly you may have to do that more than once.

    If the idea is to create new filegroups, you can move an index with

    CREATE [CLUSTERED] INDEX  ix ON tbl(col1, col2) WITH (DROP_EXISTING = ON) ON NewFileGroup
    

    The definition of the index should be the same as the existing one.

    However, you cannot use LOB data this way. The only option in that case is to create a new table and copy over.

    Like Tom, I question that you will see much benefit. But, admittedly, some people consider it to be best practice to never have user tables in the PRIMARY file group, but place them elsewhere. Myself, being a simpleminded developer, thinks that life easier with only two files per database, data file and log file.

    Was this answer helpful?

    0 comments No comments

  2. Tom Phillips 17,786 Reputation points
    2022-04-11T17:15:59.333+00:00

    With modern SAN technology, you will rarely see a performance benefit from multiple files. Especially if it is using the same IO channel.

    If you are going to do it, you create the files and then drop/recreate your clustered indexes.

    Was this answer helpful?


Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.