Fastest Way To Move Huge Table Across Servers
Mar 23, 2008Hi Guys,
What is the fast way to move huge table (77 million) records with 25 columns across servers? The servers are not linked though.
Thanks for the help.
ADVERTISEMENT
Fastest Way To Copy Tables And Their Indexes Between Servers?
Nov 13, 2006The DTS Task Copy Server Objects is PAINFULLY slow.
The Copy Table Wizard is fast but generates an unmanagable DTS and does not bring over the indexes.
Any tips or tricks to copy tables, data and indexes and a reasonable speed?
Thanks,
Carl
Move Current Huge Data File To New GPT Drive?
Oct 31, 2015I have a database data file almost at 2tb maxing out a windows drive. Only 16gb left. Should I just add another data file on another Windows drive for growth? Or just move current huge data file to a new GPT drive? Or do both adding another data file and moving existing to its own new GPT drive?
Primary objective is to make do for now.
Huge Deletes In A Huge Table
Apr 3, 2000SQL 7 SP1 NT4 SP5
I have a TRANSACTION table with 150 million rows.
I have a USER table.
Each user has about 600 records in the TRANSACTION table.
The TRANSACTION cluster index is on USERID + RECID . The second index is on USERID + Fieldx + Fieldy.
The TRANSACTION table gets about 1.4 million inserts in a normal day and about 40,000 updates.
I want to go through the USER table and delete all users who have not visited me in a while.
I want to do this without substantially hindering performance in a production environment. I can perform this over a week period or two if needed.
The best way I thought of doing this was to grab x amount of users in a cursor and loop through deleting their corresponding TRANSACTION records.
Does anyone have any ideas on a better way. What is going to happen to my indices during this time ?
Thanks !!!
SQL Server Admin 2014 :: Huge Traffic Between Share Point Front End Servers To Content Database?
Feb 20, 2014We have a Customized share point application with Very minimal data usage and we have used only 5 to 6 lists and libraries only in the share point.
Configuration is
Clients -- fire wall --- Load Balancer ---- WF1 and WF2 --- SQL DB
ROUTING IS VIA FIRE WALL.
SUDDENLY THE SITE GOT DEAD SLOW AND UNABLE TO TRACE THE PROBLEM AS EVERY THING LOOKS FINE.
Checked with the firewall Team and they stated its fine from their end & even we have verified the counters, CPU, Memory & Page life expectancy, buffer counters all looks good and even we do not have huge data in the database. We have only 50 concurrent users are working...
How To Move Files Between Servers?
Sep 9, 2004Can I move a database from one server to another server using QA?
EXEC sp_detach_db @dbname = 'IISLOG'
EXEC sp_attach_single_file_db @dbname = 'IISLOG',
@physname = 'c:Program FilesMicrosoft SQL ServerMSSQLDataIISLOG.mdf'
Or would I need a tape backup to move this database to another server?
Thanks
Lystra
How To Move SQL Servers From One Domain To Another . Steps
Jul 20, 2005Hello,I need to move 6 SQL Servers from existing domain to a new domain.Currently all SQL Server servies are started using the domain accountof old domain.What precautions and steps are necessary for migration from one domainto another. Also note that the name of the servers needs to be changedin the new domain .ThanksSrinivas
View 5 Replies View RelatedProblem When A IS Tries To Move Data Between Servers
Dec 18, 2006 Hello,
I have a problem: I have created an Integration Services in SQL Server 2005 that moves data from a table in a server to another table in another server. I have set the protection level property to "don't save sensitive".
The problem is when I try to execute it with a dtexec command. I get this errors:
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".'
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host.'
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'xxxxx'.".'
I don't know why the Integration Services does not work, and what are this errors about. Maybe the problem is with the protection level property, because when I set it to "don't save sensitive" it has some warnings, but when I set the property to "Encrypt sensitive with user key" the error messages are the same.
Thanks,
Pablo Orte
Move / Copy Linked Servers From One Server To Another
May 27, 2015I have migrated databases and logins from SQL server 2008 R2->SQL Server 2014. Now I also want to migrate/copy the "linked servers". Do you have a step-by-step for this, so that i will copy everything that is necessary regarding logins etc ?
View 2 Replies View RelatedFastest Way To Remove Large Table?
Nov 22, 1999We have a table that we BCP into, the data is then processed and inserted into its appropriate table.
Then the table or its data needs to be removed. This seems to be a very slow operation to remove
the table or table data. I have tried drop table, and truncate table and it takes nearly as long as
the bcp operation. The table has 12 million rows. I didn't think either operation wrote to the
transaction log except for page extent management. Why is the drop and truncate so slow. Suggestions?
Fastest Way To Execute Aggegate Functions On A Table?
Jul 23, 2005I've probably not given the best title to this topic, but thatreflects my relative "newbie" status.I have a table that goes essentiallyTSDATETIMEjobnumberVARCHARjobentryVARCHAR...the TS is a time stamp, and the other two fields are job numberand entries. There are (many) more fields, but this is the core ofit. The relationship is there will be several entries per job, withone row in the table per entry (i.e several rows per job).In constructing a web interface I want to create a list of recentjob numbers, and summarize it broadly as followsmax(ts)jobnumbercount(jobentry)...I can do this by a select command as followsselect top 30 max(ts) as time, jobnumber, count(jobentry)from Jobsgroup by jobnumberorder by time descHowever I'm now finding that this is quite slow now that my teat tablehas around 400,000 entries (Even though I've added indexes to mostrelevant fields). In particular it's much slower thanselect top 30 jobnumberfrom Jobsgroup by jobnumberorder by jobnumber descleading me to suspect that the aggregate functions are slowing thisdown. I would guesstimate the difference in speed is around a factorof 10-20.As I type this I think I've just realised it's theorder by time descthat is probably causing the problem (in that it will need tocalculate max(ts) for all jobnumber's in the table in order toexecute the "order by" clause).I think I can see the solution now (amazing what typing out a longquestion can do for you :-)My question was going to be if there was any approved method/tricksfor avoiding this sort performance hit.It seemed to me that if I could first grab a decent number of recentrecords (quickly) I could then execute the aggregate functions againstthat smaller set of records. I know in broad terms how any entriesthere can be per job, so I could select enough to make at least 30jobs, and then execute the real query against that. In my case therewill be probably less than 10 entries per job, so I could grab 300records and execute against that, instead of against the whole400,000.That being the case is this best donea) as a subquery, and if so, howb) by creating a temporary intermediate table (which isin essence a more explicit version of (a) isn't it?)Another solution that occurred wasc) to create and maintain a summary table, with justone record per jobnumber with a view to having itserve this particular (common) enquiry.For (c) to work I would probably need triggers on the Jobs table tomaintain the summary table. This would probably represent moreoverhead overall, but should certainly give a better responsewhen the query outlined above is executed on the web site.The response time to this query is becoming critical on the webinterface to avoid timeouts.Any suggestions or comments are welcome. If it's RTFM, then I'dappreciate page numbers :-)Thanks for reading this far :-)--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
View 4 Replies View RelatedFastest Way To Copy A Table To Another Table
Jan 8, 2008Hi,
I have a number of large tables (50,000,000 + rows and 30+ columns) that are refreshed once per month with data from another system. Data is first loaded into a staging table, that has an indentical strcuture to the final table, cleansed and then copy to the final table.
The current copy process is simple but inefficient:
Truncate Table <final-table>
Insert Into <final-table>
Select * from <staging-table>
If possible I don't want to use DTS for this and I'd like to use a batch size of around 10,000 rows to help reduce the size of the log file because I have 3 of these types of Inserts running concurrently.
What is the fatest way to copy data from one table to another, within a stored procedure using SQL 2000 on a large, clustered server that uses logging?
Thanks,
Jason
Problem On Huge Table.
Mar 14, 2001We have a huge table which has 12 million records. And when I run the following script, it took 50 hours. Is there anyone who can help? Thanks.
update TableA
set In=e.In, EA=e.ea, We=e.we
from TableA c, TableB e
where c.code=e.code
TableA 12,000,000 records.
TableB 750,000 records.
And have clustered index on each code field.
Alter A Huge Table
May 18, 2001I need to alter a table (expand the column size for varchar(10) to varchar(255)) and the table has 200 million rows.
Please suggest me the best and the fastest method to achieve it. The database is on SQL 7.0
Thanks
Huge Table Backup
May 13, 2008Hello
i want to ask about the huge table(table with many tera records) backup time cost , any one can help me please in determining the time cost nearly
Horizontal Partition Of A Huge Table
Jul 1, 2005Hi,
I have a table with 52 million rows which resides on Primary file group in my database. Because of huge number of rows the performance has gone very down and I would like to break the table into parts.
Can anyone suggest me the steps for doing the same and the number of parts that should be made. It is named as Account_Transactions and contains information of Policies in an insurance database.
Rajat
Frequency Counts From Huge Table
Mar 12, 2008Hey guys,
I have a table with about 80 columns and 400 millions records. Each columns has different responses that I need to get frequency for. I need to get counts for each response from all the columns... I have a query that does it, but it will run forever... what is the best way to do so?
My starting query:
select res, sum(cnt) from
(
select col1 res, count(*) as cnt from table1 with (nolock)
group by col1
union all
select col2 res, count(*) as cnt from table1 with (nolock)
group by col2
........................
select col80 res, count(*) as cnt from table1 with (nolock)
group by col80
)a group by res
Does DB Size Decrease When I Delete A Huge Table ??
Jan 15, 2004Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??
Urgent! Please Help! Deleting Data From A Huge Table
Mar 16, 2004I have a huge table with 4 primary keys on it. I need to delete the data from this table ( approx. 5.6 millions records to be deleted). It takes a hell lot of time to delete it by normal query.
Can someone please suggest me a better way?
Any help will be appreciated.
How To Select Specific 2 Rows Out Of A Huge Table
Jul 24, 2013I have a very large table , and from that table I need just 2 records with column1 = 'A' and column1 = 'B' .
Here I don't think if I can not use OR or IN or Case operators because I need exactly 2 records not more.
The Best Way To Delete A Huge Number Of Records In Table
May 30, 2008Hi Everyone,
We have a large test database with million of records for more than company site Code. Sometime we want to refresh the data of that database for one or more site Codes.
In order to do that I have to delete all records of the site code we want to refresh on the test database first then copy a new set of data from production database over. Since we refresh data based on the site code therefore I have to use the Delete command instead of Truncate.
Since this is a huge database with thousand of tables and million of records per table I have a performance issues with delete command. So what would be the best to delete a large number of records without writing any information to database log file?
FYI: The Recovery model of this database is Simple
Regards,
Jdang
Truncate A Huge Table Having 21 Millions Of Rows
Nov 19, 2015SQL Server: 2008 R2
Question A : I need to truncate a table, it has 21 millions of rows and it has a size of 14 GB.
1- How do I find out if this table is not being referenced by a FOREIGN KEY?
2- Does it Participates in a indexed view?
3- Is being published by using transactional replication or merge replication?
Question B: How do I safely truncate that table?
PULL Huge Table From SQL Server Problem
Jul 12, 2007
Hey Guys
I have meet the same problem, too.
I create a table with 1,380,000 rows data,
the db real size about 114 MB.
The primary key size is nchar(6).
When I use RDA pull, I found that the primary
key in the PDA disappear. So, It took a long time
to get query response.
But when I delete some rows to 680,000 rows of data.
After I pull, The primary key can pull from the SQL Server.
PS: I didn't change any code. Just delete some rows.
Is that SQL-Mobile's bug??
PS: 1.Database and Temp Database limitation both are 384MB
2.If I use query analyzer to add primary key it works! so strange!!
3.Pull process return "S_OK".
4.After Pull process finished, the db connection still alive. It seems not like
time out problem.
5.Local Connection String:"Data Source='%s\%s';SSCEatabase Password='%s';SSCE:Encrypt Database='true';SSCE:Max Database Size=384;SSCE:Temp File Max size=384;SSCE:Temp File Directory=%s"
Database Table Design For Huge Number Of Columns
Jan 24, 2008Hi
I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%
So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax
same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.
So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.
I was wondering which is a better approach out of the below 3:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
Your help is greatly appreciated.
Thanks
Question About Log File Size When Alter Huge Table
Oct 31, 2005I have the next question, and i would like to hear what do you thinkabout, and if is there a better solution for "my problem"here is the question, I have a huge table with 60GB of data (imagefiles). The problem happen always when i try to ALTER the structure ofthe table. For example I change a field char(3) to char(4)...thesqlserver then performs the "alter table" command...that must besomething similar than "insert into the new table + drop the actualtable" and for that I need about 60GB o space for my LOG file, andtakes hours to complete the operation.Is this the only way to alter a single field in my table??I would like to heard you opinions...Thanks..ALberto
View 2 Replies View RelatedDatabase Table Design For Huge Number Of Columns
Jan 24, 2008Hi
I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
e.g
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%
So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax
same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.
So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.
I was wondering which is a better approach out of the below 4:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
4) Create a view just for reports, and calculate values dynamically in UI and not adding any computed values in table.
Your help is greatly appreciated.
Thanks
Transact SQL :: Append A Column To The Huge Transaction Table
Oct 18, 2015I want to append the column to the transaction table(60 million records in it.) ..
Our transaction table is being used in production.. but i have very less amount of time ..
Instead of alter table.. (IF we use the alter to take backup of table and do the processing it will take more time). Is there any way to append the column to the transaction table ..
SQL Server 2014 :: Huge Number Of Rows In Table Spool
Jul 6, 2015I have a CTE query against a table with 32K rows that runs fine in 2008R2. I am running it in 2014 Std Ed. against the same data and it runs very slowly. Looking at the execution plan I think I see what's contributing to the slowness.
Note that the "actual number of rows" is some 351M...how is this possible?
the query:
declare @amts table (claim int,allowed decimal(12,2),copay decimal(12,2),deductible decimal(12,2),coins decimal(12,2));
;with unpaid (claimID) as (select claimID from claim where amt+copay + disct+mm + ded=0)
insert @amts
select lineID, sum(rc), sum(copay), sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln
where status is null
and lineID not in (select claimID from unpaid)
group by lineID
it's like there's some massively recursive process going on?
SQL 2012 :: Apply Primary Key On A Huge Table So Downtime Is Minimal?
Jul 30, 2015I have a table (named table1) with 20million rows. It takes around 11 minutes to apply the primary key to this table. There are some tables with over 100 million rows so based on the previous time if my calculations are correct it will take close to an hour apply this primary key for tables with around 100 million rows.
My current solution is to create another table (named table2) with no indexs or primary keys. Pump over only like 5 days worth of data, then apply the primary key. Then have a script that will eventually populate table2 with the rest of the data gradually. When I say gradually I mean like insert like every 100k per hour or something. Keep in mind this table2 is heavily updated with new records.
Transact SQL :: Table Locking Happening When Removing Huge Data
May 14, 2015declare @error int, @rowcount int
select @rowcount = COUNT(1) FROM STG_BCDR;
while @rowcount > 0
begin
BEGIN TRAN Deletion
[code]....
Above code i try to delete records batch by batch to avoid table locking at BCDR table.total records in this BCDR table is 40,000 records. However I run the code at execution plan, the BCDR table still clustered index scan which means that the locking still happend.
If i change the delete top (5000)...... to delete top (5).... then thre is clustered index seek, which is good..The problem here is each time only delete top 5 records which is means it will realy take very long time to remove those data.
how to cater the situation inorder for me to delete those huge data without table locking happend. If table locking happend , then other user will not be able to access this table at the same time.
Fastest Way To Delete Hundreds Of Table Triggers And Hundreds Of Stored Procedures?
Jul 20, 2005How can i delete all user stored procedures and all table triggers very fastina single database?Thank you
View 17 Replies View RelatedHow To Work With Huge Amount Of Records In A Table Using MSSQL Server 2000?
Dec 21, 2005In
one of our forth coming projects, with ASP.Net/C#/MSSQL Server, We have
to deal with a Business table having about 15 millions of records. We
want to know, that which methodologies should we adopt, both regarding
front end and back end perspective, so the site could give optimised
performance. Also in place of a Dedicated Server, the Hosting Company
provides MSDE (that come with .net). Will this create any problem with
this project, that have such a huge table? Should we go for some
advanced database technique, such as, Clustering, Spliting Tables, etc.
Followings are the fields that the business table contains:
ID, Category ID (which comes from a Category table, each business is
under a category), BusinessName, SignupDate, Address1, Address2, Phone
Number,
Hours Of Operation, Years in Business, LicenseNumber, DiscountCoupon, Website
Query To Move Certian Data From Sql Table To Access Table
Dec 14, 2007hi all,
is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.
thanks