variables in constraints, scripting partition sliding

Hi,

I'm trying to create a bunch of scripts that will automatically roll
the partitions of one of my database tables. The partition scheme can
be arbitrary, but for my purposes i'm testing with a scheme that
breaks on "days", has 5 partitions, and every day would cycle out the
oldest, and bring in a new one.

Question #1:
I've got the scripts to do the rolling generic enough, except for when
i'm adding a CONSTRAINT to the staging table. Here's what i want to
do:


-- get a date var for today with time cleared
declare @year as nchar(4);
select @year = datename(year, current_timestamp);
declare @yearstart as nchar(10);
select @yearstart = @year + N'0101';
declare @today as nchar (64);
select @today = dateadd(dy, datepart(dy, current_timestamp) - 1,
@yearstart);
--select @today;

-- set the date period to be two days from now
alter table [partitionedlogs-staging]
with check
add constraint aftertoday
check ([logdate] >= @today
and [logdate] < dateadd(dy, 1, @today))
go


Of course, this doesn't work because i think its trying to make the
constraint variable:
Variables are not allowed in the ALTER TABLE statement.

But in reality, i want that date to be FIXED at the time of writing
the constraint. I can programattically generate the script with fixed
values, but if i can avoid that it would preferred. Can this be done
with stored procedures perhaps?

Question #2
Perhaps of more importance, is how i actually automate running of
these scripts on a daily basis. Does sql server have built in
scheduled tasks or running of scripts to facilate this? I haven't
found any.

I am by no means a DBA nor a sql server novice, but i have managed to
do this process manually. I only need to POC this to determine if it
is possible. Any help or guidance that can be offerred would be most
appreciated.

Thanks!

Bob

Re: variables in constraints, scripting partition sliding

bobdurie@gmail.com (bobdurie@gmail.com) writes:
> alter table [partitionedlogs-staging]
> with check
> add constraint aftertoday
> check ([logdate] >= @today
> and [logdate] < dateadd(dy, 1, @today))
> go
>
>
> Of course, this doesn't work because i think its trying to make the
> constraint variable:
> Variables are not allowed in the ALTER TABLE statement.

This is a fair game for dynamic SQL. But first let me change how
you compute @today a bit:

declare @today as char (8);
select @today = convert(char(8),
dateadd(dy, datepart(dy, current_timestamp) - 1, @yearstart), 112)

This give you the format YYYYMMDD which is never subject to different
interpretations due to dateformat or language settings.

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 + '))')

Note here that I also added the date to the constraint name, as the
constraint name must be unique in the schema.

Now that you have seen dynamic SQL in action, you are likely to use it
all over town. Permit me therefore to point out that while this is a
useful feature, it also lends it to all sorts of abuse, and I have a
long article on dynamic SQL on my web site that you should read before
you start to use it wildly: http://www.sommarskog.se/dynamic_sql.html.

> Question #2
> Perhaps of more importance, is how i actually automate running of
> these scripts on a daily basis. Does sql server have built in
> scheduled tasks or running of scripts to facilate this? I haven't
> found any.

Yes, you can run jobs from SQL Server Agent. You find it in the Object
Explorer in the bottom of the tree for the server.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Re: variables in constraints, scripting partition sliding

On Sep 14, 5:33 pm, Erland Sommarskog wrote:
> bobdu...@gmail.com (bobdu...@gmail.com) writes:
> > alter table [partitionedlogs-staging]
> > with check
> > add constraint aftertoday
> > check ([logdate] >= @today
> > and [logdate] < dateadd(dy, 1, @today))
> > go
>
> > Of course, this doesn't work because i think its trying to make the
> > constraint variable:
> > Variables are not allowed in the ALTER TABLE statement.
>
> This is a fair game for dynamic SQL. But first let me change how
> you compute @today a bit:
>
> declare @today as char (8);
> select @today = convert(char(8),
> dateadd(dy, datepart(dy, current_timestamp) - 1, @yearstart), 112)
>
> This give you the format YYYYMMDD which is never subject to different
> interpretations due to dateformat or language settings.
>
> 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 + '))')
>
> Note here that I also added the date to the constraint name, as the
> constraint name must be unique in the schema.
>
> Now that you have seen dynamic SQL in action, you are likely to use it
> all over town. Permit me therefore to point out that while this is a
> useful feature, it also lends it to all sorts of abuse, and I have a
> long article on dynamic SQL on my web site that you should read before
> you start to use it wildly:http://www.sommarskog.se/dynamic_sql.html.
>
> > Question #2
> > Perhaps of more importance, is how i actually automate running of
> > these scripts on a daily basis. Does sql server have built in
> > scheduled tasks or running of scripts to facilate this? I haven't
> > found any.
>
> Yes, you can run jobs from SQL Server Agent. You find it in the Object
> Explorer in the bottom of the tree for the server.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

This advice was very helpful, thank you!!! I tested what you
suggested and works great. 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%20Server%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.

Thank you so much for your help!!!

Bob

Re: variables in constraints, scripting partition sliding

bobdurie@gmail.com (bobdurie@gmail.com) writes:
> On Sep 14, 5:33 pm, Erland Sommarskog 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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: variables in constraints, scripting partition sliding

On Sep 15, 5:48 pm, Erland Sommarskog wrote:
> bobdu...@gmail.com (bobdu...@gmail.com) writes:
> > On Sep 14, 5:33 pm, Erland Sommarskog 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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!