Table Loads From DB2 - Parallel Processes, Linked Server, And Lightweight Pooling Issue

Aug 17, 2007

Hello. I have a 32-bit SQL 2005 (SP1) server that is my current Production server. I also have a 64-bit SQL 2005 (SP1) server that will become my Production server. I have several SSIS packages that load/refresh data on a nightly basis to a few of my databases from DB2 (MVS). I have the packages setup on the current Production server (32-bit) and all is working well through the Microsoft OLE DB provider for DB2. However, on the 64-bit server, I am experiencing some issues with the SSIS packages failing due to large loads. Loads that are loading tables with 500K, or less, data seem to run without issue (through SQL Agent Jobs). But, larger table loads are failing.

I do have a linked server set up on the 64-bit server to the 32-bit server, for other processes. And because of this I have lightweight pooling turned off on the 64-bit server (because of distributed querying). Lightweight pooling is turned on on the 32-bit server. Could this be what is causing some of my issue? Since I don't have the lightweight pooling option turned on (on the 64-bit server), am I not getting the proper amount of through-put for my 8 dual core CPU server?

Thanks
Scottye

View 1 Replies


ADVERTISEMENT

Lightweight Pooling

Jan 15, 2004

I tried to use xml_prepare_document stored procedure when lightweight pooling is enabled. It does not work.
I tried to use sp_configure 'lightweight pooling', 0 and reconfigure.... It doesnt work.

Is there a way to disable lightweight pooling without restarting mssqlserver?

Thanks

Jordi

View 1 Replies View Related

.NET Framework :: Insert Results Of All Processes On A Server Into A Table

Oct 28, 2015

How the code inserted works.....I am trying to do the same thing with

GetProcesses

So I want to insert into a table the results of all the processes on a server and determine if a process/service is running.......

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;

[Code] .....

View 8 Replies View Related

SQL Server 2012 :: Parallel Update On Same Table But Different Columns

Aug 5, 2015

I have a table with 8 columns, I need to update data in multiple columns on this table, this table contains 1 million records, having single update was taking time so I broke the single update into multiple update statements and running multiple update statements in parallel, Each update statement updates different column.

This approach is working fine but I am getting the deadlock error.

Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I tried with various lock hints but no success.

View 4 Replies View Related

Large Table Loads && Transaction Log Issues

Apr 7, 2004

I have a table that’s about 3 gigs, using this table and a few others I’m making another table. The problem is when making the new table my transaction log inflates so much that I’m running out of disk space. What I can I do to prevent this or to keep the transaction log size under control?

View 4 Replies View Related

SQL Server Loads Over Network Take Forever

Feb 16, 1999

When I attempt to load a database from dump format across a network (100mb Ethernet) It takes forever. (15 hours for 16GB!) can anyone help me find a starting point to troubleshoot this?

Thanks!

-Chris

P.S. File Copies of the same size move at a rapid fashion, and I cannot find any bottlenecks in the network.

View 2 Replies View Related

The OLE DB Provider MSDAORA For Linked Server .... Does Not Contain The Table COUNTRY. The Table Either Does Not Exist Or The Current User Does Not Have Permissions On That Table.

Jun 13, 2006

I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
 
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.

View 3 Replies View Related

Sql Server 2005 Loads Data Slowly

Oct 1, 2007

This is the code i use.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset



Private Sub Form_Load()


Set cnn = New ADODB.Connection
cnn.ConnectionString = "driver={SQL Server};" & "server=SCHS-SQL;uid=sa;pwd=sa;database=Library"
cnn.Open

Call loadrst

End Sub

Public Sub loadrst()
Set rst = New ADODB.Recordset
Dim sql1 As String
sql1 = "select * from Books order by srno"
rst.Open sql1, cnn, adOpenDynamic, adLockOptimistic, adCmdText

If rst.EOF = True Then
MsgBox ("No records are present")
Command1.Enabled = False
Else
Call display
Command1.Enabled = True
End If


End Sub



This is the code i use basically to connect my vb6 application to sql server 2005. I had started out lately trying to use sql server instead of access. So far none of the program have given any problems as the databases has a max one of 120 records. But the one which this code connects to has about 5200 records. I had imported the tables from access into sql server. The size of the database was around 17.67mb so i shrank it and it became 4mb. But still it takes roughly 2 minutes for the user to see the records in the grid. Could you tell me what to do?

Sheldon

View 3 Replies View Related

SQLCE, .Net 3.5 And Me Going Crazy Looking For A Lighweight Solution With Lightweight Prereqs

Feb 19, 2008

I take it SSCE 3.5 only runs under .Net 3.5? My project was updated from VS05 to VS08 but the target is still .Net 2.0 would this be why I cant use the SQLCE namespace.

Forgive me for being noobalishous but I have been thinking that SSCE 3.5 would be a great solution for my app as I want to be able to use SQL Server Express 2005 and some other Local DB system (SSE is too bulky for my app when networking is not required). But if the prereq is that the client must have .Net 3.5 installed then I am back to square 1 because .Net 3.5 is like 195Mb install (which i tried installing as a prereq on my friend's Vista Ultimate(?) machine yesterday and it crashed it). Aaargh!! How are you supposed to develop small DB apps (3.5Mb max for app footprint) in the VS environment that don't require massive installation prereqs???

Dont get me wrong I love VS but surely there has to be a way to minimise prereq overheads.

View 5 Replies View Related

SQL 2012 :: Kill Processes At A Specific Time Then Log To A Table

Apr 3, 2015

So I have a job that shows active processes

INSERT INTO Query_results(login_name, total_elapsed_time, total_elapsed_time)
SELECT login_name, total_elapsed_time, total_elapsed_time FROM
sys.dm_exec_sessions

I need to then kill all sessions at 11:59pm then log all those that are killed. This is so I can schedule a job at that time, I have sessions that are blocking my job.

View 2 Replies View Related

Running UPDATE Statements In Parallel On The Same Table

Feb 23, 2007

Hi all,

Does an UPDATE statment lock the entire table or just the rows that will be affected by the UPDATE?

I ask because -

Can I run UPDATE statements in parallel on the same table on the same column. The need for doing this is because the table is a large fact table. I plan to execute the same UPDATE statements on different time sections of the data to expedite the processing.

If the UPDATE statment lock the entire table then I cannot run an UPDATE in parallel. If the UPDATE statement just locks the rows that will be affected then maybe I can because rows affected will be different for each UPDATE.

Let me know.

Thanks,

Vivek

View 1 Replies View Related

SQL Server 2008 :: Partition Sliding Window Causing Loads Of Blocks

Oct 29, 2015

We have a massive database with an almost massive amount of traffic to and from it.

I've been requested to implement a sliding window partitioning with 2 partitions an active and passive 1,I managed to test this on a very small testbed last month.

I currently moved 97k table on to the partition function leaving me another 26 k to go

I'm using the following stored procedure to implement the sliding window

CREATE PROCEDURE [dbo].[ManageFactSlidingWindow](@pFunction nvarchar(max),@pSchema nvarchar(max),@FG nvarchar(max),@moveDays int)
/*****************************************************************************
PROCEDURE NAME: [ManageFactSlidingWindow]
AUTHOR: Arshad Ali
CREATED: 02/24/2013
DESCRIPTION: This stored procedure manages sliding window for the partitioned table

VERSION HISTORY:
DATE EMAIL Company DESCRIPTION

[Code] .....

When I try to move the partition even a single day I get loads of locks.

View 0 Replies View Related

MSDE [SQL 2000] -- Server Balks When Web Page Loads -- Select Permission Denied

Oct 28, 2006

Hi folks,

I'm having trouble getting off the ground with the Web application walkthrough "Walkthrough: Creating a Web Application Using Visual C# or Visual Basic" in VS.NET Pro 2002 [Academic] documentation. After a bit of fishing around, and consulting the MS Knowledge Base, I got the pubs database installed. I also got the connection to work well enough that the dataset would fill in the IDE.

The problem is that when I try to run the web form, either from the IDE debug menu, or by accessing the .aspx file on localhost using Firefox, I get the error:
SELECT permission denied on object 'titles', database 'pubs', owner 'dbo'.
showing in the browser.
My understanding is that this page is running as ASPNET, and I did already carry out the recommended commands to enable access:
C:>osql -E -S MY-MACHINE-NAMEVSDOTNET -Q "sp_grantlogin 'MY-MACHINE-NAMEASPNET'"
C:>osql -E -S MY-MACHINE-NAMEVSDOTNET -d Pubs -Q "sp_grantdbaccess 'MY-MACHINE-NAMEASPNET'"
both of which commands returned successfully. Any suggestions as to what else I should do to get the necessary permissions to actually display the data in my browser? Does the IIS user account need permission also?

Thanks for any insight into this vexing problem. I must say that along the way, I have had some fun exploring the osql comand-line tool. Using the -E switch, I have been able to run select and upgrade queries, but this is all pretty much fishing in the dark. I would like to get back to actually working with the walthroughs in the Visual Studio documentation.

Thanks,

Joseph

View 4 Replies View Related

Processes Were Blocked By Other Processes

May 31, 2001

We are facing lot of problems with Blocking,can any one help us in this matter,The problem is as follows

We have SQL Server 7.0 running on Nt4.0, and three web servers and 5 application servers are accessing SQL server.
Till Yesterday everything was fine,Suddenly today more than 18 processes
were blocked by other(Like chain),First i killed some blocking process,then it was fine,once again it started and continuously some processes are blocked by other,and i found that all blocking process are running from webservers.I ran SQL Profiler to get some information,but no use.
I am not understanding why suddenly it happend,because we have't modified anything.Is there any way to overcome this situation,this is production server.
because of this users are getting slow responce/no responce.

----Here i want to know why it happend?
---How to trace the problem and fix it

Can any one help me please
Thanks
Ananth

View 1 Replies View Related

Append Query From Access Table To Linked SQL Server Table Failing

Jun 18, 2004

Strange one here - I am posting this in both SQL Server and Access forums

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?
Thanks,
Brad

View 4 Replies View Related

SQL Server Connection Pooling

Jan 16, 2007

Hello, I have a question about Connection Pooling. I use the following Code on three defferent machines with the same result. No machine has any special configuration. OS is Win XP Pro Sql Server 2005. One Server has Windows 2003 Web Edition with Sql Express 2005. 1 for(int i=0; i<500;i++) {
3 SqlConnection con = new SqlConnection("Data Source=127.0.0.1\DB;Initial Catalog=Test;User Id=sa;Password=secret;Connection Lifetime=30;Pooling=true;");
4 SqlCommand cmd = new SqlCommand("SELECT TOP 1 * FROM test", con);
5 con.Open();
6 IDataReader reader = cmd.ExecuteReader();
7 reader.Close();
8 con.Close();
9 con.Dispose();
10 }
11
 When  monitoring  The Counter  logins/sec in Perfmon  logins/sec getsup to 500 per second  when i call this method (everytime!). MSDN says when logins/sec does not stay zero after initial call, pooling does not work. Can you tryy it on your machine an tell me what experience you make? Does Pooling really does not work?  

View 5 Replies View Related

Connection Pooling In Sql Server

Jun 10, 2008

I want to know what is min size ,max size,Connection Timeout and Connection Leak in connection pooling i have searched it on Google i found this below answer but still i have a confusion Please explain me in simple language with example"Setting
an initial size other than zero can reduce the start time of the client
applications because it ensures that a certain number of connections
are always available. That could be an issue at around 09:00 in the
morning when all users start their applications and connect to the
server at the same time.

 Setting the maximum size of the connection pool is
usefull if you suspect that you have blocking problems due to the large
number of concurrent queries. In this case you may be able to reduce
blocking by limiting the maximum size of the connection pool. Of
course, the pool already limits the number of concurrent connections,
so you may never encounter this problem"

View 2 Replies View Related

SQL Server ADO Connection Pooling

Apr 10, 2007



Hi



I would like to find out, what is the actual formula to set the right amount of connection pool size in a conn string. In my scenario, when 400 concurrent users are logged into the website, SQL Server starts timing out. I am thinking the following connection string should fix it, but I would appreciate if I can get some tips to optimize connection pooling. I really need to find out the criteria or the formula to set connection pool size in a connection string. I got the following conn string over the web, by just searching for optimized connection pools, but I am not sure if this will work. Also please explain max and min, and how what criteria do i use to set that size, I mean what does in the following example min pool size of 5 and max pool size of 60 means. Thanks.



conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!

View 3 Replies View Related

SQL Server Connection Pooling

Apr 10, 2007

Hi



I would like to find out, what is the actual formula to set the right amount of connection pool size in a conn string. In my scenario, when 400 concurrent users are logged into the website, SQL Server starts timing out. I am thinking the following connection string should fix it, but I would appreciate if I can get some tips to optimize connection pooling. I really need to find out the criteria or the formula to set connection pool size in a connection string. I got the following conn string over the web, by just searching for optimized connection pools, but I am not sure if this will work. Also please explain max and min, and how what criteria do i use to set that size, I mean what does in the following example min pool size of 5 and max pool size of 60 means. Thanks.



conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!

View 1 Replies View Related

Altering Table Definition On A Linked Server Table.

Aug 11, 2000

SQL Server 7.0 (SP1)
Error:
------
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.

Thanks for any input.

View 1 Replies View Related

SQL Server Processes

Feb 22, 2006

What is the max number of processes that can be running at the same time?

View 1 Replies View Related

DAO &> SQL Server Processes

Oct 24, 2005

I need to know exactly what VBA lines Create and Destroy SQL ServerProcesses - i.e. those visible in Enterprise Manager under Management[color=blue]> Current Activity > Process Info.[/color]Why?I am experiencing strange behaviour with Processes that are createdwhen I create a DAO Database Object with the following line:Set m_ResDatabase = DBEngine.Workspaces(0).OpenDatabase(strDSN, False,False, strODBC)This creates the process as expected.However the following lines don't always close the ensuing Process:If Not m_ResRecordSet Is Nothing Thenm_ResRecordSet.CloseSet m_ResRecordSet = NothingEnd IfIf Not m_ResDatabase Is Nothing Thenm_ResDatabase.CloseSet m_ResDatabase = NothingEnd IfIf Not m_ResWorkspace Is Nothing Thenm_ResWorkspace.CloseSet m_ResWorkspace = NothingEnd IfIt seems as if SQL Server keeps hold of the first two Processes andthen will release any subsequent ones.Can anyone shed any light in this - or any good web pages where Imight find some answers?Regards Chris

View 2 Replies View Related

Captures All Server And Processes

Feb 28, 2008

Hi ,

I want to capture all Processes,Applications and Servers (name) which is accessing(linking) to my server. Is it Possible without using Profiler?

View 8 Replies View Related

How Can I Kill Sleeping Processes In SQL Server?

Sep 14, 2006

Dear, Our ASP.NET scripts send SQL statements (as inline SQL or SP) to process the requested job. After the job execution, the process ID stays in the server and waits for next command with sleeping status.Since this process does not go away, next job adds another process and eventually, the server is overloaded with these processes and dies. How can I kill this sleeping processes?Regards,Echo

View 6 Replies View Related

XML Services And SQL Server Orphaned Processes

May 1, 2007

Hi

Wanting to know how do you detect and kill orphaned spid in SQL Server left behind by an XML service which was not completed normally. The client requests an XML service which open a connection, and due to network problem the client did not manage to call the another service which suppose to end the connection. Thus leaving behind in SQL Server orphaned spid which continue to hold locks. This created problem when the next user wants to access the resource.

The simplest way is to get all spid which are holding locks for more than a certan period and kill them. Is there a better way to actually detect orphaned spid?



Help much appreciated.



regards

benc

View 3 Replies View Related

Linked Table/server To Db2(AS/400)

Jan 25, 2008

I'm very new to SQL server so forgive me for the newbie questions.

Basically I have some tables/files on an AS/400.
I want to have a link to those files/tables from SQL server 2005 (like how you would link a file in msAccess.

Is this possible?

View 1 Replies View Related

SQL 2005: Pooling, Application Roles And SQL Server Error 18059

Nov 24, 2007

Hi all:

[Posting this in the security forum because in this forum I found a related post.]

I have a problem with SQL Server 2005 and application roles and pooling. I needed to use application roles and I needed to use pooling at the same time for an application. I am using sp_setapprole and sp_unsetapprole. In order to ensure that the application role is always set and unset by the application, I actually developed a custom Data Provider based on the SqlClient Data Provider. I have a custom Connection and Command class that wrap the SqlClient versions. Upon opening my custom Connection class, I execute the sp_setapprole stored procedure. Upon closing or disposing the connection, I call sp_unsetapprole.

This works fine in 99% of my tests. However, I have three or four methods (always the same ones, but one only fails ever so often) that fail, but only under the following circumstances:


Pooling is turned on (but pool size doesn't matter)

I am using my custom Data Provider (using System.Data.SqlClient does not cause this issue... but I am also not using approles then)

When other tests have run in the same test run. I.e. when I run the failing methods by themselves in a test run, there is no problem.
So it seems to me that the problem is related to using application roles with pooling turned on. For scalability reasons, we cannot turn pooling off. When the methods fail, I see the following two (2) entries in the SQL Log:


Error: 18059, Severity: 20, State 1.

The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.
I understand the error message somewhat. However, I am not sure why the "reset" of the connection occurs. My code does not call reset anywhere, so it must be something that happens in the background.

I am reviewing code to see if there is possibly a situation where the sp_unsetapprole procedure does not get called or does not get called successfully, but there is a lot of code (in many custom components).

I would like to know if anyone has a suggestion on how to solve this problem, or, find the code that may be causing the problem.

Thanks in advance,

SA.

View 3 Replies View Related

Multiple/Duplicate SQL Server Worker Processes

Nov 18, 2005

I don't even know where to begin looking... I have a page that loads multiple web user controls...
I know I use one connection object class that is used in all my objects when executing the query (calling Stored Procedures).
The problem is when the first page is rendered and each user control queries the database (SQL Server),
it eventually slows down.  In my controls, I use a lot of repeaters and internal queries per each repeater item.
So I know it hits the database quite often. 

Problem is when I look in SQL Server Enterprise Manager Process Info, I have multiple worker processes sleeping.
My first thought is ASP.net is creating a new session connection (process) to the SQL Server?  Why?  How? 
What do I do to check either my code is creating the connection object properly.  Thanks!

Larry

View 1 Replies View Related

SQL Server 2008 :: Processes That Update A Certain Column

Oct 26, 2015

I have a table and a specific column inside this table. I know this table is being updated, by using sys.dm_db_index_usage_stats, I was able to determine this, by some process (stored procedure / SQL Job / etc), but the problem is, I am not sure what process is doing it.

How would I search our SQL Server 2008 database to find any process that manipulates this table / column (I only care about Inserts / Updates and Deletes, but do not really care for SELECT).

View 8 Replies View Related

What Command Do I Use To Check How Many Processes Are Currently Running On The Server ?

Feb 29, 2008



Hello friends I want to ask that by which command I can know about how many process are currently running on the server ?

Waiting for reply.......

View 7 Replies View Related

Parallel Execution In SQL Server

Oct 30, 2007

Is there any way to run a stored procedure in parallel to another one? i.e. I have a stored procedure that sends an email. I then scan a table and send any unsent emails. I do not want the second part to slow the response to the user.

View 2 Replies View Related

Creating Table On A Different Linked Server

May 6, 2007

Using Microsoft SQL 2000

When creating a table I want to be able to specify not only the db to create it on but also which server to create it on. I have two servers that are linked together, I can view all data without issue.

Doing further research it looks like with the create table command you can tell it the new table name and the database but you can't tell it which server to use. Is there a way of doing this?

Example :

CREATE TABLE LAPTOP.database.dbo.tableName (a INT) gives the following error:

The object name 'LAPTOP.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

I am new to linked servers so basically my question is, how do you point to a server within sql before I execute the create table command?

Tx in advance


Mark

View 2 Replies View Related

Foreign Key From Another Table In A Linked Server

Apr 19, 2008

Hi,

We're having a few linked servers in our company. some tables in one of the linked servers include columns (better saying, foreign keys) of tables in another server. I wonder if there's a way to create a foreign key referencing one column in another server. That is, suppose there's Column A in table A in Server A which references Column B in table B in server B, is there a way to create column A as foreign key referencing column B?

Any idea is appreciated.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved