Re: variables in constraints, scripting partition sliding

On Sep 15, 5:48 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
bobdu...@xxxxxxxxx (bobdu...@xxxxxxxxx) writes:
On Sep 14, 5:33 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> 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...@xxxxxxxxxxxxx

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!

.

Relevant Pages

  • Re: variables in constraints, scripting partition sliding
    ... The date should appear in quotes in the constraint definition. ... So that if the script is not run, the table can still accomodate the ... set an upper limit when you create the next partition. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: how to drop index?
    ... coresponding END in your IF Statement, because you are dividing your script ... which is the unit of Parsing and Compilation for SQL Server. ... > I want to check for duplicate data, if got duplicate data, then the ... > tell me the correct way to drop the constraint and index? ...
    (microsoft.public.sqlserver.server)
  • Re: Run as a transaction?
    ... What you need to do is upload to a temp, or more likely working table and then run a background job to migrate the records to your main table a little at a time. ... SQL Server DBA in Sacramento, ... I create a script locally using the "Database Publishing Wizard" to script that one table only and save it to a file "1.SQL". ... ALTER TABLE.DROP CONSTRAINT ...
    (microsoft.public.sqlserver.programming)
  • Re: sql script
    ... You need to drop the PK constraint, drop the existing index, create the new ... clustered index then create the PK constraint. ... > i need to write a script so that i can just execute it on the server. ... > do this using the GUI that SQL SErver 2000 provides. ...
    (microsoft.public.sqlserver.programming)
  • Re: Run as a transaction?
    ... Since the live table is just a static reference table only I guess I could just upload to "tblLanguageValues_New" then drop "tblLanguageValues" then rename "tblLanguageValues_New" to "tblLanguageValues". ... Join the Sacramento SQL Server User Group on LinkedIn ... I create a script locally using the "Database Publishing Wizard" to script that one table only and save it to a file "1.SQL". ... ALTER TABLE.DROP CONSTRAINT ...
    (microsoft.public.sqlserver.programming)