July 28, 2011 at 7:44 am
Hello Friends ….I’ve a table which has 4 partitions and those partitioned table are purged by a job every 16 hours. So If I query the table I should see 64hrs data as those 4 partitions are purged every 16 hours but In my case I get some records for 2009 and also 2010 as well….not really sure how this is happening. Could you please help me solving this issue?
July 28, 2011 at 8:07 am
Can you post partition function and scheme scripts?
You can script them from the DB>Storage folder.
July 28, 2011 at 8:57 am
you may also want to add your purge script, used every 16h to let us have a look how you are trying to do it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 28, 2011 at 10:28 am
Here is the script from the job...
CREATE PROC [dbo].[SPTEST]
AS
DECLARE @CMD0 nvarchar(2000)
DECLARE @CMD1 nvarchar(2000)
DECLARE @CMD1A nvarchar(2000)
DECLARE @CMD2 nvarchar(2000)
DECLARE @CMD3 nvarchar(2000)
Declare @CurrentFG nvarchar(20)
Declare @CurrentExitTable nvarchar(30)
DECLARE @MergeDate datetime
DECLARE @SplitDate datetime
DECLARE @MergeDates nvarchar(30)
DECLARE @SplitDates nvarchar(30)
--Get the oldest boundary. Set the new boundary to NOW.
Set @MergeDate = Convert(DateTime,(Select min(value) from sys.partition_range_values A Join sys.partition_functions B ON (A.function_id = B.function_id)))
SET @SplitDate = (GETDATE())
Set @MergeDates = (Convert(nvarchar(30),@MergeDate,121))
Set @SplitDates = (Convert(nvarchar(30),@SplitDate,121))
--Locate the filegroup that needs to be cleaned.
Set @CurrentFG =
(SELECT
fg.name AS FileGroupName
FROM (((sys.tables AS t
INNER JOIN sys.indexes AS i
ON (t.object_id = i.object_id))
INNER JOIN sys.partition_schemes AS ps
ON (i.data_space_id = ps.data_space_id))
INNER JOIN sys.destination_data_spaces AS dds
ON (ps.data_space_id = dds.partition_scheme_id))
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
WHERE (t.name = 'tests') AND (i.index_id IN (0,1)) AND
dds.destination_id = $partition.SystemEventsPartitionFunction(@MergeDate))
--Set the name of the Exit Table
If @CurrentFG = 'SysEventsFG1' Set @CurrentExitTable = 'tblSystemEventsPartFG1'
If @CurrentFG = 'SysEventsFG2' Set @CurrentExitTable = 'tblSystemEventsPartFG2'
If @CurrentFG = 'SysEventsFG3' Set @CurrentExitTable = 'tblSystemEventsPartFG3'
If @CurrentFG = 'SysEventsFG4' Set @CurrentExitTable = 'tblSystemEventsPartFG4'
--Dynamic SQL does the following:
--1. Creates the Exit Table.
--2. Switch the data in the "Main" table to the exit table.
--3. Merges the leftmost boundry in the partition function.
--4. Sets the Next Used fileGroup.
--5. Splits the right most boundry (Add a future date to the function).
--6. Truncates, then Drops the Exit Table
Set @Cmd0 = '
CREATE TABLE [dbo].['+@CurrentExitTable+'](
[fldWorkflowInstanceID] [nvarchar](50) NOT NULL,
[fldContactNumber] [bigint] NULL,
[fldAccountID] [bigint] NULL,
[fldNodeID] [uniqueidentifier] NOT NULL,
[fldStartTime] [datetime] NOT NULL,
[fldEndTime] [datetime] NOT NULL,
[fldAgentID] [nvarchar](100) NOT NULL,
[fldStationID] [nvarchar](50) NOT NULL,
[fldSystemEventTypeID] [int] NOT NULL,
[fldServerName] [nvarchar](100) NOT NULL
) ON ['+@CurrentFG+']
'
SET @Cmd1 = '
ALTER TABLE [dbo].[Events]
SWITCH PARTITION 2
TO ['
Set @Cmd1A = ']
ALTER PARTITION FUNCTION SystemEventsPartitionFunction()
MERGE RANGE ('''
Set @CMD2 = ''')
ALTER PARTITION SCHEME [SystemEventsPartitionScheme] NEXT USED ['
SEt @Cmd3 = ']
ALTER PARTITION FUNCTION SystemEventsPartitionFunction()
SPLIT RANGE ('''
--Uncomment To Test:
--Select (@CMD0 + @CMD1 + @CurrentExitTable +@CMD1A+ @MergeDates + @CMD2 + @CurrentFG + @CMD3 + @SplitDates + ''')'+ ' Truncate table dbo.' + @CurrentExitTable + ' Drop table dbo.' + @CurrentExitTable)
Exec (@CMD0 + @CMD1 + @CurrentExitTable +@CMD1A+ @MergeDates + @CMD2 + @CurrentFG + @CMD3 + @SplitDates + ''')'+ ' Truncate table dbo.' + @CurrentExitTable + ' Drop table dbo.' + @CurrentExitTable)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply