|
|
Posted by bobdurie@gmail.com on 09/19/07 14:56
On Sep 15, 5:48 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> bobdu...@gmail.com (bobdu...@gmail.com) writes:
> > On Sep 14, 5:33 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> >> The dynamic SQL is simple enough:
>
> >> EXEC('alter table [partitionedlogs-staging]
> >> with check
> >> add constraint after ' + @today ' + '
> >> check ([logdate] >= ' @today ' + '
> >> and [logdate] < dateadd(dy, 1, ' + @today + '))')
>
> But not that simple. It should read:
>
> >> EXEC('alter table [partitionedlogs-staging]
> >> with check
> >> add constraint after ' + @today + '
> >> check ([logdate] >= ''' @today + '''
> >> and [logdate] < dateadd(dy, 1, ''' + @today + '''))')
>
> The date should appear in quotes in the constraint definition.
>
> > One final question. I'm concerned that
> > when i get my job up and running that it will not be very fault
> > tolerant. I followed the tips in the following paper:
>
> http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQ...
> ver%202005%20Beta%20II.htm#_Toc79339947
>
>
>
> > The steps all work, but the partitions that are being dropped/added
> > are always based on 'today'. If the job doesn't run for a few days,
> > i'm quite certain it will fail. Is there some way to query partition
> > function information in order to get the ranges so that multiple days
> > sliding can be done? Does this question even make sense? If it does,
> > let me know if you have any tips.
>
> I think the question makes very much sense! Far too often solutions based
> that something is run every day or similar, fails to consider the risk that
> the job is not run on one more days for some reason.
>
> One observation here is that when you create the constraint for @today,
> it should probably simply be:
>
> CHECK logdate >= ''' + @today + ''')
>
> So that if the script is not run, the table can still accomodate the
> data for coming days. The script would then change this constraint to
> set an upper limit when you create the next partition.
>
> There are two ways to retrieve the most recent day the script was
> done. One is to examine the partition function by looking in
> sys.partition_function and sys.partition_range_values. There is
> also a more direct way, with the naming scheme that I used:
>
> SELECT MAX(name)
> FROM sys.objects
> WHERE name LIKE 'after%'
> AND type = 'C'
>
> If you make the constraint name distinctive enough you can rely on them.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks again for the help. The dynamic sql worked like a charm, the
double quotes are very important in that without them, it won't let
you switch the new table in because it doesn't recognize that
constraint as limiting enough for the given partition and you get an
error.
I've got the script working in an sql server agent job nightly for
testing, and seems to be ok. I made the script work in a loop and
basically do its work for the given number of days since the partition
schemes lastmodifieddate. Its not foolproof, if someone makes changes
to the partition scheme and the lastmodifieddate is updated, this will
break, but for my purposes it works great! It should work if there is
a prolonged outage too, although to test this i'll need to bring the
db down for a few days.
If anyone is curious to see the script please contact me, i'll have to
filter out the proprietary schema elements so its a bit of work, but
i'm willing to do it if someone wants to see it.
Thanks again!
[Back to original message]
|