I have a table contains huge rows of data. Performance issue raised. I am
thinking archive some data so that the table will not be that big. The most
convience way is move it to another table. The problem is: will this solve
my performance problem? or I need to move it to another database to reduce
the database size?
Regards,
TrueNo
Project assigned to me called ''Archive off old data''.As SQL DBA the best way to archive large table is data Partitioning. But one table does not enough in terms of size to do partition and plus table has 32 other tables,view,sps dependencies.The data from that table needs to archive off every week to different database on different server. My question are
1) what is the best way to archive data ?
2) what are steps I need to follow ? Do I need to remove dependencies first and then take out data?.
Hello, everyone:
What does "data archive" mean in SQL Server? Is it same thing as archiving in Oracle?
Thanks.
ZYT
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data.
Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days.
We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok.
Any other solutions that would allow us to archive data instead of deleting it? Thanks.
Hi Folks,
We have transactional replication setup to replicate data from production across to a reporting server.
We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server.
Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database?
Cheers,
David
Hi Folks,
We have transactional replication setup to replicate data from production across to a reporting server.
We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server.
Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database?
Cheers,
David
Hello:
The purchased-application mssql 6.5, sp 4 that I am working on has one large table has 13m illion. It the largest table considering thenextlatgest table is only1.75 million rows.
Thew vnedor has made a change to this largest table in recommending changing a data type -- char to varchar. To make this change easier to do,
I want to "archive" older data not necessary for the current year or current processing to another table.
What is the best way to do this archiving?
Any information you can provide will be greatly appreciated. Thanks.
David Spaisman
I have two tables say A and Archive. After a certain period of time some records are to be sent to archive table.To copy records to archive table I am using SqlBulkCopy operations.Now I have to delete the records from A Table. I was thinking of sending a Comma seperated id's of rows that are to be deleted to a stored procedure.Are there any better techniques to move data to archive table and to remove data from main table.?Thanks.
View 9 Replies View RelatedI have the requirement to implement archiving of some database old data.
The database is not used to store files, just table with text/numeric data, some data modification logs.
Which strategies do you usually use to archive old records from a database?
Do you move old/unused data to another similar database in another server?
The data needs to be accessible for read access, even when archived.
I am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday for all the tables and once the move is done I need to delete this data from A. And the same thing the next day and everyday. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. Some tables have 200Mil+ rows. So it takes forever.
I'm looking for a process to archive data through replication. I have nightly job that purge records in few source tables(publisher) retaining only 3 yrs data. I have archive database (subscriber) that contains prior 3 yrs data and current 3 yrs data.
Before nightly job DELETES records in Source table i want to STOP replication so that the delete is not replicated in archive database. After the job completes i would like to TURN ON replication so that any new inserts and updates in Source will ONLY be replicated in archive database.
My DBA tested this but after last step of turning replication back ON archive database is sync'd with source table.
There are around 70 tables where 30 of them are transactional tables that needs record purge. Developing ETL process is possible but tedious.
I wrote code to archive Data.I created a table that set the maximum number of records to archive and delete and loop until finished or a set a flag in a table to stop archiving. I was able to limit the number of records to commit.
View 9 Replies View RelatedI need a script that can be schedule for a SQL7 server to archive a table-space's data for exery day except current, then pull the last 7 days worth of data back into the table. Any one have anything like this? My dbase is a load totaling dbase and the size is getting out of control.
View 4 Replies View RelatedHello,
I will have to create a table that consists of only of two main fields. one: them employeeID and two: the SupervisorID,
my question is what should I define as my primary key. Should it be an aditional field, or could it be the EmployeeID field.
The employeeID is an unique filed. The end user for this application will be updating rearly some of this records, and may be adding or deleting some new records exporadically.
Thanks for suggestions.
I have a table it contians 3735300 rows. and 5 col
Everyday more than 200000 rows will be inserted
When I run a query to extract a week data from the above table It is taking more than 2hrs.
The table has clustered index on all the 4 col.
Can anyone suggest me how to improve the performance.
Thanks in advence.
sqlranga@yahoo.com
hi.. guys
one of my table has more than 300million rows. that i need to remove the data b4 may 2006 which is over 250 million rows. since the insert rate so high(none stop) and i cant truncate the whole table and dump the data after may 2006 back in (they wont let me). and if i use delete statment to delete only 200k rows data it will freeze up the whole database for long time(around 45 mins) and it will take me few months i believe. so that's not the option either.
is anyone out there got better idea?
also i got another table has more than 150 millions rows that index need to rebuild .... anyone got any idea how long it gonna take? just roughly guess. thanks
Dear all
I am using sql server 2005 , asp.net 2.0 ,C#.
I need some suggesstion on how to pass request info to new level . that is if a employee request for something mail should go its next level ie. immidiate boss(next level can be null) and at any time requested or chain of employee( immidiate boss and succesor ).
Please suggest something
Hi,
We are plan to develop a new VB6 / MS Access application that will produce a whole bundle of reports for the outside salesman team (all of them have a laptop). The data is come from midrange AS400 tables.
Now we have decided should we using SQL Express with SQL tables or just MS Access MDB tables. Some of the laptop the salesman is only P3-800 and I'm worry about the speed for SQL express on those laptop.
The other hand, I like to use SQL databases, since we have the SQL server 2005 running for other application already.
Any one tested the performance between the SQL Express via MS MDB, it seems the SQL 2005 express is use more overhead than MSDE.
We tried the MSDE and the performance is not that great after the database grow beyond 500MB .
Any suggestion are welcome.
Thnaks!
Hey guysI have to implement a dynamic Parent - > Child Scenario ... but the catch isas Follows :I need to create this Table Design so that I can have multiple Parent ->child - > parent Relationships(ie Db driven "Window Explorer - feel". 1 Folder that holds another Folderthat holds another Folder etc to Infinite )So if the Above makes any sense ... Suggestions would be WelcomeThanx
View 3 Replies View RelatedHi,I've 2 Databases one is online and one is offline (connected toInternet).. using SQL SERVER for both servers, i need to update theOFFLINE Database one on a time schedule for every 15 mins from theOnline DATABASE.. can anybody suggest a better way other than web services.. coz ourserver is quite old and doesn't support webservices..any ideas.--chavasreedharMessage posted via http://www.exforsys.com for all your training needs.
View 1 Replies View RelatedAll,
I need to keep checking (every 30 minutes) for a record in a table to appear,
Any idea on how to do it? Should it be done in a SSIS package or in sql server job schedule?
Please give more detail on to accomplish.
Thanks in advance
We have SQL server 2005 as backend in our portal project.We have job search interface with below searchable fields
1.) City
2.)State --length 2
3.)Zip-- 5
4.)Keywords ( space or comma delimeted words)
5.)Category (Drop down List)
6.)Subcategory (Drop down List)
Based on the above fields we have 2 options to search and avoid performance related issues.
1.) concatenate all the above 6 columns into 1 column and apply a full text search. Fields like state & Zip will be appended with some delimeter to do a search on those particular values .
Ex : store the state "CA" as '#stCA#'. so that it will not search the rest of the data that has keyword CA.
2.) create a table with the above 6 columns and create a Full-Text search on all the columns.
Can you suggest us which one will best and why? If not any alternate better solution
I have a table that allows the scheduling of devices.
I can create an Insert Statement that allows a user to schedule a device as follows:
Device #1 From 5/1/08 To 5/30/08
Is there away to create a check that prevents the user from scheduling that SAME device for any days within that month?
Device #1 is NOT available to be scheduled anytime during the month of May because it is already scheduled.
Thanks for any suggestion.
Wayne
Hi
I have a few tables which store classinfo, testsinfo, scoresinfo respectively.
For a given classID/TestID/CourseID i need to be able get the top 5/10/15 etc, avg, max, min of scores for reporting purposes.
I am wondering if i should maintain a seperate table which would be populated by a job daily and which would maintain the topper IDs rather than having a Stored proc return them dynamically when needed. I am looking for some suggestions. Please let me know.
Thanks.
I have some problem with ASP.NET cache, I found other people has similar problem, but I didn't find real solution.
The one bother me most is the SQLCacheDependency doesn't work stable. I insert object in cache and has SQLCacheDependency linked. After a period of time, it stopped working. That means the the object is still in cache, but change on db side doesn't remove the cache entry. I am not sure if it is ASP side or SQL side, I feel it is ASP side.
I am using 2.0 + SQL 2005.
Once the db command notification stop working, you have to restart IIS or clear all items in cache since you don't kno which one is changed.
Any suggestion?
My primary data files are ponting to F drive which is 200G and one db has occupied 90% of the space and the applications are not running. What is the best solution
View 1 Replies View RelatedHai, This posting related to my previous posting. Updating Bulk record. Actully im writing SP for windows Services, which does bulk updating of records to Sql 2000.
In one point of time, i may recieve 2000 records for single update..So what i have done, im concatinating the values to be updated in string and passing the string..
But when i did the calcuation for 2000 records, the length of the character comes around 10000. So i cannot user Varchar to hold me concatinated value in SP. There is another datatype "Text" but im not sure abt that. For my requirment what is the data type can i go for.. if i can use "Text" how to declare that...Ordinary declaration gives me error..
Help pls
Hai, i need ur suggestion to use ntext in my table. basically i want to store the xml data(will be in same xml format only) in sql2000 database. the max size of the data will be 5mb.
so can i use ntext to store this data.basically, only once i insert the data to the database. no update after that. only i throw the query to retrieve that xml data from the database. is it advisable to use,ntext for this requirment. is there any other suggestion ??
help pls
Can anyone recommend good books for learning Reporting Services? Assume the reader is familiar with Crystal Reports and other reporting environments, but has no experience with SQL Server 2005 and very little with Visual Studio 2005.
Hi Friends
I required a suggestion from your side
Actually I am working in MS Access from last 6 months. Now I want to convert my database into SQL Server
I have a knowledge of SQL Server and I don't have any problem with this.
But what I want to know how to maintain a report in SQL Server I don't have any knowledge for creating a report. My freinds suggest me a Visual Studio 2005 for creating a Crystal Report.
So Please suggest me it is helpful or not and onething more is that I have to work on SQL Server 2000 or SQL Server 2005 which is more beneficial.
Please reply me for this
Waiting for your any kind of reply
Please
Thanks
Ashish
Hi,
I have push scripts using transcation replication . can i use some task of SSIS to automate the process .
Thanks,
Nav
Hi
I am new to Sql Server
I have to select all fields from a table based on min(date) . i have duplicate accountnumbers in the query i need to eliminate that based on min(dateTime).
when i am writing a query
Select a,b,c,min(d) from table , it is not working?
Below is the example
A B C D
222 1 11 2007-02-12 14:09:16.000
222 1 8 2007-11-06 13:57:18.480
Any suggestions would be very helpful
I am transfering data from a textfile to sql server.I use a data flow task for trasfering my text files.
Here is what i do.
1.Add text file source
What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.
how can this be achieved???
2.add one more column to my text file which should contain the status(insert or update).
how can this be done??
3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.
please help...