Posted by Erland Sommarskog on 09/15/07 21:48

bobdurie@gmail.com (bobdurie@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%20SQL%20Ser
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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]