|
|
You're in the
sql server (alternate)
group:variables in constraints, scripting partition sliding
sql server (alternate):
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
bobdurie@gmail.com (bobdurie@gmail.com) writes:
[quoted text, click to view] > 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. [quoted text, click to view] > 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/downloads/books.mspx Books Online for SQL Server 2000 at
[quoted text, click to view]
On Sep 14, 5:33 pm, Erland Sommarskog <esq...@sommarskog.se> 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/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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
bobdurie@gmail.com (bobdurie@gmail.com) writes:
[quoted text, click to view] > 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: [quoted text, click to view] >> 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. [quoted text, click to view] > One final question. I'm concerned that http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Ser> when i get my job up and running that it will not be very fault > tolerant. I followed the tips in the following paper: > ver%202005%20Beta%20II.htm#_Toc79339947 [quoted text, click to view] > > 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
[quoted text, click to view]
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!
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |