September 15, 2008 at 2:36 pm
I have a database that is getting out of control on size because the administrators want to keep data for 6 months just in case something is lost when it is transferred to another system. We would like to clip this data at 3 months but they are too worried something will be lost. In an effort to placate them and control the size issue, I was planning on adding another file group and then having their regular purge process take effect on records that are 3 months out. The trick I was going to do is have a trigger setup on these tables to insert upon deletion into these temp tables on this new file group. These archive tables would then hold the bulk of the data on a temporary drive that our SAN guy can recover after the system admins are satisfied. So...
Add drive for temp storage.
Add another file group only for these extra temp tables for archiving.
Add archive tables on new file group.
Add trigger to insert upon delete.
Start purge.
Time passes...sys admins are happy everything is working ok and we can then purge correctly...
delete trigger
delete archive tables
delete file group
SAN guy reclaims drive
celebrate that this whole shebang is over! 🙂
So, how does that sound?
It is my understanding that you can add all the files you want to a file group for a particular database, but the DBMS manages what goes where inside that particular file group. If you add another file group, you can specify what tables are placed into that file group. So, file groups are for administering where particular data is stored.
This whole thing is so that I can give them storage but take it back quickly and easily without major work done to both the application workflow and database.
September 15, 2008 at 3:16 pm
jason.stephens (9/15/2008)
It is my understanding that you can add all the files you want to a file group for a particular database, but the DBMS manages what goes where inside that particular file group. If you add another file group, you can specify what tables are placed into that file group. So, file groups are for administering where particular data is stored.This whole thing is so that I can give them storage but take it back quickly and easily without major work done to both the application workflow and database.
This is exactly what you want to do. Putting multiple files in the same filegroup is used more when you want to spread I/O across multiple disks for better performace. Setting up a table in a different filegroup allows you to put all the data for that table on a separate disk so it won't be impacting the performance of the original table.
September 16, 2008 at 7:48 am
So, to make this happen through Enterprise Manager, do i just:
Right-Click on specific database > Properties > File Groups Tab
Then just type in a name?
Then do I just add a file to the second file group through the data files tab?
Can you move tables and such back and forth between file groups or do you have to put them there during creation of the objects?
Should I just use T-SQL for this?
September 16, 2008 at 8:01 am
You can use EM or T-SQL. For T-SQL you would use
alter database DBName add filegroup FILEGROUPNAME
go
alter database DBName add file (NAME = 'Name', FILENAME = 'D:\MSSQL\NAME_Data.NDF', SIZE = xxxxxKB, FILEGROWTH = XXXXKB) TO FILEGROUP NewFileGroupName
go
See BOL for more details.
Moving tables to new filegroup instructions can be found here - http://www.sqljunkies.ddj.com/HowTo/B9F7F302-964A-4825-9246-6143A8681900.scuk
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 16, 2008 at 8:08 am
Hi Jason,
Sounds like you require a solid archiving strategy and your suggestion makes sense, but you need to plan this properly and test, as you are planning to "migrate" data from one location to another. You can add another filegroup to your existing database and add new files into this filegroup. You then can create your archiving table on this new filegroup and use a scheduled job with a stored procedure to "migrate" data between your "production" and "archiving" table(s), thus eliminating the requirement for a trigger.
Ideally, you may want to consider creating a seperate archiving database with its own filegroup(s) and associated files as your archiving solution, unless you still have requirements to report from existing database. With this solution, you can create a scheduled job to run on a regular basis to migrate data that exceeds your time limit, thus controlling your database size.
Would this be a temporary solution or an on-going solution, as this is not explained?
Thanks,
Phillip Cox
September 16, 2008 at 8:48 am
First off, let me thank you guys for your responses!
It is supposed to be a temporary solution. The sys admins have already gobbled up more space than they originally said they would need by changing the specifications of the application. The company that produces the application already has a 90 day purge process in place. It is just not turned on at the moment because of the sys admins concerns for lost data when transferring it to ancillary systems. The idea was to limit the impact to the company’s application and database. Since the space we will be giving them will only be theirs for 6 months, I wanted to make it easy to initiate and then easy to disassemble. The trigger was so that they could simply turn on their purge process and the database would handle this transfer of data to the temporary archives. The tables that are being purged are actually the applications archive table, so this would be the archive of the archive tables 🙂
Would it be wiser to just create a separate database? It isn’t necessarily a problem to do either. I just thought that file groups would solve the issue without putting in a whole new database. I guess the work and excess tables would be about the same. The main point here is to keep this temporary space separate from the permanent space so they don’t fancy it theirs. It is nothing but a loaner 😛
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply