Interrupt Processing Of A Large Insert Process In SQL Server 2000.
Jul 20, 2005
I'm running a resource-intensive stored procedure, which reads a file
with about 50,000 lines with a BULK INSERT into a temp table, then
goes through it and inserts a record for each line into another table.
While this procedure is running, SQL server stops accepting any other
requests coming from the website.
Question:
Is there a way to make SQL server "listen", or emulate an "interrupt"
to other requests while in the middle of a long intensive process?
I really appreciate your replies.
Thank you,
Oleg.
View 5 Replies
ADVERTISEMENT
Jan 9, 2014
I am attempting to do a rather simple purge task on a very large table. This task will need to take place daily and delete records older than 6 months out of the database. On first pass this will delete well over 130 million rows. I thought the best way to handle this is create a proc and call the proc from a SQL Agent Job that runs nightly. Here is an example of the script:
CREATE PROCEDURE usp_Purge_WCFLogger
AS
SET NOCOUNT ON
EXEC sp_rename 'dbo.logs', 'logs_work'
GO
SELECT * INTO dbo.Logs_Backup FROM dbo.Logs_Work WHERE TIMESTAMP < DATEADD(month, -6, GETDATE())
[Code] .....
View 3 Replies
View Related
Nov 2, 2006
Hi,I'm implementing mass information procedures that is stored in a SQL Database.What methods and actions i need to take in order for the process to be faster in a case like this (its more then 1000000 rows).I'm trying also to improve the memory usage since i use the DataTable in C# and I'm looking for a better way to process the retrieved data. is there a better class or method thats improving the speed and preventing any memory leaks ?please advise....Thanks for any help,Lior S ;)
View 1 Replies
View Related
Feb 27, 2006
Hi,
I'm running an application on a server which grabs data from a database table on another server using SqlConnection, SqlDataAdapter and DataSet.
The application then updates every row in that DataSet's DataTable and the updates are saved back using DataAdapter. The code is pretty much straightforward code that you would find on MSDN documentation for using DataSets. The table contains a little over a million rows.
When I run the application, I get an error saying the Server Application is not available. Upon looking into the application event log, I get this message.
aspnet_wp.exe was recycled because memory consumption exceeded the 306 MB (60 percent of available RAM)
How do I get round this? I thought DataSets were supposed to handle large datatables comfortably without having memory issues.
-Thanks
View 1 Replies
View Related
Oct 21, 2007
HiI have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.Any ideas?ThanksEd
View 1 Replies
View Related
Sep 25, 2006
We recently installed SQL server 2005 on a couple of our servers. I use Visual Basic 6.0 at the moment and use ADO to connect to our various SQL servers.
I recently discovered on one of the new servers, that every time my programs runs, (every 4 minutes for 12 hours a day) the SQL process shown in task manager grows by 1-10 Megs.
The SQL process was at 776,912K when I rebooted this afternoon. It started back up at 106,120K.
I am not doing anything differently than I did when my programs were talking to SQL 2000, and I have never seen this memory leak issue. Is there something extra I need to do in SQL 2005 to finish/clear these SQL queries and not bog down SQL's memory?
An example of how I would connect and do a SQL transaction:
Dim cn as ADODB.Connection
Dim rs as ADODB.RecordSet
Set cn = New ADODB.Connection
Set rs = New ADODB.Connection
cn.Open strConnect
select1 = "select firstName, lastName from clients"
rs.Open select1, cn, adOpenKeyset, adLockOptimistic
If rs.EOF = False Then
rs.AddNew
End If
rs!firstName = Trim(Text1(0))
rs!lastName = Trim(Text1(1))
rs.Update
rs.Close
cn.Close
At the end of the program's run I would:
Set cn = Nothing
Set rs = Nothing
View 3 Replies
View Related
Jun 22, 2004
Hi,
I am trying to replicate a production database server,on sql server 2000 at a particular geographic location to a new failover database server on sql server 2000 in a different geographic location via internet. The intention here is to use the failover database during times when the production server is down or busy and the synchronization needs to be sceduled for every 10 min.
It will be of huge help if any expert could give the detailed process involved and any precautions that need to be taken. I also need to keep inmind to use the ever/odd sequence number generator during the replication implementation.
Also, Whether to use row-level or column-level tracking?
thanks
SV
View 2 Replies
View Related
Jun 25, 2007
A similar question has been asked in a different thread, but didn't get a conclusive answer. Is there a way to set process priority on a specific process (alternatively - processes started by a specific user), similar to what the Task Manager allows in Windows?
I need to archive some data from an OLTP database. Unfortunately, some of the tables are growing rather large, and copying the data, and especially removing it based on record age, is a resource-intensive operation. My database must be available 24/7, and I can't have queries timing out, or I start losing business real fast. I was thinking of firing off the archive script and setting the process priority on it to something lower than normal, so it won't interfere with my OLTP processes.
Maybe someone can suggest a better solution?
View 2 Replies
View Related
Feb 23, 2006
hi..
I want to store a RMVB file to SQL SERVER 2000 ,and read from it,iwant to play the RMVB file in web,the size of the RMVB file is more than 300MB less 1G.the SQL Field Image can include it.
Now My Quesstion is How can i Store and Read the RMVB file from SQL Server2000?
I used SqlInsertCommand.ExecuteNoquery() in my program,but it Too slow,ao make a unknown error.
Thank you for your help.
View 6 Replies
View Related
Jan 26, 2004
Hi,
I have some problems with our database which is growing too large, and was hoping someone might have some tips on what I can do!
I have about 100 clients, each logging about 10 000 rows of status logs a day. So after just a few days the db is growing very large.
At present it's manageable, since I don't need to "dig" into the logs more than a few times a day. The system it self is not affected by the size of the log or traffic on the server. But it will increase to about 500 clients in 2004, and 1000-1500 in 2005. So I really need a smarter solution than what I have today to be able to use the log efficiently.
98-99% of these rows are status-messages which are more or less garbage during normal operation. But I still need to keep them in case an error occurs, and we need to go back an hour or two (maybe a day) to see what went wrong. After 24-48 hours these 98-99% are of no use. I do however like to keep the remaining 1-2%, they are messages like startup, errors, etc. Ideally they should be logged in two separate tables by the clients, but unfortunatelly I cannot make the clients change their logging.
This presents problems on multiple levels. Mainly in searching, which often times out, but also with backup and storagespace. At the moment I check the system for errors, and every other day I just truncate the log-file. It works, but it's not exacly elegant......
The server is a 1100 MHz P3 / 512MB / Windows 2000 Server /
SQL Server 2000. Faster hardware would help, but the problem is more of a "bad design" than "slow hardware" problem.
My log is pretty simple, as follows:
LogId - int - primary key - clustered index
ClientId - int - index asc
LogTypeId - int - index asc
LogValue - nvarchar[2500], ikke index
LogTimeStamp- datetime - index asc
I have deducted 3 different solutions:
Method 1:
Simply run "Delete from db_log where logtyipeid <> stuff_I_want_to_keep".
This is the simplest and the one i prefer, but it takes too long time to complete. Any tips to speed this process up?
Method 2:
Create a trigger which runs something like "Delete from db_log where logtypeid <> stuff_I_want_to_keep and date < today_minus_two_days" every hour or so. This will ensure that the db doesn't grow to large. But if I'm away from work a few days we might loose data we'd wanted to keep.
Method 3:
Copy what I want to keep into another table, and empty the log. Sort of like "Insert into db_log_keep stuff_to_keep; drop db_log; create table db_log; " (or truncate, but that takes a long time too)
But then I would be stuck with two log tables, "48-hour_db_log" and "db_log_keep". I could use a view to "union" them so they would appear as a single table, but that's not ideal either.
However, it seems as this method is what will work best for my set-up, unless there are other suggestions??
Method 4:
...eagerly awaiting ideas!!! :-)
(Also, whatever tips and/or links to info on maintaing VLDB's are greatly appreciated. )
Thanks in advance for your help! :-)
Nikolai
View 4 Replies
View Related
Jul 20, 2005
I'm in the process of migrating a lot of data (millions of rows, 4GB+of data) from an older SQL Server 7.0 database to a new SQL Server2000 machine.Time is not of the essence; my main concern during the migration isthat when I copy in the new data, the new database isn't paralyzed bythe amount of bulk copying being one. For this reason, I'm splittingthe data into one-month chunks (the data's all timestamped and goesback about 3 years), exporting as CSV, compressing the files, and thenimporting them on the target server. The reason I'm using CSV isbecause we may want to also copy this data to other non-SQL Serversystems later, and CSV is pretty universal. I'm also copying in thisformat because the target server is remotely hosted and is notaccessible by any method except FTP and Remote Desktop -- nodatabase-to-database copying allowed for security reasons.My questions:1) Given all of this, what would be the least intrusive way to copyover all this data? The target server has to remain running and berelatively uninterrupted. One of the issues that goes hand-in-handwith this is indexes: should I copy over all the data first and thencreate indexes, or allow SQL Server to rebuild indexes as I go?2) Another option is to make a SQL Server backup of the database fromthe old server, upload it, mount it, and then copy over the data. I'mworried that this would slow operations down to a crawl, though, whichis why I'm taking the piecemeal approach.Comments, suggestions, raw fish?
View 2 Replies
View Related
Mar 28, 2015
Our system runs a SQL Server 2012 DB, it has a table (table_a) which has over 10M records. Our system have to receive data file from previous system daily which contains approximate 3M updated or new records for table_a. My job is to update table_a with the new data.
The initial solution is:
1 Create a table (table_b) which structur is as the same as table_a
2 Use BCP to import updated records into table_b
3 Remove outdated data in table_a:
delete from table_a inner join table_b on table_a.key_fileds = table_b.key_fields
4 Append updated or new data into table_a:
insert into table_a select * from table_b
As the test result, this solution is very inefficient. Step 3 costs several hours, e.g. How can I improve it?
View 9 Replies
View Related
Jul 9, 2007
Hi Sql Gurus.
Please help me i'm in troublle now.
I have Two databases,that same schemas and same tables. that both servers are different location. but data must be update and input from both sites.that data must be same both.
so i've planed to replication data both servers,then i must chose merge replication right?
so i'm set
server1 (assumed server1 as main) are Distributor/Publisher
and
server2 just Subscriber only
that i can configuration successfully.
then i'm chose push replication from server1 but it's not work.
this is error message that show
The process could not deliver the snapshot to the Subscriber.
please advise me what's wrong and how to fix problem?
Thank you.
View 5 Replies
View Related
Jul 20, 2005
Hi all,I am doing some large updates,that may update 10,000 plus rows.This works fine when I execute the SQL directlyin Query Analyzer.If I set the timeout on my VB connection to 0 (zero)the connection should not time out????But it does.If I set the time out to a high value, say 1200,I get the same problem well within 1200 seconds.Also, I am getting the problem that the log fills up,but it is set to auto grow????Any ideas would be appreciated.ThanksGreg
View 1 Replies
View Related
Nov 14, 2007
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies
View Related
Nov 16, 2006
Can a SSIS server, i.e. staging server be used to create packages that update SQL Server 2000 Analysis services objects on another server running SQL server 2000 OLAP?
It appears that the OLAP connection manager ion SSIS supports only connections (and thus updates) to 2005 OLAP objects. I work for a company that has a huge investment in a 3rd party DW that uses Analysis Services 2000. the DW tool vendor will not support an upgrade to SSAS 2005. We wish to extend the DW from other data sources. My thought was to use a staging server with SSIS, used solely as the ETL tool for all new development (thus no more DTS development), and to update the sql server 2000 operational data store on another box.
I can find no documentation on how to process sql server 2000 analysis services objects from within an SSIS package. Any ideas?
View 4 Replies
View Related
Mar 6, 2015
I have stored procedure .In SP i am using cursur to load data from Parent to several child table.
I have attached the script with this message.
And my problem is how to use direct select and insert or load to speedup the process instead of cursor.
USE [IconicMarketing]
GO
/****** Object: StoredProcedure [dbo].[SP_DMS_INVENTORY] Script Date: 3/6/2015 3:34:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
View 3 Replies
View Related
Feb 14, 2006
I am trying use call DirectRowToOutput0 more than once to achieve the goal, but it gives an error of calling COM. I can create more than one destination to achieve it, but I need create 8 destinations for my situation. Is there any easy way?
Thanks
View 5 Replies
View Related
Oct 22, 2015
I have a job which executes hourly.
Essentially:
Begin
Truncate table A
Insert into A
(Col1,
Col2,
Col3...
)
Select Value1,
Value2,
Value3...
From Table B
End
The insert operation query takes approximately 3.5 minutes to execute. What's occurring is the Table is immediately truncated, and there are no rows in the table for those 3.5 minutes.
How can I avoid having this gap - where there are no rows in the table for that period of time during the job execution ? The table could be locked, but that doesn't seem like the best solution.
View 8 Replies
View Related
Sep 11, 2007
Hi,
I need to import an SQL string from MS Excel 2003 to SQL SERVER 2000.
The string I need to import is composed by 5 different several blocks and looks like:
Code Snippet
CommandLine01 = "USE mydb"
CommandLine02 = "SELECT Block ..."
CommandLine03 = "GO
ALTER TABLE Block...
GO"
CommandLine04 = "UPDATE Block..."
CommandLine05 = "SELECT Block..."
The detail of the SQL string is at:
http://forums.microsoft.com/msdn/showpost.aspx?postid=2093921&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
I am trying to implement OJ's suggestion:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2117223&SiteID=1
to use multi - batch processing to import the string to SQL SERVER, something like:
Code Snippet
Dim SqlCnt, cmd1, cmd2, cmd3
'set the properties and open a connection
cmd1="use my_db"
cmd2="create table mytb"
cmd3="insert into mytb"
SqlCnt.execute cmd1
SqlCnt.Execute cmd2
SqlCnt.Execute cmd3
Below is the code (just partial) I have, and I need help to complete it.
Thanks in advance,
Aldo.
Code Snippet
Function TestConnection()
Dim ConnectionString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
ConnectionString = "Driver={SQL Server};Server=myServer;Database=myDBName;Uid=UserName;Pwd=Password"
ConnectionString.Open
CmdLine01 = " USE " & myDB
CmdLine02 = " SELECT ACCOUNTS.FULLNAME FROM ACCOUNTS" ...
CmdLine03 = "GO
ALTER TABLE Block...
GO"
CmdLine04 = "UPDATE Block..."
CmdLine05 = "SELECT Block..."
RecordSet.Open CmdLine01, ConnectionString
RecordSet.Open CmdLine02, ConnectionString
ConnectionString.Execute CmdLine01
ConnectionString.Execute CmdLine02
'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next
ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet
'Close ADO objects
RecordSet.Close
ConnectionString.Close
Set RecordSet = Nothing
Set ConnectionString = Nothing
End Function
View 7 Replies
View Related
May 18, 2006
Hi,
I have a large text which contains special characters and I need to insert it in "ErvTxt" field which is a varchar field.
I get an error:
Error:Line 1: Incorrect syntax near '1'.
Unclosed quotation mark before the character string ', 19 )'.
At line 4467SQL statement: INSERT INTO tbl1 (ZPERS, Status, ErvTxt, VENTS, DNR, ANAME, Fall, Zust, GER, Empf, BET, SSK, GA, JAHR, PZ, LAUFNR, RVLAUFNR, EDAT, ETIME, VDAT, UTLAUFNR, Bew, AINR, ErvNr, PrintStatus, FristNr, HDAT, HTIME, RVExportiert, CrDat, CrPers, GATT, AZ, NR) VALUES ( 'STS', -1, '0123'1'00'0138-33/02(1)'J'F'452'R020311'KL01'ZB'01'C'00001'03'i'000000000'430200001'20030116'0905''000000460'20030116'1100'0242'3'15'00'00000001000'''-'00000000000100''''''''00100û¥00000000000100û¥00000000000100û¥0000000000010000000000015699200000'0000000363û¥00000000001000û¥00000000001000 rrrrrrrrrrrr ssss''RI20030'101012003010'1200301160'0''''''''''''''''''''', '', 0, '0138-33/02(1)', 'J', 'F', 97, 'R020311', 'KL01', 'ZB', 1, 3, 'i', 0, 430200001, '2003-01-16 12:00:00', '1899-12-30 09:05:00', NULL, 460, 0, 2927, 1744, 0, 0, '2003-01-16 12:00:00', '1899-12-30 11:00:00', NULL, '2003-01-16 04:04:04', 'STS', 'C', '00001', 19 )
Can anyone tell me what's wrong?
Thanks.
View 4 Replies
View Related
May 21, 2013
What will be the best way to go to select summary data from a big table with detail records and insert it into another table.
The source table contains approx 100 million detail records for a couple of months. I have a select statement that select a summary of the latest month's data and it average about 15 million records for the month which i want to insert into another table.
In the past i just used a standard insert into statement but not the best way of doing it.
If a view is created with just the last months summary data and i select from the view will the performance be better or will it just add more overhead?
Will a SSIS package work better to insert the summary data?
View 1 Replies
View Related
Apr 25, 2008
im sure there must be function for this but i cantfind it anywhere
essentially i want to do a Large insert on a table around 3000 records
which links to a table using 3000+ non sequential IDS
the content will be the same but bar the ids
roughly along the lines of
Code Snippet
insert into comment
values
('Some comment',date,commentypeid, non sequential List of IDs)
i could do this fairly simply in c# using a List<int>
Code Snippet
foreach (int ID in IDS)
{
insert into comment
values
('Some comment',datetime.now,1234, ID)
}
surely there is a a function
Thanks in advance
Jake
View 2 Replies
View Related
Apr 9, 2008
how do i insert a large chunk of text into a table column. my project is to build a news website. where people can go and read news articles. the articles are provided by the author in word format, so how do i insert that news article into the table's column? any help would be appreciated
thanks
View 2 Replies
View Related
Feb 9, 2007
Hello!I have a developer that is playing around with some SQL statementsusing VB.NET. He has a test table in a SQL 2000 database, and he hasabout 2000 generated INSERT statements.When the 2000 INSERT statements are run in SQL query analyzer, all2000 rows are added to the table. When he tries to send the 2000statements to SQL Server through his app., a random number ofstatements do not get executed. But, SQL Profiler shows that each ofthe 2000 statements are getting sent to the server.I suggested that he add a "GO" statement at the end of the INSERTblock, but the statement fails when that is sent to the server.I know that this is not the ideal manner to insert bulk data to thesystem, but now we are all just curious as to why SQL server doesn'texecute each individual INSERT.Any thoughts?
View 3 Replies
View Related
Sep 4, 2007
Hello,
I have an SSIS package that basically inserts a large amount of data into a SQL Server table. The table contains sixty five columns, and a single load of data can contain two million records.
The 'loads' are split up into several 'daily' flat files. The package uses a ForEachFile loop to process each of the files. As each file is processed, the data from the files is loaded into a SQL Server table (destination).
Apparently, as the package is running, tempDB begins to consume a lot of disk space. The data file for TempDB on this particular server is configured to grow in 50mb increments with unrestricted file growth. During the last run of the package, the data file grew to 17GB. I ran the following and got the data file size down to 50mb;
USE TempDb
GO
DBCC SHRINKFILE(tempdev, 1)
Should I consider incorporating this code as part of the package, or is there something else I should consider to configure the SSIS package so that I don't run into space problems with TempDB?
Thank you for your help!
cdun2
View 2 Replies
View Related
Dec 19, 2006
Hi,
I'm testing Mirroing.
1) I have dedicated NIC for Mirroring - 100Mb
There is no issue with the network (file of 25MB goes in 2.5 seconds)
2) I'm issuing the next simple command
Select * into dbo.Table2 from dbo.Table1
3) the size of the table is 25MB
in async mode it takes 3-4 sec (as if it runs only local)
in sync mode it takes 25-29 sec !!!!
WHY ? IS THIS NORMAL ?
is there any configuration i can change ?
View 5 Replies
View Related
Oct 10, 2007
Hi all,
I have an existing database, which is used to store complaints data and have one field, which stores notes in a varchar(8000) column.
My users are now wanting to store information, which exceed the number of available characters. What options do I have in facilitating this?
Many thanks
View 5 Replies
View Related
Oct 17, 2005
I need to do a conditional insert, and I am afraid that if I do it in 2 steps it will create bugs...I have a table called customers and a table called lockscustomers has 3 fields CustomerID, FirstName, and LastNamelocks has fields LockID, CustomerID, UserID, SessionID, and TimeStampI need to check if there are any records in Locks for a CustomerID and if there are none, I need to insert One.This is going to tell my application that this Customer record is locked so no one else can edit it, until the Lock record is deleted.I just don't want to make 2 trips, One to check if the Lock exists, then One to insert the Lock, since this could allow 2 locks to be created if the timing is correct.Any ideas or comments?
View 1 Replies
View Related
Dec 26, 2007
Hi everybody !!! I have a problem with database SQL server 2000, and I need anyone help me.
I have PC machine with hardware configuration following:
+ RAM : 512M
+ CPU : 2.4 GHz (intel Pentium 4)
+ System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 1
(this is call Machine1 )
and other Server machine with hardware configuration the following :
+ RAM : 2G
+ CPU : 3.6 GHz( intel Xeon)
+System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 2
(use RAID 5)
(this is call Machine2 )
.And SQL Server 2000 is installed in these machine.
I created one table and one script to insert several records into this table. and script for inserting:
declare @count int , @max int
set @count =1
set @max = 5000
WHILE (@count < @max or @count = @max)
BEGIN
insert into TBAT_STOCKEOD(TRDT,SEQNO,COMPID,TRANSTYPE) values('20071219',cast(@count as varchar(5)),'13215','1')
set @count = @count +1
END
I execute this script on Machine1 and Machine2 :
+ Machine1 : spent 3 seconds.
+ Machine2 : spent 30 seconds. I don't understand why Machine2 is slower than Machine1 ( while configuration of Machine2 is better than Machine1's ). I don't know what happen.
I checked resource usages in perfmon when SQL server execute script and result following :
Machine1:
Proccessor used 100%
whereas,Machine2 used physical Disk 100%
Is there the way to configurate SQL server to improve speed writting of hard disk?
View 5 Replies
View Related
Apr 20, 2001
p.s. my email was incorrect in the last mail.
Hi all,
is there a sql 2k thread. Am interseted in finding out what the largest database size of a sqlserver database people have worked with.
We have a 1.2 Terabyte db with about 150-200 million new rows being processed everyday. Would like to share some thoughts on this with other people who are working with this much data and what they are doing with it.
bhala
----------------------------------------
Please check us out at: http://www.bivision.org/bivision
View 2 Replies
View Related
May 29, 2007
My question is two fold:
We have a database 65 GB in size and has grown over 12 years.
1) How can I upgrade to 2005 without downtime?
2) Our upgrades on SQL 2000 now can take upwards of 10 hours to add just a column and rebuild index tables?
Any way we can speed this up without detaching the database and going offline?
thanks,
Larry Sitka
View 6 Replies
View Related
Jul 14, 2004
I could not insert Unicode strings into SQL Server 2000 DB by using OleDbAdapter (insert into command), e.g: "á à u".
Please give me an instruction.
Thank you.
View 3 Replies
View Related
