2011 Attachment Table to different SQL FileGroup

(0) Share

Report

Posted on by Community Member

Hi,

Our CRM database is beginning to grow quite quickly. From inspection the majority of the space is being used by the Attachment table - 14GB/24GB.

Is it possible to move this table to a different FileGroup in the database to separate out the SQL memory and storage issues?

Thanks,

Henry

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Mohammad Atif on at

Like (0)

Report

I found other customer's asking the same question in MSDN social.microsoft.com/.../d6830ef0-2f5d-43c6-a4ca-cf9f76425492. Please see if the workaround suggested here works for you, if not please let me know

Thanks,

Mohammad

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Mohammad,

That thread doesn't really help. Our crm is installed in a virtualized environment and the storage is san-based. We are using RAID 6 with a mixture of 7.2k and 15k drives.

The server has 16gb of ram which means if we could separate the attachment table out into a different filegroup then the rest of the database could be loaded into memory.

Is it supported to move the attachment table to a different filegroup?

Thanks,

Henry

Was this reply helpful? Yes No
Suggested answer

Muhammad Adeel Javaid 5,580 on at

Like (0)

Report

In MS SQL Management Studio click "Tools - Options - Designer" and UNcheck the "Prevent saving changes that require table re-creation" box.

1. Right-click the table and select "Design" for SQL 2008 or "Modify" for SQL 2005.

2. Press F4 to open the "properties" window.

3. Make sure that you have the table (!) selected on top of the "properties" window.

4. Change the "text filegroup" for the table.

5. Wait! DO NOT save your changes. The SQL-server most likely will throw a timeout error for your large table.

6. Instead choose "Generate change script" and copy the resulting text to the new query window.

7. Run the query.

It will take some considerable time depending on the size of tables to move to new location.

Regards

Was this reply helpful? Yes No
Suggested answer

Muhammad Adeel Javaid 5,580 on at

Like (0)

Report

For SQL 2005 refer to this link:

decipherinfosys.wordpress.com/.../moving-tables-to-a-different-filegroup-in-sql-2005

Was this reply helpful? Yes No
Mohammad Atif on at

Like (0)

Report

Table portioning and moving that to different file group is SQL related operations. I don't see a Document on this from the CRM side but I think we can do this if the database size is very large. I will recommend doing this in test environment and then going for the Production after taking the database backup.

Thanks,

Mohammad

Was this reply helpful? Yes No