SQL Server 2000 Deadlock Concurrent Select Transcations
Oct 14, 2007
There are a series of traces of transactions for essentially the same units of work. Each includes a retrieving the same table "Select" from the database. The first 4 transactions ran concurrently. The last one ran about 7 minutes later with no other concurrent transactions.
The elapsed times for the database accesses for each of the first 4 is significantly higher than the last one.
Can you think of any database optimazation that might improve this?
Are there any utilities available than can be used for stand-alone testing of SQL Server database performance?
E.G. they would measure response time for reading/writing large/small amounts of canned data once/multiple times with concurrent/nonconcurrent access.
SQL Server Database is on 2000.I am not sure about the ISOLATION level in the Java code.
Basically I am new to SQL Sever....Please ans the above questions and provide me the direction to proceed on these questions
View 5 Replies
ADVERTISEMENT
Dec 16, 2004
Standard costs 2000 euros approx (5CALs)
Enterprise costs 40000 euros apprx
Standard only supports 2GB or RAM
How many concurrent users do you think standard edtion can handle in a Dual Xeon 2.8 with 2GB ram ?
Enterprise is very expensive. sucks that standard can't handle more than 2GB Ram
View 1 Replies
View Related
Feb 20, 2008
Hi,I would like to know if its possible to add/subtract/multiply/divide values between a cell in a database and a textbox/label on a web form? how would I go about doing this? Also, I would like a number from a cell to appear in a label when a page first loads. How can I achieve this?
View 8 Replies
View Related
May 12, 2003
Does anyone know how to reserve a number of concurrent user licenses /per server logins in MSSQL 2000? I have a situation where I have more users than I have licenses for an enterprise SQL application and would like to make sure that a certain group of users always has access to the system. Individuals who only need inquiry access should only be allowed to login if there are licences available beyond the core group of users. Any Ideas? Thanks.
View 2 Replies
View Related
Feb 8, 2006
My SQL Server has kicked out a deadlocked process, which should only berunning a select statement, though there is another select on one ofthe tables in the WHERE clause (see code below). Can anyone tell mewhether this is possible, or is it that my system, which is re-usingconnections, is trying to complete an earlier statement? I've lookedthrough the system and think I'm committing all transactions.The query I'm running (simplified, I don't use daft names like 'table1'or 'date_col', honest :) is:SELECT table1.*, table2.*, table3.*FROM table1, table2, table3WHERE table2.col1 = table1.col1 AND table3.col1 = table1.col2AND table1.col3 = 'xyz'AND (table1.date_col >= '2006-02-01' OR table1.date_col IN(SELECT date_col FROM table1 WHERE (col4 = 'A' OR col4 = 'B') AND col3= 'xyz'))ORDER BY table1.date_colThanksJ
View 2 Replies
View Related
Jan 31, 2005
I have a simple select query that selects data from a view. I consistently get a deadlock exception when running this query:
Server: Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID #) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The view is a simple select statement that has WITH (NOLOCK) as a hint on all tables. I thought I understand how deadlocks worked, two threads are holding a lock and request the other's item. How does a read uncommitted select statement participate in this?
If I look at the other processes in the current activity and run sql profiler, there is no other activity and no existing locks at the time the statement is run.
Can anyone explain this? Or should I bounce my server and hope it never happens again?
Thanks,
Dave
View 1 Replies
View Related
Nov 17, 2006
Hi
Can we identify the reason for a Deadlock , after it has passed. I mean can I get to knw the reason as to why the deadlock occured yesterday on my production box.
I had not left the profiler on ... neither did I send the dead lock erorrs to the SQL error logs using the DBCC TRACEON (1204, 3605, -1)
Is there some way we can figure it out .
Please help.
View 2 Replies
View Related
May 12, 2008
Hi ,
I have some issues with deadlock.I am getting deadlock becuase of two
select on same table. The sql server log is like this ,
Select statements should have always shared lock.I am not getting why
its doing exclusive lock and creating deadlock.
5/6/2008 12:38 spid4s Unknown Deadlock encountered €¦. Printing
deadlock information
5/6/2008 12:38 spid4s Unknown Wait-for graph
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:1
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594050117632 (8d036f07c58f)
CleanCnt:3 Mode Flags: 0×0
5/6/2008 12:38 spid4s Unknown Grant List 3:
5/6/2008 12:38 spid4s Unknown Owner:0×12E9F160 Mode: S Flg:0×0 Ref:1
Life:00000001 SPID:68 ECID:0 XactLockInfo: 0×353D1C54
5/6/2008 12:38 spid4s Unknown SPID: 68 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:€™OR€™Xdes:
0×2FBB67F0 Mode: X SPID:112 BatchID:0 ECID:0 TaskProxy0×0792E378)
Value:0×38baa20 Cost0/11888)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:2
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594049986560 (6f02e1cd37c3)
CleanCnt:3 Mode:X Flags: 0×0
5/6/2008 12:38 spid4s Unknown Wait List:
5/6/2008 12:38 spid4s Unknown Owner:0×12396EE0 Mode: S Flg:0×2 Ref:1
Life:00000000 SPID:90 ECID:0 XactLockInfo: 0×0AA8178C
5/6/2008 12:38 spid4s Unknown SPID: 90 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:€™OR€™Xdes:
0×353D1C30 Mode: S SPID:68 BatchID:0 ECID:0 TaskProxy0×13B3E378)
Value:0×12e9e780 Cost0/6164)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
If you have any idea regarding this please let me know ASAP.
Thanks in advance .
View 4 Replies
View Related
Feb 25, 2004
Hello
I'm trying to run an update on data in one SQL Server, based on data from another SQL Server (they're linked). It seemed to take forever, even with just one row in the table, so I tried making a dummy query that just printed some stuff on the screen. This also took forever, so I tried doing the same thing, but not as a transaction. Then it worked perfectly!
What is the problem with linked servers and transactions? Does anybody have an explanation as to why they can't be combined? Or even better, does anybody have a solution for combining them?
Thanks
Martin N Jensen
View 3 Replies
View Related
Apr 10, 2008
Lets say that within a procedure, you start a transaction. Within the transaction there are three blocks of code, each surrounded by try/catch blocks. The first statement succeeds, but the 2nd fails and doesnt enter its catch block because for example it was inserting into a table that didnt exist at the time the procedure ran. So then, the engine will terminate the procedure and return, but there will still be a transaction open. How can you handle this situation?
View 10 Replies
View Related
Jul 23, 2005
I've got a view that creates a parent child relationship, this view isused in Analysis Services to create a dimension in a datastore. Thisquery tends to deadlock after about 10 days of running smoothly. Onlyway to fix it is to reboot the box, I can recycle the services for aquick fix but that usually only works for the next 1-2 times I call theview.This view is used to create a breakdown of the bill-to locations fromContinent-Global Region-Country-Sub Region-State/Province- City-ZipCodeYes, I know that sounds crazy, but it was a requirement.So why would I get a deadlock on a SELECT Query? Is there a way to setthe Isolation level to Repeatable Read for a view?Here is the view code:CREATE View dbo.vwBillToas-- US ZipCodeSelect 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')',z.Zipcode_WK as 'Child',z.ZipCode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToWherez.US_Region_wk IS NOT NULLGroupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrtUnion--CitySelect 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) asvarchar)+')',z.City as 'Child',z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zWherez.US_Region_wk IS NOT NULLGroupby z.State_Long,z.City,z.State_shrt,z.US_Region_wkUnion-- Canada ZipCodeSelect 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +')',z.Zipcode_WK as 'Child',z.Zipcode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion--CitySelect 'Parent'=z.Province_Long,z.City as 'Child',z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion-- Canada ProvinceSelect 'CANADA',Province_Long,Province_LongFromdbo.DIM_POSTAL_CODES_CANGroupby Province_LongUnion-- CountrySelect t.Region_NK,c.Country_Name,c.Country_NameFromdbo.DIM_COUNTRY cInnerJoin dbo.DIM_WORLD_REGION tOnc.Region_WK=t.Region_WKWherec.Country_Name Is Not NullGroup by t.Region_NK, c.Country_NameUnion-- SubRegionSelect c.Country_Name,sr.US_Region_Name,sr.US_Region_NameFromdbo.DIM_US_REGION srInnerJoin dbo.DIM_COUNTRY cOnsr.Country_wk=c.Country_WKGroupby c.Country_Name, sr.US_Region_NameUnion--RegionSelect sr.US_Region_Name,c.State_Long,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'Fromdbo.DIM_US_REGION srInnerJoin dbo.DIM_POSTAL_CODES_US cOnsr.US_Region_WK=c.US_Region_WKGroupby sr.US_Region_Name, c.State_Long,c.US_Region_wkUnion-- ContinentSelect Null,Region_NK,Region_NK[color=blue]>From dbo.DIM_WORLD_REGION[/color]WhereRegion_NK Is Not Null
View 1 Replies
View Related
Nov 10, 2015
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
View 5 Replies
View Related
Jun 16, 2006
Hi folks,
That is the question: how many concurrent connections support Microsoft SQL Server 2005?
Thanks in advance,
Javier.
View 3 Replies
View Related
Feb 2, 2015
Is there a way to find the max number of sessions that were on a sql server since its last restart? I know how to get how many total connections there were since last restart, and the current number of sessions, but not the max number of sessions that were running since last restart. Also, opinion on the optinum number of allowable sessions, or a good way to figure out the balance?
View 0 Replies
View Related
Jun 16, 2015
I am STIGing a database and have to create a trigger that will stop any connection from occurring after a certain limit has been reached. For example I set the concurrent connection to 50 so I need a trigger that will stop any connection from all logins happening after 50.
The STIG info:
Rule Title: SQL Server must limit the number of concurrent sessions for each system account to an organization-defined number of sessions.
STIG ID: SQL2-00-000100
The below triggers works, but for only the one login, so how can I have it include all logins for that SQL instance.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
View 2 Replies
View Related
Nov 9, 2006
If applicable, what's the limit on concurrent queries supported by SQL Server 2005 Express? Also, if there's a limit, does next connection after the max errors out or merely queues up?
Thanks,
Phillip
View 3 Replies
View Related
Feb 2, 2015
I am researching this and playing with using the syss.sysprocesses view and counting the dbid column. I would most likely want to store this information in a table with a date/time stamp because we really want to track our concurrent users during the day for awhile. Do you have any neat tricks in getting this done? Is there a DMV or Information_Schema view that could be used to accomplish this task? I found the following code on the internet:
SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame AS LoginName,
nt_domain AS NT_Domain,
nt_username AS NT_UserName,
hostname AS HostName
FROM sys.sysprocesses
[Code] ....
View 1 Replies
View Related
Dec 19, 2006
what is maximum no. of concurrent Logins Possible on a SQL server 2005 Database?
View 3 Replies
View Related
Jan 18, 2006
Hi all, i have a trouble when i want to select 100.000.000 records in a table, Pls, give me a solution thanks
View 4 Replies
View Related
May 4, 2006
Suppose there's a table named [Category], which has 2 columns:
CategoryID int,
ParentCategoryID int
Each category, except the top most, has a parent category, it's a 1-to-n parent-children relationship.
Now I want to write a stored proc./function that accepts a CategoryID input parameter, and output all the descendent CategoryIDs (son, grand son, ...). How to do that in MSSQL 2000?
View 1 Replies
View Related
Mar 29, 2007
The SQL statement below works without the WHERE statement but as soon as i try to use the where it stops working, I think it has something to do with the HAVING statement in the vwMultiAE2 view but I am not sure.It did the same thing when I had it in one view. Anyone know what is going on here?SELECT Ch, Reg, FMM, AE, Acc, Totals FROM vwMultiAE2 WHERE (g1ID = @g1 or @g1 = 'All') AND (g2ID = @g2 or @g2 = 'All') AND (g3ID = @g3 or @g3 = 'All') AND (AEID = @g4 or @g4 = 'All')
vwMultiAE2 >
SELECT TOP 100 PERCENT
Ch,
Reg,
FMM,
AE,
Acc,
Totals,
g1ID,
g2ID,
g3ID,
AEID
FROM
vwMultAE
WHERE
Account_ID IN (SELECT Account_ID FROM vwMultAE GROUP BY g2ID, Account_ID HAVING ( COUNT(Account_ID) > 1 ))
ORDER BY
g2ID, Account_ID
View 5 Replies
View Related
Dec 16, 2007
Hi all,
I have to write a select query which need some logic to be implemented.
Query is like
select name,number,active,
if active ="true" then
select @days=days from tbl_shdsheet
else
@days=''
end
from tbl_emp
In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet
So how can i write queery for this.
View 3 Replies
View Related
Jun 17, 2004
Is anyone have already encountered such a problem :
When a table is locked by several locks (key, pag), either a SELECT request which does not need to lock any row cannot succeed.
In SQL Server Enterprise Manager, I see that my request is waiting for a lock "LCK_M_S" !!!
How I can avoid this kind of situation which seems to me completely abnormal.
I have never been faced with this kind of problem in Oracle.
Thanks for anyone who will help me to resolve this "bug".
View 4 Replies
View Related
Apr 4, 2006
i am facing a problems, hope that anyone who know the answer can help me.
i am using sql server 2000 and vs.net. i wan to retrieve a data from the table which only select 3 top row based on their different contingentID. the table have different contingent and each contingent have 5 row data. i wan to retrieve 3 top row based on the total group by contingentID and sum up the total to order by the total and generate the ranking based on total. what i can do is only retrieve every row of data in that table. so anyone know the answer please help me, or if u dun understand my question , can message me..thank your
Skyline...
2006-04-04
View 3 Replies
View Related
Mar 19, 2008
Hai friends,,
I have a table name "Student"
it contain 2 fields no ,name
no name
1 Raja
2 Larsen
3 Ravi
4 Ankit
5 Eban
my questions is I have a webform random.aspx
whenever any user open a webform random.aspx
it should display anyone of name in a random order..... CODING:-
Dim cn As New System.Data.sqlclient.SqlConnectionDim rd As sqlDataReader
cn.ConnectionString = "Persist Security Info=False;User ID=sa;Initial Catalog=master;password=david;"
cn.Open()Dim cmd1 As New SqlCommand("select no,name from Student", cn) ' how i can chage to random order....
rd = cmd1.ExecuteReader
rd.Read()
Response.Write(rd(0) & "." & rd(1))
rd.Close()
thank u..
Ambrose...
View 4 Replies
View Related
Jul 20, 2005
Hi All,I am facing problem in MS SQL Server 2000. It is behaving slow forselect statements. It is even slower than MS ACCESS. For example, if iuse"Select count(*) from tbl;". i get the results after long time ifthere is more than 100k rows. What might be the possible reasons forthis??ThanksHoque
View 1 Replies
View Related
Feb 14, 2008
Hi Guys.
I just want to ask some insights on SQL Server Deadlock and what is the best way to handle deadlock in asp.net?
Or something like a Try... Catch.. statement to handle the error?
Please advice. Thanks in advance.
View 6 Replies
View Related
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
May 30, 2007
We've got a 3rd party application that periodically runs SQL commands throughout the day. We've been getting issues with this application showing a sql error:
Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
In checking the processes on SQL Server, there were a lot, and process ID 71 was actually hitting a completely different database.
Is there a way to streamline how SQL Server handles processes, and what's the limit at any given time?
View 4 Replies
View Related
Mar 27, 2007
I have some ASP.NET C# code which executes a stored procedure in SQL Server via the SqlCommand and SqlConnection classes.
One of the stored procedures that gets executed is giving the error: "Transaction (Process ID 272) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." This only happens occassionally.
Is there a way to get around this in my ASP.Net application? One thing I tried is ensuring that no 2 users entered the stored procedure concurrently:object synclock = new object() ;
lock (synclock) {
// execute SQL stored procedure
...
} This did not solve the problem, and I'm not even sure if that is the correct implementation to ensure sequential execution of the stored procedure.
View 1 Replies
View Related
Aug 10, 2007
I'm investigating my production server because there appears a deadlock every day. So, in SQL Profiler, I use the the Deadlock graph to capture the trace in a file.
When I click on the textdata to see the graph an error appears:
- Failed to initialize deadlock control.
Cannot find process victim in process list.
I also get the following error from another deadlock:
- Failed to initialize deadlock control.
Object reference not set to an instance of an object.
I stopped my trace, so this can not be the problem.
Does anybody knows why I can't see the graph?
I also captured the trace in a table and then used the following query to see it in xml:
select convert(xml,textdata)
from TableName
This works fine.
View 2 Replies
View Related
Mar 3, 2007
HI,
I have a framewrok that runs tests and keeps updating the status of the tests to the DB. They are approx 20 tests whose status will be updated simultaneously. Recently i have seen the follwoing error
{"Transaction (Process ID 84) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."}
I am using SQL server 2005. Any suggestions?
Thank you
View 1 Replies
View Related
Jan 24, 2008
Hi Guys,
I have one situation. I need to find out the processes which are all in deadlock state. I my figure out that SQL server maintains the process details in sysprocesses table.
Can any one please help me, Whether this is the table which contains deadlock details. If not where are they stored and How to kill that process ?
Thanks & Regards,
Senthil
View 38 Replies
View Related
