August 3, 2015 at 9:34 am
Welsh Corgi (8/3/2015)
How is this?Can you see how it could be Improved?
DECLARE @NextIDs TABLE(UniqueID int primary key)
DECLARE @ThreeYearsAgo datetime
SELECT @ThreeYearsAgo = DATEADD(d, -(3 * 365), GetDate())
as a start, from the top, why are using 365 days per year?
DECLARE @mydate datetime = '2014-08-01'
SELECT @mydate as mydate
SELECT WELSH_ThreeYearsAgo = DATEADD(d, -(3 * 365), @mydate)
SELECT JLS_ThreeYearsAgo = DATEADD(year, -3, @mydate)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 3, 2015 at 9:36 am
Next question, how many rows in a normal batch?
😎
August 3, 2015 at 9:40 am
Welsh Corgi (8/3/2015)
Jayanth_Kurup (8/2/2015)
seems fairly simple , i would personally use partitioning since it handles the whole process with far less stress on the DB but it would be just as easy to create an ssis package to do the same. If you could share the table structure I am sure I can drum something up in a few hours.Here is the table structure. It is ugly. I did not create it.
Thank you for your help.
CREATE TABLE [dbo].[tblCall](
[Call_ID] [int] NOT NULL,
[Call_Date] [datetime] NULL,
[Call_Time] [datetime] NULL,
[Operator_ID] [smallint] NULL,
[Reviewer_ID] [char](30) NULL,
[Insurer_ID] [int] NULL,
[DivisionID] [int] NULL,
[Insurer_Name] [char](50) NULL,
[Insurer_Approval_Comments] [text] NULL,
[Insurer_Daily_Comments] [text] NULL,
[Adjuster] [char](30) NULL,
[CSR_ID] [char](30) NULL,
[CSR_Name] [char](30) NULL,
[CSR_Phone] [char](50) NULL,
[CSR_Ext] [char](10) NULL,
[CSR_Conf] [char](1) NULL,
[CSR_Conf_Date] [datetime] NULL,
[CSR_Conf_Time] [datetime] NULL,
[Insured_Full_NM] [char](50) NULL,
[Insured_FName] [char](14) NULL,
[Insured_LName] [char](16) NULL,
[Insured_Address] [char](30) NULL,
[Insured_City] [char](20) NULL,
[Insured_State] [char](2) NULL,
[Insured_Zip] [char](10) NULL,
[Insured_HPhone] [char](50) NULL,
[Insured_WPhone] [char](50) NULL,
[Insured_CellPhone] [char](50) NULL,
[Insured_Pager] [char](50) NULL,
[Insured_Email] [char](100) NULL,
[Insured_AltPhone] [char](50) NULL,
[Insured_Comments] [text] NULL,
[ThirdParty_Claimant] [char](50) NULL,
[ThirdParty_Phone] [char](50) NULL,
[ThirdParty_AccessName] [char](50) NULL,
[ThirdParty_AccessPhone] [char](50) NULL,
[Policy] [char](25) NULL,
[Claim] [char](25) NULL,
[Deductible_Amt] [money] NULL,
[Loss_Date] [datetime] NULL,
[Loss_Location] [char](30) NULL,
[Loss_Address] [char](30) NULL,
[Loss_City] [char](20) NULL,
[Loss_State] [char](2) NULL,
[Loss_Zip] [char](10) NULL,
[Loss_Descr] [text] NULL,
[Loss_Comments] [text] NULL,
[CONTR_ID] [int] NULL,
[Contractor_Name] [char](50) NULL,
[Contr_Rep] [char](30) NULL,
[Contr_Fax_Date] [datetime] NULL,
[Contr_Fax_Rec_Date] [datetime] NULL,
[Contr_Fax_Rec_Time] [datetime] NULL,
[Contr_Insured_Date] [datetime] NULL,
[Contr_Insured_Time] [datetime] NULL,
[Contr_Appt_Date] [datetime] NULL,
[Contr_Appt_Time] [datetime] NULL,
[Contr_Comments] [text] NULL,
[Contr_GetJob] [char](1) NULL,
[Contr_Cancellation] [char](1) NULL,
[Contr_Assigned_Date] [datetime] NULL,
[PRISM_Just_Dev] [char](1) NULL,
[PRISM_Frst_Est_Date] [datetime] NULL,
[PRISM_Frst_RevReq_Date] [datetime] NULL,
[PRISM_Received_Date] [datetime] NULL,
[PRISM_Closed_Date] [datetime] NULL,
[PRISM_Inspection_Date] [datetime] NULL,
[PRISM_FollowUp_Date] [datetime] NULL,
[PRISM_Comments] [text] NULL,
[PRISM_Frst_Review_Date] [datetime] NULL,
[PRISM_ClaimClosed_Date] [datetime] NULL,
[Owner_ID] [int] NULL,
[DateRec_ID] [int] NULL,
[Creator_ID] [int] NOT NULL,
[CALL_LST_REV_ID_NB] [int] NULL,
[CALL_LST_REV_SER_NB] [int] NULL,
[CALL_LOCK_ID_NB] [int] NULL,
[CALL_BIX_FILE_NM] [varchar](80) NULL,
[CALL_LST_SENT_DT] [datetime] NULL,
[CALL_RCV_EN_IN] [char](1) NULL,
[Est_System] [char](3) NULL,
[CALL_STAT_CD] [char](20) NULL,
[Contr_WorkAuthorization] [char](1) NULL,
[Contr_EMail] [varchar](80) NULL,
[PRISM_ClaimCancellation] [char](1) NULL,
[Loss_Location_Same] [char](1) NULL,
[Nexus_ID] [int] NULL,
[Loss_Cause] [int] NULL,
[CommitDate_Con] [datetime] NULL,
[CommitDate_Ins] [datetime] NULL,
[CommitDate_Est] [datetime] NULL,
[Insured_Company] [varchar](50) NULL,
[PRISM_Frst_RevRec_Date] [datetime] NULL,
[CommitDate_Rev] [datetime] NULL,
[Type_ID] [int] NULL,
[Has_Been_Cancelled] [tinyint] NULL,
[Claim_Cancelled_Reason] [int] NULL,
[Program_Option_ID] [int] NULL,
[ClientAdminResource] [int] NULL,
[CustSurveyTag] [tinyint] NULL,
[ReinspectTag] [tinyint] NULL,
[RandomNum] [real] NULL,
[BilledClient] [tinyint] NULL,
[BilledContractor] [tinyint] NULL,
[billedContrDate] [datetime] NULL,
[billedAssign] [tinyint] NULL,
[billedAssignDate] [datetime] NULL,
[USAAregionName] [varchar](200) NULL,
[singleTradeListID] [int] NULL,
[IsDirty] [tinyint] NULL,
[custSurveyPendDate] [datetime] NULL,
[callDateFull] [datetime] NULL,
[roofInspectionFlg] [bit] NULL,
[USAAprogramArea] [varchar](200) NULL,
[xactTransactionIDValue] [varchar](50) NULL,
[profileCode] [varchar](5) NULL,
[contrEmailSentFlg] [bit] NULL,
[Reinforcement_FLG] [bit] NULL,
[JobStartOverrideDate] [datetime] NULL,
[THDSentFlg] [bit] NULL,
[AddlEstNeeded] [bit] NULL,
[CheckPointPriceList] [varchar](25) NULL,
[PriceList] [varchar](25) NULL,
[ReferralFlg] [bit] NULL,
[MRPSupplementDate] [datetime] NULL,
[CATAssignment] [bit] NULL,
[RvwFlg] [bit] NULL,
[CMS_NextFollowUpDate] [datetime] NULL,
[TestFlg] [bit] NULL,
[CatCode] [varchar](10) NULL,
[PendingCancellationFlg] [bit] NULL,
[PendingNCFFlg] [bit] NULL,
[ReferralId] [varchar](25) NULL,
[CallCenterRepAssigned] [int] NULL,
[isBeingHeld] [bit] NULL,
[XADataSet] [varchar](100) NULL,
[CC_CATCode] [varchar](30) NULL,
[SymbilityClaimId] [uniqueidentifier] NULL,
[SymbilityClaimAssignmentId] [int] NULL,
[SymbilityIntermediateClaimAssignmentId] [int] NULL,
[Loss_BuildYear] [int] NULL,
[Target_Followup_Date] [datetime] NULL,
CONSTRAINT [PK_tblCall] PRIMARY KEY CLUSTERED
(
[Call_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG1]
) ON [FG1] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__Operato__0CEFF2A1] DEFAULT (0) FOR [Operator_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__Insurer__0DE416DA] DEFAULT (0) FOR [Insurer_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__Deducti__0ED83B13] DEFAULT (0) FOR [Deductible_Amt]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__CONTR_I__0FCC5F4C] DEFAULT (0) FOR [CONTR_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_Contr_Cancella1__82] DEFAULT ('N') FOR [Contr_Cancellation]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__CALL_RC__10C08385] DEFAULT ('N') FOR [CALL_RCV_EN_IN]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_PRISM_ClaimCan2__82] DEFAULT ('N') FOR [PRISM_ClaimCancellation]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_Program_Option_ID] DEFAULT (1) FOR [Program_Option_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_ReinspectTag] DEFAULT (0) FOR [ReinspectTag]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_RandomNum] DEFAULT (rand()) FOR [RandomNum]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_BilledClient] DEFAULT (0) FOR [BilledClient]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_BilledContractor] DEFAULT (0) FOR [BilledContractor]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_billedAssign] DEFAULT (0) FOR [billedAssign]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_IsDirty] DEFAULT (1) FOR [IsDirty]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_roofInspectionFlg] DEFAULT (0) FOR [roofInspectionFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_contrEmailSentFlg] DEFAULT (0) FOR [contrEmailSentFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_THDSentFlg] DEFAULT (0) FOR [THDSentFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD DEFAULT ((0)) FOR [CATAssignment]
GO
ALTER TABLE [dbo].[tblCall] ADD DEFAULT ((0)) FOR [RvwFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD DEFAULT ((0)) FOR [PendingCancellationFlg]
GO
That would be what I call a "W.O.R.M." table. It's actually a form of "audit" table in that once a row is written, it will not be changed. It turns out that I've given a presentation and have implemented a system for partitioning and compacting the partitions prior to making them read-only (once a month is completed). The neat thing about making the partitions read-only is it cuts index maintenance to only the current month, cuts backup times to only the current month, enables easy archival (which is your original request), and also allows for relatively easy "piece-meal" restores if a monthly filegroup goes haywire, and also allows for greatly reduced "get back in business" times if something goes haywire with the server.
The presentation I have on the subject actually has a working example of the conversion of a 4 million row table (which you could easily modify to suit your purposes) as well as the necessary monthly routine to "pack'n'seal" the previous month.
The question that I have is, which edition of SQL Server are you working with here? The system I built in the presentation is for Partitioned Tables, which requires the Enterprise Edition.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2015 at 9:49 am
J Livingston SQL (8/3/2015)
Welsh Corgi (8/3/2015)
How is this?Can you see how it could be Improved?
DECLARE @NextIDs TABLE(UniqueID int primary key)
DECLARE @ThreeYearsAgo datetime
SELECT @ThreeYearsAgo = DATEADD(d, -(3 * 365), GetDate())
as a start, from the top, why are using 365 days per year?
DECLARE @mydate datetime = '2014-08-01'SELECT @mydate as mydate
SELECT WELSH_ThreeYearsAgo = DATEADD(d, -(3 * 365), @mydate)
SELECT JLS_ThreeYearsAgo = DATEADD(year, -3, @mydate)
That is just an example. Sorry to mislead.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 3, 2015 at 9:50 am
Eirikur Eiriksson (8/3/2015)
Quick question, is ISArchive ever used and then how?😎
Not it is not being used.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 3, 2015 at 9:59 am
Jeff Moden (8/3/2015)
Welsh Corgi (8/3/2015)
Jayanth_Kurup (8/2/2015)
seems fairly simple , i would personally use partitioning since it handles the whole process with far less stress on the DB but it would be just as easy to create an ssis package to do the same. If you could share the table structure I am sure I can drum something up in a few hours.That would be what I call a "W.O.R.M." table. It's actually a form of "audit" table in that once a row is written, it will not be changed. It turns out that I've given a presentation and have implemented a system for partitioning and compacting the partitions prior to making them read-only (once a month is completed). The neat thing about making the partitions read-only is it cuts index maintenance to only the current month, cuts backup times to only the current month, enables easy archival (which is your original request), and also allows for relatively easy "piece-meal" restores if a monthly filegroup goes haywire, and also allows for greatly reduced "get back in business" times if something goes haywire with the server.
The presentation I have on the subject actually has a working example of the conversion of a 4 million row table (which you could easily modify to suit your purposes) as well as the necessary monthly routine to "pack'n'seal" the previous month.
The question that I have is, which edition of SQL Server are you working with here? The system I built in the presentation is for Partitioned Tables, which requires the Enterprise Edition.
There are 3 file groups and 6 files.
It makes it a little confusing.
I have only created partitioned tables in Oracle for new tables.
I'm using the Enterprise Edition.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 3, 2015 at 10:08 am
Welsh Corgi (8/3/2015)
There are 3 file groups and 6 files.
Not that it will matter much but for what? The database or that one table?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2015 at 10:21 am
Jeff Moden (8/3/2015)
Welsh Corgi (8/3/2015)
There are 3 file groups and 6 files.Not that it will matter much but for what? The database or that one table?
That is for the database.
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 4, 2015 at 7:59 am
Then you might want to consider the methods in the attached presentation. It's definitely worth going through the presentation in the "present" mode because it's somewhat animated to explain some of the more serious problems in partitioning WORM tables.
It also includes full up, documented code to demonstrate it all, which could be modified to your table situation. After the table is partitioned by month, the it's a simple matter of using SPLIT to drop off a month at the temporal beginning of the table. The good part about this is that once you complete an initial backup of each month's filegroup, you only ever have to do a backup on the current month, which saves a ton of time and space not to mention that you'll no longer need to do index maintenance to the larger part of the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2015 at 11:06 am
Thanks Jeff.
When I click on the like it directs me to the main forum page.
I'm going to probably archive year.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 4, 2015 at 12:16 pm
Welsh Corgi (8/4/2015)
Thanks Jeff.When I click on the like it directs me to the main forum page.
I'm going to probably archive year.
Thanks again.
You click on the attachment link and not on the links in my signature, correct?
Also, if you're going to archive by year, I wouldn't bother with partitioning because you're not doing much for indexes and recoverability if you partition by year. It would be far better (IMHO) to partition and archive by month. It will also make that DA panic restore from the archives to find that one important phone call during a given month a whole lot easier, faster, and use a whole lot less diskspace in doing so.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2015 at 12:17 pm
Ah... the other possible problem that you may be having for the download of the attachment is the possibility of blocked downloads by your company policy.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2015 at 12:47 pm
I clicked on it this time and opened as a new window and it worked.
Thanks a bunch!:cool:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 5, 2015 at 10:25 am
@jeff Moden
What is "but there can be a lot of other baggage that goes along with it" referring to when you mentioned it in your comment.... I was just going over splitting and thought it might me a great way to archive the data. Any clarification you may be able to provide on this topic would be greatly appreciated.
August 5, 2015 at 11:22 am
ARC211 (8/5/2015)
@jeff ModenWhat is "but there can be a lot of other baggage that goes along with it" referring to when you mentioned it in your comment.... I was just going over splitting and thought it might me a great way to archive the data. Any clarification you may be able to provide on this topic would be greatly appreciated.
Great question. I've actually covered most of the "baggage" in the presentation I attached to my previous post.
One of the big "baggage" problems is when you try to do the right thing on WORM tables (think Audit tables or, as in this thread, a telephone log, where each row is written once and then never modified) where you split out each month's rows into a separate file in a separate file group. Of course, since the file will never be written to again, it makes sense to do a final reindex and then make the filegroup read-only so that backups and index maintenance is simply no longer needed for that filegroup.
The problem is that any index of 128 Extents (that's only 8MB, BTW) will first be rebuilt and only after the new index has been committed will the old index be dropped. REORGANIZEing instead of REBUILDing is one possible work around but there still may be gobs of free space in the filegroup that you're getting ready to make permanently unavailable.
Other "baggage" includes the problem that occurs with any unique key index. The partitioning column will automatically be added to any and all unique indexes unless you create a "non-aligned" index, which is a separate index that's not part of a table partitioning. Doing such a thing obviously complicates restores but it also makes SPLIT impossible unless you first drop the non-aligned index. Of course, adding the partitioning column to the unique key index suddenly makes the index non-unique insofar as the original key goes. That also means that any FK pointing to the table (usually not done with WORM tables) can no longer be. You can make the partitioning column be the PK (for example, an IDENTITY column) but that's not normally what happens an also complicates matters for monthly grouping because you cannot predict what the final IDENTITY will be for a month (which could cause serious data movement when you create the new partition) not to mention the fact that IDENTITY values are not necessarily temporally sequential according to, say, the date and time that a call occurred.
There's also the problem of how to do the final month end processing without making the data unavailable for more than several seconds especially if you intend to do a final index rebuild and free space compaction prior to making the now previous-month's filegroup read only.
There are work arounds for all of the above but you end up even fighting the fact that SELECT INTO will only create a new table on the primary FG, which is NOT where you ultimately want your archive data to live.
Again, I've worked through and solved ALL of those problems (except for the FK stuff, which I didn't need for my WORM tables) and have included what all the "baggage" caveats and work arounds are in the presentation I attached.
Heh... like I've told people in the past, if you think that partitioning a table, even a WORM table was easy, then you've probably done something wrong. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply