Partitioning Archiving

Feb 21, 2007

Hello,

I have current events going to a log, and I'm implementing partitioning it into weeks using the following function...

CREATE PARTITION FUNCTION [trackPointLogWeekPF](int)
AS RANGE LEFT FOR VALUES (7, 14, 21, 28)

and in the table create I add an extra field of day number to pass to the function...
[intPartitionDayNum] AS (datepart(day,[dtTrackPointTime]))

So if that's all for the current month, is it possible to have monthly partitions for the older data so that I could drop off a month from a year ago for example or would I need to keep it weekly?

Thanks for any help.

View 1 Replies

Archiving

Feb 17, 2007

Hi All,
I'm building an archiving applicaition for my small organization, but I have many file types such as videos, photos, texts, pdfs.... and some huge file sizes of about 500 MB.
My questions are:
1. Somefriend told me that there is a hardware compression device which can compress allready compressed videos and JPEGs. Is it correct? If so, where can I surf to find such devices?
2. Which datatype have I to use with SQL Server 2000 to handle all the different file types and sizes?

Thanks very much
Haytham

View 3 Replies View Related

Archiving Table

Feb 14, 2002

Hi,

We have a table of size greater than 200GB, so all the SQL is very slow.

Is there any way of doing table partition, If so How?

Is there any better way of archiving a portion of table used currently which can be restored later when is required, If so How ?

Thanks
John Jayaseelan

View 5 Replies View Related

Archiving Data

Aug 2, 2000

Hi everyone!

My problem is, that i don#t know how i can archive the data. That means to documentate when, who, etc. changed the data (in a seperate table).
I tried to solve it with different triggers.

Thanks in advance,

Maria.

View 1 Replies View Related

Data Archiving

Oct 6, 2004

Hi ,

I need to archive my production database to a new Server.....

Is it possible to move data using INSERT INTO ServerName.DBName.dbo.TableName from the current Database Server!!!

Do I need to create a linked server to do this....or shoud I go for DTS..

Thanks
Cheriyan.

View 1 Replies View Related

Data Archiving

Jan 11, 2007

What's the best archiving procedures? :)

SlayerS_`BoxeR` + [ReD]NaDa

View 2 Replies View Related

Archiving Database

Jan 22, 2008

Hi,

Actually i have done a program (vb.net) to archive the database.

Due to some policy issues, i not allow putting the EXE file into the database server.

So, i only can using the SQL Agent.

My question is:
(1) Since i already prepare set of program (vb.net), how can I put into the sql agent?

(2) I found some articles, SSIS can be done. How to do it?

How you all have a clear guide for me.

Thanks anyway.

View 5 Replies View Related

Importing And Archiving

Jul 23, 2005

I was wondering how i could use dts to import accessfiles and then archivethem to another folder. I've read some examples on sqldts.com but i stillcan't figure it out.Basically this is what i want:- import an accessfile which has a name like this <companyname>_<today'sdate>.mdb. The importfolder is called in my case d:import- for updating some fields, the dts should use the company's name from theaccessfile and check that with a lookuptable to translate the name into aint value.- after some processing, the dts should place the file to another folder. Inmy case it is d:archiveIf someone can help me with this, i would be very greatful.------------------------------------------------------This mailbox protected from junk email by MailFrontier Desktopfrom MailFrontier, Inc. http://info.mailfrontier.com

View 2 Replies View Related

Archiving Is Not Reducing DB Size

Jan 10, 2005

I created few jobs that would archive the production DB and delete the archived data...
but it looks like the DB size is not reducing!!! Some times it looks like the size has increased!!

I think this is because of the log file size has increaded by the DELETE operations....But what can I do for this???

Please Help!!

View 1 Replies View Related

Moving Records For Archiving

May 18, 2004

I've got two tables, one is an archive of the second (tables are identical). I'd like to migrate records from one to the other (as in, move, insert into one while deleting from the other).

I know I can start a transaction, do an INSERT INTO...SELECT, followed by a DELETE, check rows affected, then closing with a commit transaction (or rollback if the counts don't match), but it seems as though I might be over thinking it. Is this considered the optimal approach?

View 10 Replies View Related

Monthly Archiving Of Tables?

Mar 27, 2014

The Database will hold 2 tables. One of those includes dates. They are joint by a constraint using an ID. What i got to do is, store the tables in a different schema named after the month the data was created. I will have to keep the original tables aktive because this should work while accessing the tables but can flush the data to keep the database small. So i would end up with 2 tables in 1 schema for every month and the productive that keeps track of the current bookings.

I would have access to the enterprise edition if that changes anything at all.

View 3 Replies View Related

Archiving Analysis Server Dbs

Jul 20, 2005

Hello,Does anyone know of a way to schedule the archiving of analysisdatabases? Seems pretty lame if you can't... The only answer I've gottenis "maybe in Yukon"....Thanks.*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Archiving Rows Using A Date Field

Jun 6, 2000

Hi all,

Would anyone be able to point me in the right direction for creating a procedure to archive rows using a DATE field value as the determinant for selecting the rows to be moved to a backup table?

I'd really appreciate any assistance that anyone can provide.

Thanks,
Tom C.

View 1 Replies View Related

Archiving Data To Flat File?

May 24, 2002

How do I put data into a text or excel file before I attempt a deleteion from a large table. I know how to select the necessary data, but i'm not sure about the t-sql required to put it into a file?

are there any better methods of archiving?

thanks

View 3 Replies View Related

Database Automated Part-archiving

Apr 16, 2007

Hi all,
i am a student doing my final year project and i have the duty to develop a automated part-archiving for our MsSql Server 2000 Database, but its not so simple :eek: .

the environment:
- MsSql Server 2000
- Suns App-Server Glasfish
- Java (J2EE)
- Hibernate

the requirements:
1.)
Some tables should be synchronized and redundant (the same data in both databases)

2.)
If the maindatabase is changing the schema the archivedatabse must do the same changes.

3.)
The datas in some tables (the ones that aren't synchronized) which are older then 2 Years should be moving (copy, paste) to the archivedatabase every 24 hours.

4.)
If there are is a access for a data which are not in the maindatabase, it is necassary to get it from the archivedatabase.

I don't find a way to realise this ...
can some one give me a hint?

... Sorry for my bad english, i hope i explained my problem good enough.

Thanks for ur answers

View 10 Replies View Related

SQL 2012 :: Deleting / Archiving From 100 GB Table

May 20, 2014

I would like to archive /delete data from a 100GB table. I have to delete on the basis of date column. Date column has been added to clustered index But not having an individual non clustered index.

My estimated execution plan shows a index scan.

Should I impose an non-clustered index on the date column then try to archive /delete after confirming the index seek is used in estimated execution plan or, is there any other method to do this?

View 3 Replies View Related

Archiving And Deleting Records (Data)?

Aug 10, 2015

I wrote a script to archive and delete records rom a table back in 2005 and 2009.

I can't seem to get the syntax right. Any sample script to simply archive and delete records?

This is what I have so far.

DECLARE @ArchiveDate Datetime
SET @ArchiveDate = (SELECT TOP 1 DATEPART(yyyy,Call_Date)
FROM tblCall
ORDER BY Call_Date)
--SELECT @ArchiveDate AS ArchiveDate
DECLARE @Active bit

[Code] ....

View 9 Replies View Related

Stored Procedure For Archiving Records

Mar 27, 2008

I have two tables called A and B and C. Where A and C has the same schema

A contains the following columns and values
-------------------------------------------
TaskId PoId Podate Approved

1 2 2008-07-07 No
3 4 2007-05-05 No
5 5 2005-08-06 Yes
2 6 2006-07-07 Yes

Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status

1 A 7/7/2007 No
3 B 2/4/2006 Yes

Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and i have the column TaskId as the common column

Pls provide me with full stored procedure code.

C.R.P RAJAN

View 1 Replies View Related

Millions Records Archiving And Delete

Feb 27, 2007

The iussue:Sql 2KI have to keep in the database the data from the last 3 months.Every day I have to load 2 millions records in the database.So every day I have to export (in an other database as historical datacontainer) and delete the 2 millions records inserted 3 month + one day ago.The main problem is that delete operation take a while...involvingtransaction log.The question are:1) How can I improve this operation (export/delete)2) If we decide to migrate to SQL 2005, may we use some feature, as"partitioning" to resolve the problems ? In oracle I can use the "truncatepartition" statement, but in sql 2005, I'm reading, it cant be done.This becouse we can think to create a partition on the last three mounts tosplit data. The partitioning function can be dinamic or containing afunction that says "last 3 months ?" I dont think so.May you help usthank youMastino

View 5 Replies View Related

Super Dumb Question (Archiving)

Dec 12, 2007

Hi,

This is probably a no-brainer for most of you, but I'm really really new to sql.

I'm using ms sql 2005 (I think), and I'm making a scheduling program in VB that uses a database hosted on the sql server. Does sql come with archiving tools, or will I need to make something? All the other DBs on the server are 3rd party apps that have a little archiving tool as part of the utilities package. Will I have to make something like that to remove and save the old schedule entries? If so, can anyone point me in the right direction?

Thanks,
Chris

View 9 Replies View Related

Data-archiving And Purging Strategy

Mar 31, 2008

Regarding SQL Server data, I am looking to implement the beset Data-Archive and Purge policy. Normal, we do SQL Backups and keep the history for some period , for example, 8 weeks, so we can go back and restore any data point in time upto 8 month in past. and we also do Tape backups.

Question is Where can I get nice article or documentation on this to best design such policy where I make sure that I am covered for point in time recovery of database (which is sql backups) and point in time recovery in far past, say, 3 years ago using tape backups, and I need to make sure that I don't repeat the same efforsts.

Any advices or suggestion on this topic.

Thanks,

View 5 Replies View Related

Stored Procedure For Archiving The Records In Another Table

Mar 27, 2008

I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values-------------------------------------------TaskId   PoId   Podate         Approved
   1       2    2008-07-07      No    3       4    2007-05-05      No    5       5    2005-08-06      Yes    2       6     2006-07-07     Yes
Table B contains the following columns and values-------------------------------------------------TaskId      TableName   Fromdate     Approved_Status
1                A        7/7/2007     No3                B       2/4/2006      Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and B i have the TaskId as the common column
Pls provide me with full stored procedure code.

View 2 Replies View Related

Analysis Services Archiving Is Working On One Server

Oct 5, 2001

Hi,

I am able to archive the Analysis Services db's using the msmdarch on one server but not the other server. (It is working in the Test environment but not the Production environment.) Here are the details:

Both servers are SQL Server 2000 sp1.
I compared sp_configure and both servers have the same settings.
The Production server is clustered and the Test Server is not clustered.

I can archive in TEST using the following:
- From Analysis Manager
- Using the msmdarch command from a command prompt
- From a scheduled job using the "Operating System Command" Type from a job step entering the msmdarch command in the Command box.
- Using the msmdarch command with xp_cmdshell within Query Analyzer and from a Stored Procedure.

I can archive in PRODUCTION only using:
- From Analysis Manager
- Using the msmdarch command from a command prompt
** It does not work from a job or from Query Analyzer

I have checked the permissions of the SQL Server Agent account on both servers and they are identical with sa rights and a member of the OLAP Administrators group.

Does anyone have any thoughts on what could be causing this?

Thank You

Michael

View 4 Replies View Related

Archiving Data Transformation Service Packages

Feb 29, 2000

Is there a way, for example to script a DTS Package, so that it can be deleted and recreated at a later date if necessary? I have quiet alot of these, but few are used regularly. The msdb database is now up to 80 MB. However I don't want to delete them and have no way to recreate them.If I took a backup of msdb and then deleted the packages, would restoring msdb at a later date restore the packages?????

View 1 Replies View Related

Extracting Data From A Table For Archiving Purposes

Sep 22, 1999

Hello,

I have been placed in the position of administering our SQL server 6.5 (Microsoft). Being new to SQL and having some knowledge of databases (used to use Foxpro 2.6 for...DOS!) I am faced with an ever increasing table of incoming call information from our Ascend MAX RAS equipment. This table increases by 900,000 records a month. The previous administrator (no longer available) was using a Visual Foxpro 5 application to archive and remove the data older than 60 days. Unfortunately he left and took with him Visual Fox and all of his project files.

My question is this: Is there an easy way to archive then remove the data older than 60 days from the table? I would like to archive it to a tape drive. We need to maintain this archive for the purposes of searching back through customer calls for IP addresses on certain dates and times. We are an ISP, and occasionally need to give this information to law enforcement agencies. So we cannot just delete it.

Sorry this is so long...

Thanks,

Brian R
WESNet Systems, NOC

View 1 Replies View Related

Analysis Service: Pb When Archiving/restoring From 1 Machine To Another

Oct 19, 2004

I have to install cubes on another machine (not on the same site/NT domain).
I have archived the OLAP db and generated a .cab file.

I have retored on the destination server.
It works fine in Analysis Manager console (I can process and lokk at the data).
But when I want to access the cube from Excel using OLAP cube, I have an error msg.

Is there something I need to do on the distination server ?
Langage, SP, role, version of SQLServer/Analysis services ...

Thanks

View 3 Replies View Related

SQL Server 2008 :: Batch Archiving With OUTPUT

Oct 13, 2015

I have to set up archiving of some pretty large tables (125M+ rows) and I'm trying to figure out the best way to do it.

I've been using OUTPUT into, something like this:

delete top (100000) a
output deleted.RecordID
into dbo.RecordsArchive (RecordID)
from dbo.Records

It's difficult to tell if it sub-optimal code or just sheer volume, but any performance penalty using OUTPUT into in this way? or is it advantageous to insert the records to the archive first, and then delete them in two separate operations?

View 0 Replies View Related

Transact SQL :: Syntax Check For Archiving A Table

May 12, 2015

I am trying to write a SQL Server query that archives x-days old data from [Archive].[TestToDelete] to [Working].[TestToDelete]table. I want to check that if the records on ArchiveTable do not exist then insert then deleted...which will be converted to a proc later.. archives x-days old data from [Working].[TestToDelete] to [Archive].[TestToDelete] table */Here is the table definition, it is the same for both working and archive tables. There are indexes on: IpAddress, Logdate, Server, User and Sysdate (clustered).

CREATE TABLE [Archive].[TestToDelete]([Server] [varchar](16) NOT NULL,[Logdate] [datetime] NOT NULL,[IpAddress] [varchar](16) NOT NULL,[Login] [varchar](64) NULL,[User] [varchar](64) NULL,[Sysdate] [datetime] NULL,[Request] [text] NULL,[Status] [varchar](64) NULL,[Length] [varchar](128) NULL,[Referer] [varchar](1024) NULL,[Agent] [varchar](1024) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]/* the syntax, which will be converted to proc is: */SET NOCOUNT ON;DECLARE @Time DATETIME,@R INT,@ErrMessage NVARCHAR(1024)SET @R = 1/* set @Time to one day old data */SET @Time =

[code]....

View 8 Replies View Related

Managed Procedure To Automate Archiving Files In A Database

Sep 3, 2007

I need to archive files in a database by checking an archive date for the file contained in a field in a table of a database, if the archive date is greater than todays date then archive the file by moving it to an archive folder. I am thinking the best way might be to use a manged stored procedure, but I also need to run this procedure once every 24 hours at about midnight so how would I do thi? Another way might be by using DTS or something. Has someone else done this and how did they go about it?

View 1 Replies View Related

What Is The Best Way To Implement An On-line Near Real-time Archiving Method?

Aug 9, 2000

Hi to you all and specially to who will solve my problem!

That is what I want:
I need a database that will serve as an archive db in read-only for day-to-day queries
and a production db that will be kept small by deleting un-needed records

A replication setup would be fine but I didn't find a way to avoid the deletes to keep the production database small, to replicate in the archive database where transaction are replicated.

Is there someting i am missing?

Andre Vigneau

View 2 Replies View Related

Transaction Replication && Data Archiving On SQL Server 2000

Jul 23, 2005

Hi techiesI have set up a Transaction replication from My Primary Server toSecondary Server on Orders table.Thousand of records gets inserted on Orders every hour which getreplicated on the secondary server. it works finereporting apps uses Secondory server's Orders table data for generatingreports .The Problem :Let say if i want to Remove older records from Orders table in theprimary serverwith out reflecting this change on the secondary server.is there a way to PREVENT this operation /transaction to be propogatedto the secondary server.Note : i am moving the records to another table (orders_Archive ) anddeleteing the rows from orders table . Also I need all the rows to bepresent on the secondary server table.Please advice ASAPRegards,Raj

View 4 Replies View Related

Partitioning

Sep 10, 2007

Hello
I have a table containing 100,000 record for each year, and every year a new 100,000 record are inserted,I need to know how to make partion this table by year
I need to know the syntax
Thank you

View 5 Replies View Related

Help With Partitioning And Between

Jul 25, 2006

I have begun my quest to become familiar with SQL 2000 table partitioning and have had some success.
I've partitioned tables according to the "rules" and guidlines of other papers and the show plans look good for all cases.

But when I went to create another demo series, I got stuck.
I've created two tables, with the appropriate CHECK constraints, and with a "union all" view.
When inserting data, it goes to the right tables.
When reading data with a where clause like:
col1=10,
the show plan shows the query getting the data from the ONE correct table.
BUT
When the where clause looks like:
where col1 between 1 and 10:
the showplan shows the query getting data from both tables, instead of the table that 1 to 10 belongs to.
Help!

View 2 Replies View Related