[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

SQL Server files & filegroups

Hi

We have a database that is growing quite large with legacy data, we are archiving the old data into archive tabes within the same database. The data has to be available for reporting.

It has been suggested that these archive tables can point to different files than the main database.

How is this done - I can create a new file, but how do I get the data from the archive tales to use it.

Andy
0
Andy Green
Asked:
Andy Green
  • 4
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
steps:
1) create a new filegroup
2) create a new file into this filegroup
3) move the table to the new filegroup
  => to perform that step, you use the "clustered index trick"
  a) if the table has already a clustered index: drop it, and recreate it and specify the new filegroup
  b) if the table does not have a clustered index, create a clustered index on the filegroup
0
 
Andy GreenAuthor Commented:
I have done as you suggest, yet when I do a properties on the table, the storage still says Primary not Archive as I'd expect.

If I look at the properties for the index that too says Primary.

Andy
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then you did not create the clustered index correctly.
please show how you created the index
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Andy GreenAuthor Commented:
Opened the table in design mode
Clicked TableDesigner
Clicked Index Keys

from the pop up I clicked Add
In the section Data Space Specification I selected Archive.

If I go back into this it says Archive, its just the properties that report wrong.

I have refreshed.

Andy
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to "SAVE" those changes, otherwise it will not be effective.
0
 
Andy GreenAuthor Commented:
Sorted it, rather than open the table in Design, I added the index by opening the table dropdown | Indexes | Add.

Andy
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
good :)
glad I could help
0
 
Andy GreenAuthor Commented:
Thanks for your help

Andy
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now