SQL Server Job Timeouts?

Jul 23, 2005

Subject: SQL server job timeouts?

We have a job that uses WinHTTP inside a stored procedure. We have
another SP wrapper that runs this for a couple hundred records.

When we add this as a job using SQLAgent it times out half way. Running
it from SQL Query analyser and it completes to the end.

Anyway to set the timeouts for jobs?

View 5 Replies


ADVERTISEMENT

SQL Server Timeouts

Mar 29, 2007

I am getting Timeout Errors quite often and cannot figure out why. I am using Enterprise Library 2.0 when accessing the database. It is not from any particular function or page either and when I check the database there may only be 2 or 3 connection from my app. Any ideas of what could be causing this? Below is my error and stack.
 
Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
 
Stack:
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at HM.Security.SecurityData.GetAllUsers(Int32 filter) at HM.Admin.Security.SecuritySearch.SetUpAutoFill() in f:InetpubWwwroothomemiSectionsactSearch.aspx.cs:line 74 at HM.Admin.Security.SecuritySearch.Page_Load(Object sender, EventArgs e) in f:InetpubWwwroothomemiSectionsactSearch.aspx.cs:line 26 at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

View 1 Replies View Related

Timeouts In SQL Server

Sep 5, 2001

A timeout issue is occurring resulting in following error.
This issue is sporadic and may be related to network.
This error occurs on the client: Microsoft OLE DB Provider for SQL Server error `80040e14`
OLE/DB provider returned message: Timeout expired
"conn_info".asp, line 444

View 1 Replies View Related

Sql Server 2005 Timeouts

Sep 6, 2006

Getting an issue with SQL Server 2005 in my production environment and
wanted to see if anyone else has seen similar behavior. First the
environment:

SQL Server 2005 on the backend, main site is running on ColdFusion 5
and there are various internal applications written in VB6 and .NET.

Upgraded to SQL Server 2005 about three weeks ago and since then about
once a week the server throws a fit and causes all sorts of timeouts.
Event Viewer is showing a bunch of same event:

Event ID: 8628
Description: A time out occurred while waiting to optimize the query.
Rerun the query.

The events occur a rate of about 10/minute and basically lock up the
server. Last time this happened I was not able to open a connection to
the SQL Server to even analyze the issue. All connections timed out...
Had to shutdown and restart the server to recover.

The first time this happened I saw a lot of waittypes
RESOURCE_SEMAPHORE_QUERY_COMPILE in the processes table and thought
that this might be related to the known bug with 2005 that has to do
with implicit conversions between Ascii and Unicode. This might be the
case since I know we have a lot of NVARCHAR columns and most searches
are using ASCII text so the result looks something like this:

SELECT cols FROM table WHERE nvarchar_column = 'Something'

There is a published article regarding this issue here:

http://support.microsoft.com/?kbid=919636

Any thoughts?

Thanks

View 8 Replies View Related

Question About SQL Server/Query Analyzer And Timeouts

Mar 29, 2006

I was given a list of user comments from an old application and told to match it up with our current system.  After all the easy methods I was still left with 95% of the records unmatched.  The only way I had left was to try and match up the names.  Given that people could have the same name I added a count to make sure it only found one record.  This query has been running for over an hour now.  How do I tell if it's actually running, or if it locked up?
Also here is the query I'm using, does anyone have any feedback on it?  Is this poorly written, is my logic wrong?  Did I put the server in a loop?
UPDATE user_table_1SET id = s.idFROM user_table_2 AS sWHERE RTRIM(s.first_name) = RTRIM(user_table_1.first_name) AND RTRIM(s.last_name) = RTRIM(user_table_1.last_name) AND (SELECT   COUNT (*)  FROM   user_table_2 AS cs  WHERE   RTRIM(user_table_1.first_name) = RTRIM(cs.first_name) AND  RTRIM(user_table_1.last_name) = RTRIM(cs.last_name)) = 1

View 2 Replies View Related

SQL Server Integration Services And Web Service Timeouts

Feb 20, 2007

Hi,

A coworker of mine is experiencing some problems with SQL Server Integration Services (SSIS) and long running Web Service calls. Any feedback on the problem would be greatly appreciated.

The problem is as follow:
He has a large SSIS-package that, among other things, contain a few Web Service Tasks. All of the tasks are executing as expected for small amounts of data. All the Web Service Methods have in common that they have a long running time. The running time depends on the amount of data.

During requests with large amount of data the Web Service task fails with the error message €œThe operation has times out€?, but on the server they see that the service call completes as expected (after quite some time, approx 200sec).

The Web Service tasks are using an €œHTTP Connection Manager€?, created in the SSIS package. The timeout-value is set to 300 sec (which is the largest value for the property). My coworker expected that this value was the same as setting the timeout value for a Web Service proxy object for any other project.

After testing the Web Service from a Console application, with 300 sec timeout, the app executed as expected with no timeout exception.

After doing some testing, he€™s quite sure that the SSIS task times out after 100 seconds, which is the default .NET 2.0 timeout value for a WS proxy, even though the timeout property in SSIS is set to 300 seconds.

As a work around he have created a console application that does the web service call, and then use the €œexecute process task€? in SSIS. This is off course a €œdirty€? hack, and he want€™s to use the €œHTTP Connection Manager€? task instead.

So the question is: Have anyone experienced this problem before? Is there a limitation on the HTTP Connection Manager task? Is the timeout value on the HTTP Connection Manager task the same as setting the timeout property on a WS proxy? Why isn€™t this €œtimeout€? value picked up my SSIS?


Again, thanks for any help!

View 5 Replies View Related

SQL Server Admin 2014 :: How To Close Active Connections Or Create Connection Timeouts

Dec 9, 2014

we have roughly 22 people connected to one database. But after a while, their applications begin to drag due to in and out communication with the server. When i check the active connections on the sql server, some times i see 157 active connections, please how to i set a timeout or connection interval close, so as reduce the heavy load being put on the server. Or how can i automatically close connections when they get higher than 50 connections.

This settings should be sql server 2008 related.

View 5 Replies View Related

Timeouts

Jul 30, 2001

Hello,

Has anyone experienced timeouts on SQL Server 2000 that happen at regular intervals?

Regards,
Brent.

View 2 Replies View Related

Timeouts

Apr 21, 2004

Anyone else expirencing some heavy duty timeouts?

View 2 Replies View Related

Timeouts: What Are All Of The Possible Causes

Apr 1, 2008

of query timeouts in SQL Server Management Studio?

I have increased the timeout setting in many places and still receive a timeout message within 40 seconds for certain update queries that involve large tables and many records. The only workaround is to break up the job into smaller queries but this makes management, unmanageable.

Is there a solid alternative to SQL Server Management Studio?

View 6 Replies View Related

Timeouts!!

Jul 23, 2005

hi!I am having some timeout issues.I am running sql 2k with 3gig available ram.I did a 600,000 record delete on a table that gets written to by theactive/production application and my application timed out when it wasdoing the delete.why did stored procedures that ran fine before I started the largedelete slow down?Some of the procedures which slowed down were accessing the same tablewhere i was doing the delete.Thanking you in advance!!parez

View 1 Replies View Related

ODBC TimeOuts

Apr 27, 1999

Hello all,
I am managaing an NT infrastructure that supports a web application. The web server is configured to use an ODBC System DSN to access data on an SQL server over a 100MB private segment. When users run a particular query through the web page, they get an ODBC "Timeout Expired" message.
Does anyone know a fix for this. I could not find anything that explicitly identified this problem in MS Support. The query runs fine from an Eneteprise Manager T-SQL window both on the server and remotely.

Thanks in advance,
Ed Molinari
Technical Architect
Eamerald Solutions

View 2 Replies View Related

Problem With Timeouts

Jul 20, 2005

I've had my SQL server database running for two years now without aproblem.However, just today one of the main tables started returning an error.The table is contained within a database called engineering. I backit up once a week and the file size is up to about 40 MB.The error returned when trying to return data from one table(DbLucent) is:"[Microsoft][ODBC SQL Server Driver]Timeout expired"I can open/query any of the other tables in the database. I can opendesign table for this table. But it won't return any query.I'm debating whether to restore the database from the last backup.Any suggestions would be appreciated. Being located reomotely, Irather not fly back to the city where the server is and work on itthere either.-David

View 6 Replies View Related

Connection Timeouts - A Tough One!

Dec 16, 2003

I'm having major problems with SqlClient Data provider connections in my application. I'm using Win 2003 Server with SQL Server 2000 (both on the same machine), and my application is developed in VB.NET.

I'm using the following db connection string:
Data Source=(local);Initial Catalog=database;Uid=user;Pwd=password;max pool size=100;

As you can see max pool size is set to 100. The SQL Database has it's MAX_CONNECTIONS property set to >32000.

The symptoms are that at seemingly random times, not nessecarily during peak hours, the application becomes extremely slow and stops responding. The error message that is beeing generated is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

After monitoring and debugging, I've found two things.

1) At random times, zero-three times a day, the number of database connections (according to performance monitor) rises from normally 2-8 up to 20-25. After a while, usually 10-60 minutes, it drops back to normal. This is directly related to when the timeout errors occur. It doesn't seem to happen more often during peak load, for some reason. I suspect there is connection leakage in the application, but I've done extensive debugging, and I just can't find any. Also, since the peaks occur randomly and I can't reproduce the error in my development environment, I have no solution to this problem. A few weeks ago, the application ran during heavy load without this problem for 7 days. Before and after that, there have been errors at least every second day. That confuses me even more.

2) The number of connections (monitored in perf mon) never reach above 25. Why? I'm using SQL Server enterprise edition, it's connection limit is over 32000. ADO pool size is 100, and I can't find anything else that would limit the amount of concurrent connections. I'm totally confused. Why does number of connections never rise above 25? Is there a setting hidden somewhere that I just can't find? Also, according to Performance Monitor, I don't get any failed connects, I just get failed commands. Maybe that's a clue? Could the problem some kind of deadlocks in the database that causes timeout?

I realize this entire post is rather confusing. It reflects the state of my mind, I guess, after working around the clock on this issue for a month :)

Any help is deeply appreciated!

View 2 Replies View Related

Linked Servers - Timeouts

Jun 24, 2004

I have a basic UPDATE that's something along the lines of:


UPDATE LocalTable SET LocalColumn = RemoteColumn
FROM LocalTable INNER JOIN server.catalog.dbo.RemoteTable ON (...)


After running in Query Analyzer for ten minutes, I get:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].

I checked the linked server settings in EnterpriseManager and both timeout settings are at 0 (no timeout). Any idea how I can remove whatever timeout is causing this?

Anyone know how remote join performance compares to moving remote data over via DTS, running an UPDATE, and dropping the data?

View 1 Replies View Related

Database Mail Timeouts

Feb 29, 2008

I'm having a problem with SQL Server 2005 not being able to send all of my messages. Occasionally when we batch a large number of messages together, we run into problems because the SMTP server can't process them fast enough, and the message doesn't send due to a timeout. I know I have increase the number of retries, but I'd rather just increase the timeout setting. Does anyone know where I might be able to do this at? The mail admins are also looking at modifying the rules in SMTP so that messages from these servers don't go through the vigor of SPAM and phishing detection. Hopefully that will speed it up, but I'd like to give it a little extra leniency on the SQL side as well. Any suggestions?

View 3 Replies View Related

Tables Seem Too Big - Timeouts Happening

Jul 23, 2005

I have two problems I need some help with.First, I've just inherited a system and am delving into a few timeoutproblems that are causing problems for the users.Now, if I do a simple select * from the table (which looks to be thecause of the problem at this stage) in QA, I get the results back inless than a second. If I open the table in EM it takes about 10. Isthere a difference in viewing the data this way ? I'm used to EM beingvirtually the same speed. There is only one row. Minor questionreally, just something I'd like to understand if there is adifference.CREATE TABLE [QUERY] ([QUERY_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[CAT_ID] [numeric](18, 0) NOT NULL ,[QUERY_DESCR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_ID] [int] NOT NULL ,[IND_EURO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULLCONSTRAINT [DF_QUERY_IND_EURO] DEFAULT ('N'),[IND_DGCOLUMNS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL CONSTRAINT [DF_QUERY_IND_DGCOLUMNS] DEFAULT ('N'),[NO_GROUPS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_GROUPS] DEFAULT(0),[NO_FIELDS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_FIELDS] DEFAULT(0),[NO_LINES] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_LINES] DEFAULT (0),CONSTRAINT [PK_QUERY] PRIMARY KEY CLUSTERED([QUERY_ID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [FK_QUERY_QUERY_CATEGORY] FOREIGN KEY([CAT_ID]) REFERENCES [QUERY_CATEGORY] ([CAT_ID]) ON DELETE CASCADE ON UPDATE CASCADE) ON [PRIMARY]GOI don't think any re-indexing has been done on this (or the othertables in the db). I was wondering if constant adding/deleting rowscould cause the index to be massive and in need of a good clear out.Any pointers would be appreciated. From what I can tell, there wassome problems trying to get replication to work. I need to dig deeperto see if this is now correct.-------------------------Secondly, there is a another table in the same database.CREATE TABLE [FIELD_DATA] ([ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[DATA_ID] [numeric](18, 0) NOT NULL ,[FIELD_ID] [numeric](18, 0) NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,CONSTRAINT [PK_FIELDDATA] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GOIt holds approx 4 million rows. The rest of the tables have minimaldata and about the same amount (consider them the same if you will).Now, another 'copy' of this database is held elsewhere (differentclient data) and this holds 40 million rows. The difference is thatthe first DB is 4.5GB and the second 6.5GB (approx). Does this provemy theory that re-indexing would be a good idea ?ThanksRyan

View 3 Replies View Related

Timeouts - Disk Queues

Jul 20, 2005

HiGot a strange problem.For some reason our web client box times out occasionally. Maybe afew times a day.Nothing appears in the logs.What I do know is that the disk queue ramps up to <high>(think top ofthe graph), processor queue jumps up and the tps drops to 0 (naturallyenough!) along with reduced pagefile usage. Usually at this time thetps is between 20 and 300. Running a pair of mirrored 18gb scsi disksfor the whole server (yeah yeah) and a whopping 640mb memory. Oh anda single piii 1 gig. Sql2k standard vanilla, server2k.What server trace events is it worth my catching to try and get abetter Idea of what may be causing this? No major jobs (backups etcseem to be occuring at these times).Cheers

View 1 Replies View Related

Fill Stops /timeouts

Jul 20, 2005

Dear Group,I am tring to use a command that calls the server to fill an adapter, itnever seems to get to the adapter, command and the server either times outor does not respond. The timeout is set at 10 hours. I am using VisualStudio to acces MS SQL - Server.I think I have all the rights and permissions set correctly. Also, I haveused this command to fill other adapters and tables.Does anyone have a suggestion.Jeff Magouirk

View 1 Replies View Related

Stored Procedures And Timeouts

Apr 29, 2008

im testing some database cleanup scripts that dont seem to be working. im calling stored procedures through a VBScript file to clean out a table in the database at night. there are a couple of tables to clean out (around 12 tables) and i have a sleep in between each call to the stored procedure.
the sqlcmd im using is in this format:

cmd /C osql -E -S computernameSQLEXPRESS -d database -Q ""{CALL SP_MY_PROC}" -l 20 -o debug.txt

so this is called a few times, once for each table, and sleeps for ~10 seconds before the next call to give some lead time.
the output i see is below

[SQL Native Client]SQL Network Interfaces: Error Locating
Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections.


now when i run these SPs manually in SSMSE, it executes fine with no problems. is there some timeout valuee im doing wrong? or am i using the wrong syntax for the osql?

View 3 Replies View Related

Intermittent Connection Timeouts

Apr 6, 2007

I'm having an issue with what appears to be SQL Server 2005 deciding to randomly ignore new connections.



I currently have two virtual servers - one running just SQL Server 2005, the other running Reporting Services, Windows Sharepoint Services and Team Foundation Server.



For 3 weeks, it was all working perfectly, then on Wednesday night the server (and both Virtual Servers) was rebooted after installing the latest updates for Windows. Since then, I've had this issue.



It will work fine for a while, then it'll start throwing loads of Errors and Warnings into the Event Log, all along the lines of unable to connect to the database. The Reporting Services Configuration utility throws up the same problem. Then randomly, it'll start working again.



If anyone has any ideas, they would be much appreciated as this is driving me crazy!



Thanks!

View 2 Replies View Related

Timeouts Like Clock-work Every Night.

Sep 21, 2007

We get the following errors at the same times every night (between about 3AM and 4AM) on our distributor (ss2000). The publisher and subscribers do not log anything unusual during this time period but are busy. The distribution agents time-out with:
Timeout expired (Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
---------------------------------------------------------------------------------------------------------------

exec sp_MSadd_repl_commands27hp 2, N'pacefs', 0x0013D1040000A2BF00010013D119000008080142000003D70002F4A10000001E0003A1000000003E01000029007B00430041004C004C002000730070005F004D00530069006E0073005F00740062006C005F0063006F006E00760065007200730069006F006E005F006400610074006100200028003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F0029007D00060000004C007B00370030004300460037004100420041002D0041003600430034002D0034003300340045002D0039003500310045002D003300360037003000410044003500320036004500330035007D000C00050000005765656B310C00000C00000043757272656E744D6F6E746801000000010000000C000000060000003173742054440100010000000C00000008000000484C4320576573740C00000009000000526566696E616E6365010000480000000C000000050000004E61696C730C000004000000323239340C000000090000004A656464204C6172610C00000C0000004C656E64696E6720547265650C000000000000000C00000007000000333431353338320C0F0000003239353433323330202020202020200E00002E0032003000300037002D00300039002D00310030002000310032003A00350036003A00330034002E003400360030000C000B0000004C454E44494E47545245450C130000004C454E44494E4754524545204E4554574F524B0C09000000524546494E414E43450100000000000001000000000000000713040016003400300032003000300030002E0030003000300030000713040016003400300035003000300030002E00300030003000300001000000540000008E01000000000000010000000000000001000000000000000C000000090000004A656464204C6172610C0000050000004E61696C730C000008000000484C4320576573740C000000060000003734363937330C0002000000464C0C0000000000010000002005000001000000480000000100000001000000010000000000000001000000000000008E, 0x0013D1040000A2BF00010013D119000008080142000003D70002F4A20000001E00033D000000003E01000029007B00430041004C004C002000730070005F004D00530069006E0073005F00740062006C005F0063006F006E00760065007200730069006F006E005F006400610074006100200028003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F0029007D00060000004C007B00370031003500370037004500420031002D0034003200450035002D0034004300420041002D0039004600320036002D003300360037003000420030003400450039004200370030007D000C00000000000C000000070000004D6F6E74683131810C0000000A000000556E61737369676E6564818C0C00000006000000457175697479810C040000004E6F6E650C000000030000003931360C0C0000004E69636F6C61205365

exec sp_MSadd_distribution_history 7, 3, N'The process is running and is waiting for a response from one of the backend connections.', 0x0000000000000000000000000000, 0, 0, 0.00, 0x00, 1, 0x0000000000000000000000000000, 0, 0x01, 0x01



This is driving me crazy as there appears to be little to go on and I don't know what to do next.

Any useful suggestions?

Thanks,

Michael

View 3 Replies View Related

Timeouts On Delete With Image And Varbinary(max) Data

Oct 8, 2007

I have a database that I am using as an archive for emails and am storing them in varbinary(max) data types. The database works fine for inserts and retrieval but I cannot delete large sets of records ( > 30K) without it timing out. I assume this is because SQL server isn't simply releasing the handle to the blob but is instead doing a lot of work reclaiming the space.
Is there any flag I can set or approach I can use to resolve this issue?

I was considering moving the blobs into a seperate simple table and putting async triggers on the primary to delete the blobs, will this work?

Any ideas are appreciated. Besides the "store files in the file system" idea.
- Christopher.

View 4 Replies View Related

Random Timeouts Related To Multi Processor Usage

Apr 30, 2007

Hello all,



Has anyone else experienced random SQL timeouts and checked the processor usage and found that processors 0 and 1's usage patterns are mirrored (when one goes up the other goes down)? We're running into this all of a sudden.



SQL 2000

fairly small db and very low usage at this time < 100 connections/hour.

only web services, SQL 2000 running on the machine, SQL 2005 is installed but currently stopped.

1GB memory

Windows Server 2003 SP 1.



2 processors (dual core) in use.



SQL Usage of the processors according to performance monitor becomes an exact mirror where proc 0 goes high proc 1 goes low, if proc 1 is 25% then proc 0 is 75%.



Thanks

Bob



View 5 Replies View Related

Creating Clustered Index On View With Table Containing XML Data Types Takes Forever And Causes Timeouts

Apr 21, 2007

I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?



The table definition is displayed below.



CREATE TABLE [dbo].[AuditLogDetails](

[ID] [int] IDENTITY(1,1) NOT NULL,

[RecordID] [int] NOT NULL,

[TableName] [varchar](64) NOT NULL,

[Modifications] [xml] NOT NULL,

CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]



The view definition is displayed below.



ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING

AS

SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications

FROM dbo.AuditLogParent P

INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1



The definition for UDF f_GetModification



ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )

returns xml

with schemabinding

as

begin

declare @pidstr varchar(100)

SET @pidstr = LOWER(CONVERT(varchar(100), @PID))

return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')

end





The definition for UDF f_GetIfModificationExist



ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )

returns Bit

with schemabinding

as

begin

declare @pidstr varchar(100)

SET @pidstr = LOWER(CONVERT(varchar(100), @PID))

return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')

end



The Statement to create the index is below.



CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]

(

[ID] ASC,

[RecordID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

View 1 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

Memory Configurations For SQL Server 2005 EE X64 With SQL Server 2000 EE X32 On Windows Server EE X64 Server

Apr 20, 2007

My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.

I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?

View 8 Replies View Related

How To Move Sql Server 2000 Database From One Server Box To Server 2005 On Another Server Box Sql

Aug 10, 2007


Hi,

We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.

When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?

Thanks.

View 1 Replies View Related

How To Move Sql Server 2000 Database From One Server Box To Server 2005 On Another Server Box Sql?

Aug 10, 2007

Hi,

We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.

When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?

Thanks.

View 5 Replies View Related

Server: MSg 17, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server Does Not Exist Or Access Denied

Jun 6, 2007

Hi,

When I am trying to access SQL Server 2000 database from another machine i got this error

Server: MSg 17, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied

but I could access the database on same server and in that server i could access other databases in different server.



View 6 Replies View Related

SQL Server 2005, SQL Server Mobile, SQL Server Management Studio. Unsupported HTTP Function Call

Sep 5, 2005

Hi All,

View 14 Replies View Related

A Call To SQL Server Reconciler Failed. SQL Server 2005, SQL Server Mobile Merge Replication

Oct 2, 2005

Hi,

View 36 Replies View Related

SQL Server 2000 Databases To SQL Server 2005? Enterprise Manager And Management Stuio At The Same Server?

Feb 13, 2008

When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that
we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.

Questions:
1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server?
2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected
problems when running both 2000 and 2005 SQL Server at the same database server?
3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?

TIA,
Jeffrey

View 4 Replies View Related







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