Solved

SQL Server files & filegroups

Posted on 2013-02-07
8
230 Views
Last Modified: 2013-02-07
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
Comment
Question by:Andy Green
  • 4
  • 4
8 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38863894
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
 
LVL 3

Author Comment

by:Andy Green
ID: 38863926
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38863942
then you did not create the clustered index correctly.
please show how you created the index
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Author Comment

by:Andy Green
ID: 38863964
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38863989
you have to "SAVE" those changes, otherwise it will not be effective.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 38864039
Sorted it, rather than open the table in Design, I added the index by opening the table dropdown | Indexes | Add.

Andy
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38864061
good :)
glad I could help
0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 38864078
Thanks for your help

Andy
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question