How To Get Table Info From RID Deadlock ?
May 7, 2007
Hi There
I am trying to troubleshoot deadlock issues.
The dead lock is with a RID.
Now from various links i have established that you have to use DBCC PAGE() with relevant info to get more information, i get back a object and index id, m_objid.
However i dont know how to get the tablename form here, msdn simply says that it is an allocation id not and actual table id, so how can i from here get to table information?
All i am trying to figure out is which table is affected fromt he RID dealock, but that does not seem to be a simple process.
Thanx
View 5 Replies
ADVERTISEMENT
May 7, 2007
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
ddave
View 3 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
Jul 20, 2005
We have one user who enters a transaction and then does a single rowupdate (updates all columns but only one is changing - this is due tothe way our sql is generated in the application), at this pointanother user enter a transaction and tries to update the same row (heunderstandably has to sit and wait while he is blocked by the originaluser). The original user then updates the same row again – at thispoint the second user is chosen as a deadlock victim and killed. If Itry and recreate this with any other tables(or pubs) I get my expectedbehaviour of the original user just doing 2 successful updates and thesecond user then completing his update once the original user haseither committed his changes or rolled back. The query plan indicatesthat a drop and insert of the row is happening (this is not the casewith any other tables where we get our expected behaviour). This onlyhappens when the index is clustered - if we use a non-clustered indexit does not occur.Is this expected behaviour? it seems dangerous to me as the firstuser has not commited or rolled back his updates. It was onlyhighlighted by a fault in our application that caused the secondupdate to be executed.I have some thoughts about it being something to do with a row lockbeing relased due to a delete / insest of the row in the second update(we see this in the execution plan).....Any help much appreciated as I am struggling to get my head round howthe second user was ever able to get hold of the resource.
View 1 Replies
View Related
Sep 16, 2004
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.
Here is a basic breakdown of what I am trying to do:
Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)
Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)
I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.
Code:
SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x
This did not seem to do what I needed, it did not delete any rows at all.
I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2
Then I tried another scenerio that I also needed to do:
Code:
SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'
'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.
Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.
Any ideas on what I am doing wrong here?
View 3 Replies
View Related
Dec 29, 2005
I sometimes get the following error from an update statement in astored procedure:Transaction (Process ID 62) was deadlocked on thread | communicationbuffer resources with another process and has been chosen as thedeadlock victim. Rerun the transaction.The isolation level is READ UNCOMMITTED and there are no explicittransactions in the stored procedure. The update statement is asfollows:UPDATE PLSET PL.PL_SI_LAST_YEAR_AMOUNT = #tmpWorkPLPrior.PRIOR_AMOUNTFROM #tmpWorkPLPriorWHERE PL.COMPANY = @compAND PL.PLAN_YEAR = @yearAND PL.FORECAST_QUARTER = @qtrAND PL.VERSION_ID = @verAND PL.BUSINESS_UNIT_CODE = #tmpWorkPLPrior.BUSINESS_UNITAND PL.PROJECT_ID = #tmpWorkPLPrior.PROJECT_IDAND PL.BUDGET_CODE = #tmpWorkPLPrior.BUDGET_CODEAND PL.BUSINESS_UNIT_CODE <> 'G7'PL rows: 24,342,553PL rows - Filtered: 230,088#tmpWorkPLPrior rows: 3,641Updated rows: 43,692The temp table (#tmpWorkPLPrior) is created by a SELECT INTO statement.It has the values that need to be set in the PL table. The PL tablehas a clustered index on 8 columns. The filters (@comp, @year, ...)select 230,088 rows. When the update succeeds it updates 43,692 rowsin about 15 seconds. Why does this sometimes deadlock and other timessucceed? There is nothing else running, so the process is deadlockingon itself.Thanks,Frank
View 4 Replies
View Related
Oct 16, 2007
Hi,
We wrote some stored procedures, and some temporary table used because there some some complicated logic.
In normal testing, the application works weel, but very easy to get deadlock error in stress testing.
Error message like this:
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Sample code:
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId int
)
INSERT INTO #PageIndexForUsers (UserId)
SELECT USER_ID
FROM Users
Other tested functions will insert/update table Users too.
I searched on internet, several people said it's MS bug, anybody help?
View 1 Replies
View Related
Jul 23, 2005
I want to set an alert for a specific table whenever an event hascaused a deadlock to occur on the table.I understand how to set up an alert. But I don't know which errornumber to use for the New Alert error number property for a deadlock.Or how to specify a deadlock on a specific table.Thanks,DW
View 1 Replies
View Related
Aug 31, 2005
I am having problems with my store procedure displaying my last column name which is "DISPLAY_CATEGORY_DESC". I am trying to display the description from another table. But in order to get to the description I have to go through 2 tables. The tables look like something like this.
First TableTABLE_CATEGORY
PRIMARY_KEY = DIR_CATEGORY_ID = 5000
Column Name
DIR_CATEGORY_LVL_ID1 = 100
DIR_CATEGORY_LVL_ID2 = 200
DIR_CATEGORY_LVL_ID3 = 300
Second TableTABLE_CATEGORY_DESCRIPTION
PRIMARY KEY DIR_CATEGORY_LVL_ID1 = 100
Column Name
DESCRIPTION = "Car Model Company"
This is my DISPLAY_CATEGORY_DESC
Not sure how to get to this description. Thanks for your help.
select cwebs.SESSION_ID, webs.ENTERED_DATE, cwebs.DIR_CATEGORY_ID, DISPLAY_CATEGORY_DESC
FROM di_webclickstats cwebs
left join di_websessions webs on cwebs.session_id = webs.session_id
where cwebs.DIR_CATEGORY_ID <> 0 and webs.ENTERED_DATE between @startDate and @endDate+1
View 3 Replies
View Related
Nov 3, 2015
I am getting a number of deadlocks when inserting and deleting items from the same table.
The delete statement has a U lock and awaiting an IX lock on an index that covers the column in the where clause.
The insert statement has a IX lock and awaiting a U lock on the same index.
The delete statement is deleting about 5000 rows, where as the insert statement is inserting a single row.
Both these statements are found in stored procedures being called from LINQ to SQL.
I am wondering if there is a way I can prevent the delete statement taking the U lock out?My thinking being if the delete didn't take out the U lock then it would not deadlock with the insert. Are there any hints I could use to avoid the particular lock above?
I have seen various examples of multiple updates causing a deadlock, which can be fixed by adding multiple indexes. However, as I am inserting and deleting rows I imagine that all the indexes will need to be updated by both operations.
I have inherited the architecture and don't have the time to redesign everything at present. My backup plan is to deprioritize the delete and build in a retry mechanism.
However, it would be really good if I could find a more elegant way to handle deleting and inserting rows at the same time.
Deadlock trace information below...
11/02/2015 22:21:26,spid21s,Unknown,waiter id=process1cc9c68558 mode=IX requestType=wait
11/02/2015 22:21:26,spid21s,Unknown,waiter-list
11/02/2015 22:21:26,spid21s,Unknown,owner id=process203f31b498 mode=U
11/02/2015 22:21:26,spid21s,Unknown,owner-list
11/02/2015 22:21:26,spid21s,Unknown,pagelock fileid=1 pageid=721673 dbid=6 subresource=FULL objectname=PerforceReports_Staging.dbo.DebugReport id=lock1663f5d900 mode=U
[Code] ....
View 1 Replies
View Related
Jun 4, 2007
I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.
rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000
For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.
I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.
it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while
the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607
If some body could please help me it would be really helpful for me.
Thanks in advance!!
View 5 Replies
View Related
Jan 14, 2002
Is there a utility or function or 3rd party product that will show information about tables and other objects such as:
Last change date
Last used date
Where Used
more .....
I'm from an AS/400 environment and found it VERY useful to be able to access that kind of information to clean-up, monitor & manage the systems.
Any Ideas ?
View 1 Replies
View Related
Apr 7, 2008
Hi
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME
,HAS_PRIMARY_KEY
,KEY_HAS_IDENTITY_SPECIFICATION
FROM
...this is where you come in
ORDER BY
TABLE_NAME
View 6 Replies
View Related
Oct 25, 2007
I'm beating my head up against a wall here. I have one very LARGE table called op_hist.
CREATE TABLE [dbo].[op_hist] (
[op_trnbr] [int] NOT NULL ,
[op_type] [varchar] (80) ,
[op_date] [smalldatetime] NULL ,
[op_act_setup] [decimal](38, 10) NULL ,
[op_act_run] [decimal](38, 10) NULL ,
[op_qty_comp] [decimal](38, 10) NULL ,
[op_wkctr] [varchar] (80) ,
[op_part] [varchar] (30)
)
I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.
SELECT op_wkctr, pcs, hrs.b
FROM
dbo.op_hist
INNER JOIN
(
SELECT op_part, SUM(op_qty_comp) as pcs
FROM dbo.op_hist
where op_type='BACKFLSH'
group by op_hist.op_part
UNION ALL
SELECT op_part, SUM(op_act_setup + op_act_run) as b
FROM dbo.op_hist
where op_type='LABOR'
group by op_hist.op_part
) AS hrs
ON op_hist.op_part = hrs.op_part
where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'
Help me oby one - you're my only hope.
Sincerely,
Frustrated!
View 4 Replies
View Related
Mar 25, 2008
Hello All.
I just made some huge changes to a database on the development computer. However, I want to transfer all of the information from the Employees table in the old DB to the new DB (no changes made on that table).
Can anyone point me in the right direction?
View 4 Replies
View Related
Sep 15, 2007
I have run into a challenge that I'm not sure how to best solve. I'dappreciate any opinions or input.I am working with a third party database. They are storing some datathat I need to use in a binary field. I've got the code to parse thebinary and reconstruct what I need. Unfortunately, there might bemultiple "entries" stored in a single binary field. a certain byte,let's just say the first, will always be the count of "entries" inthis particular SQL entry.An example:then entry might be:0x01000012341234where 12341234 is the data entry that I will parse.Another possible entry is:0x03000012341234567856789ABC9ABCThe first byte indicates that there are three data values I want toparse out:12341234567856789ABC9ABCThe portions of the binary I need are always the same length and theremay be from 1 to ~100 of them. (usually 1 if it matters.)The big question:How could a SQL query return an entry for each of the "entries" in thebinary field? For the second example I would want three entries in myresults, each row returning a different section of the binary data.For the first, only one row. I'll be querying the set and expecting toget back more results than the number of entries in the set.I haven't really tried much with this yet, (other than some mentalcalisthenics were I fell down onto the mat pretty hard), so just somedirection on where to begin would be helpful.(Yes, I understand that the difficulty is that this data shouldn't bestored this way, but there is not much I can do about that.)Thanks...James FraserJoin Bytes!
View 4 Replies
View Related
Apr 24, 2008
Does TSQL (2005) offer a way to retrieve a given table's total size (data size vs index size)? Would like to be able to get at this info using TSQL (not management studio).
thanks
View 3 Replies
View Related
Apr 7, 2008
Hi
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME
,HAS_PRIMARY_KEY
,KEY_HAS_IDENTITY_SPECIFICATION
FROM
...this is where you come in
ORDER BY
TABLE_NAME
View 3 Replies
View Related
Feb 21, 2007
I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete.
Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.
View 12 Replies
View Related
Apr 5, 2001
Hi:
After I used "copy database" to upgrade a SQL 7 database to SQL 2000, all are fine except the TaskPad view. The table info section is blank. Though we know all tables are in the table object.
Any thought?
David
View 2 Replies
View Related
Oct 25, 2005
I am importing a csv file into a temp table (works fine), now I want to take the data in the temp table based off of the temp id compared to the id of the permanant file.
basically if the record id exists skip to the next line and so forth, I am able to import everything just fine but I cannot get the WHERE condition to acually work.
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 WHERE table2.col1 <> table1.col1
this is what my actual code is
INSERT INTO USER_INFO (fpu_id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id <> dbo.USER_INFO.USERID
this is the actual error that I get.
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'USER_INFO' does not match with a table name or alias name used in the query.
-----
thanks for the help
View 1 Replies
View Related
Oct 19, 2013
I used to use this code to extract coupon usage from two tables orders and coupons
select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
[code]....
Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are
ID in Promotions
and
PromotionID in PromotionUsage
Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?
View 5 Replies
View Related
Aug 24, 2006
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.I tried the above query with changing the table_name to 'db2..tab_db2'but went invain.sysobjects also dint work..Any help on this will be appreciated..regards,Sathya V
View 2 Replies
View Related
Dec 14, 2005
I'm kinda new to SQL. I'm trying to get a print out of a specific table and its columns and column types. Is there a specific command or code that I can use to accomplish this?.
thx,
Newbie.
View 3 Replies
View Related
Sep 28, 2007
i want to have a user defined function or stored procedure(i don't know which one should be used, anyway!) which returns false if the table has no records, with the table name comes with a parameter.
could you give a little code sample?
thanx
View 4 Replies
View Related
Dec 28, 2007
Hi All,
Can anyone help me find out which system table/s are out there that hold SQL Server Job/Agent information?
Thanks!
View 3 Replies
View Related
Jul 8, 2007
I have a query that gets the tables, columns, indexes, index_columns for all tables in a data base.
But, I need to be able to select any data base on the server
and then drill down into the tables, columns, etc.
I looked at sys.databases but can't see how to relate that to the
sys.tables to extend my query.
Is there a way?
I've looked at the information_schema area and catalog views
but I don't see a solution yet.
View 7 Replies
View Related
Apr 28, 2014
I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.
Here is the SELECT statement
SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu,
user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id,
user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex,
user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,
[code]....
We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is
UPDATE user_group
SET user_group.description = Consultants.description
FROM user_group
INNER JOIN Consultants
ON user_group.description = consultants.description
WHERE name like 'Adam Froth%'
but it keeps erroring and saying that it could 'Not be bound'.
View 2 Replies
View Related
May 11, 2007
MSSQL noob here. Sorry guys, but I really need some help.
I have a MSSQL (2000) database and a Products Table. Now, there was three columns used for pricing that I was not able to populate on the initial usage. So I exported the table as an Excel document, and got rid of everything except the UID (unique identifier column) the Name, and the Three columns, so that the customer could take their time in filling out the information.
Ok, so These are what are common to both the MSSQL database and the Excel sheet
column uid = unique id integer
column Name = text
column eBay = number
column PriceGrabber = number
column Amazon = number
So, any idea how I can update my Products Table with this?
Please HELP!
View 18 Replies
View Related
Mar 8, 2008
I have one database named StudInfo. It has two tables named StudentInfo, and GradeInfo.
StudentInfo conntains 4 columns. The 1st one is StudentID (PK) int, LastName varchar(10), FirstName varchar(10), and PhoneNumber int.
GradeInfo contains 4 columns also StudentID (FK) int, GradeID varchar(10), Grade int, Date Datetime.
What I would like to know is how using a T-sql query I could make a temp table with studentID, LastName, FirstName, and then the average of all the different types under GradeID. As of right now I have been limiting the names that are put into GradeID to Homework, Daily, Test, Quiz, and Bonus. When I say average I mean the average of all Homeworks under one studentID, and all Daily under one studentID... etc. I would like the info returned for each student in studentID. Allow Nulls has been turned off.
Never assume someone knows what you are talking about.
View 6 Replies
View Related
Aug 5, 2007
Hi,
I have tables parent - PurchaseInvoices and child - PurchaseInvoiceDetails. I have trigger in PurchaseInvoiceDetails that would update stock qty based on Location code stored in parent table - PurchaseInvoices upon INSERT/UPDATE/DELETE.
I also set table RI to have cascade delete. I faced problem that, when I delete record in parent table, it would cascade delete child table. Trigger in child table fired and try to update stock qty. However, in this case, trigger unable to locate parent table record anymore.
How to overcome this? I can't move my stock update code to other place since I got to update stock if any changes happen to child table.
Please advice.
View 7 Replies
View Related
Aug 29, 2006
Hi All
Can u pls tell me How to Get the SQL Affinity Mask value in Sql 2005.
and what is the table/views name that store this info.
for SQL 2000 , we were using following query:
SQLStatement.printf("select c.value from master..sysconfigures c, master..spt_values v,"
"master..syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 "
"and v.number = r.config and v.name ='affinity mask'");
lErrCode=SQLExecDirect(hStmt,(SQLCHAR*)pszSQLStatement,strlen(pszSQLStatement));
lSQLBindCol(nAffMask);
But With SQL 2005 , we are getting ZERO(0) ROWS SELECTED.
View 1 Replies
View Related
Oct 2, 2015
I need ssrs report  which  get job info from job table in list control  and other  one is jobid and workdate which also need to be group by and storeproc I am using for this  report is as follow:
ALTER proc [dbo].[VRAJ_JobScheduleReport] Â Â Â Â Â Â Â Â Â Â Â
  @JobID int = null,  @StartDate varchar(25) = null,  @EndDate varchar(25) = nullASSET
NOCOUNT ONÂ SET ANSI_NULLS ONÂ SET QUOTED_IDENTIFIER ONÂ DECLARE @sql varchar(max);DECLARE @param varchar(max);DECLARE @where1 varchar(max);DECLARE
@where2 varchar(max);SET @param='';SET @where1='';SET @where2='';IF @JobID IS NOT NULLÂ Â BEGINÂ Â Â
SET @param = @param + 'DECLARE @JobID int; SET @JobID=' + CAST(@JobID AS varchar(50)) + ';';Â Â Â IF @where1 = ''Â Â Â Â
[Code] ...
I need ssrs report which get job info from job table in list control and other one is jobid and workdate which also need to be group by and storeproc I am using for this report is as follow:
ALTER proc [dbo].[VRAJ_JobScheduleReport]
@JobID int = null,
@StartDate varchar(25) = null,
@EndDate varchar(25) = null
AS
SET NOCOUNT ON
[Code] .....
View 5 Replies
View Related
