I have a database that contains log files for an internet bank. The log tables are very large, and I've been asked to create a "sliding window" partition schema for the database, partitioning each table per quarter I've looked at this sample,
http://msdn.microsoft.com/en-us/library/aa964122.aspx , but have decided on a slightly different approach.
To create a new filegroup for the next quarter I've written the following procedure:
create procedure proc_createFilegroup ( @filegroupName nvarchar(max),
@name nvarchar(max),
@path nvarchar(max),
@size nvarchar(max))
as
declare @filename nvarchar(max),
@execStrFilegroup nvarchar(max),
@execStrModifyFilegroup nvarchar(max),
@execStrFile nvarchar(max)
set @filename = @path + @filegroupName + '.ndf'
set @execStrFilegroup = 'Alter database MyLog add filegroup ' + @filegroupName
--print @execStrFilegroup
exec (@execStrFilegroup)
set @execStrFile = 'Alter database MyLog Add File (name = ' + @name
+ ', Filename = ''' + @filename +
+ ''', Size = ' + @size + ') To Filegroup ' + @filegroupName
--print @execStrFile
exec (@execStrFile)
In addition, I've created the following partition function:
create partition function partfunc_quarter (datetime)
as range right for values ('20070101', '20070401', '20070701', '20070901')
And the following partition scheme:
create partition scheme partscheme_quarter
as partition partfunc_quarter
to (fg_06_4, fg_07_1, fg_07_2, fg_07_3, fg_07_4)
And use a create table statement similar to this:
Create MyTable (LogId int,
Created datetime)
ON partscheme_quarter
Now, for each new quarter, I will:
- Execute the SP to create a new filegroup
- Issue an alter partition function:
Alter partition function partfunc_quarter() split range('20080101')
- issue an alter partition scheme:
Alter partition scheme partscheme_quarter next used fg_08_1
I believe I'm on the right track here, but would appreciate any input on whether I've left anything out. The most recent quarter data is naturally what will be most heavily used at any time, so this must be the most readily accessible.
Start Free Trial
