Transact SQL :: Reducing Deadlocks From Conflicting Delete Statements
Jun 15, 2015
I am working on an app that getting quite a few deadlocks due to delete statements. Â I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks. Â I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.
What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap. Â
Open this url in new tab [URL] ....
View 7 Replies
ADVERTISEMENT
Feb 13, 2014
I am not sure if i am looking correctly at the deadlocks but i see deadlocks between two select statements.These statements are being run through an application. Below is the table schema from where the select is being performed
CREATE TABLE [dbo].[CMS_LOCKS7](
[PARENTID] [int] NOT NU, Â --we have a non clustered index on this column
[CHILDID] [int] NOT NULL, --we have a non clustered index on this column
[ISMEMBER] [int] NOT NULL, -- we have a non clustered index on this column
[ORDINAL] [int] NULL,-- we have a non clustered index on this column
[Code] ....
View 12 Replies
View Related
Jun 25, 2015
We are not allowed to write delete statement in a function. It will give the below error.
"Invalid use of a side-effecting operator 'DELETE' within a function."
We will get the same error for truncate/drop statements.
What is the reason behind this error?
View 8 Replies
View Related
Nov 21, 2015
The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time.The table has clustered index on ID column
----session 1 --------
begin transaction
select * from a1
update a1
set id = 22
where id = 2
----session 2 --------
begin transaction
select * from a1
update a1
set id = 22
where id = 2
Now to avoid deadlock in the above scenario we should use (UPDLOCK) hint in the select statement.Now my question is that deadlock will be avoided in this case when both the sessions use UPDLOCK hint. If only one session uses UPDLOCk and other does not then there will be deadlock .For example session 1 uses UPDLOCK hint this will hold the U lock on the row, but the session 2 does not use this hint and apply shared lock on the same row. Now there will be deadlock when session 1 tries to update the record and is blocked by shared locks of session 2. same will be the case with session 2 and both will wait for each other and hence dead lock.so what steps can be taken to avoid deadlocks in this case. I do not want to use Snapshot isolation.
View 3 Replies
View Related
Oct 22, 2015
I have a SQL server 2014 environment. How to set up a mechanism which will throw a mail alert whenever we get a deadlock on the system.
View 2 Replies
View Related
Mar 11, 2008
I would like to run more than 100 delete statements from a database.
is it possible to use all delete statements in one single stored procedure.
Thanks.
View 1 Replies
View Related
Aug 13, 2002
I have a database that I am splitting the data using odd account numbers and even account numbers. The odd acct numbers in one database and the even in the other database.
This database is very large. The problem is when I run the delete statements
it is going to fill up the log files. Can I turn on the "Simple" mode on the database while I am deleteing the data. Will this cause a problem? Then can I turn back on the 'Full' mode when I have finished?
Has anyone ever done this and so how did it work. Or better yet is it possible?
Thanks,
Dianne
View 2 Replies
View Related
Sep 21, 2007
Hi
I was curious whether it's possible to audit DELETE statements in the MS SQL database. I created a procedure (below), but I didn't find any event associated with DELETE statements.
Any help will be greatly appreciated!
Thanks,
Alla
CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @StatusMsg varchar
declare @ServerTraceFile varchar
set @ServerTraceFile = 'E:Program FilesMicrosoft SQL ServerMSSQLTraceAudit_Info'
set @maxfilesize = 1024
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec @rc = sp_trace_create @TraceID OUTPUT, 0, N'\hostnamedbauditlogmy_dir', @maxfilesize, NULL
print @TraceID
if (@rc != 0) goto error
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
--SELECT @StatusMsg = 'sp_trace_setstatus' + ' Error - ' + @TraceID
-- display trace id for future references
select TraceID=@TraceID
goto noCursor
error:
select ErrorCode=@rc
noCursor:
return
GO
exec sp_procoption N'sp_Turn_Audit_On', N'startup', N'true'
GO
View 3 Replies
View Related
Dec 1, 2006
I'm trying to find information and/or articles on what exactly "Do not replicate DELETE statements" does in transactional replication. How does this affect the Publisher, Distributor, and Subscriber? Does information deleted on the publisher not replicate to the subscriber? What scenarios would someone use this option on a article?
Sorry noob to replication and SQL 2005....
View 6 Replies
View Related
Dec 10, 2002
Hi, I am using sql2000 ent edition. I have a partitioned view based on 8 tables. My selects and inserts are fine. But, when I run a delete on the view based on a query on the paritioned column, I get a "Transaction (Process ID 149) was deadlocked and has been chosen as a victim".
I looked at the query plan and it was showing a parallel query on all the underlying tables. So, I put the Option(maxdop 1), using only one processor and the delete worked fine.
Does anybody know why? is parallel query create deadlocks? is there any known problems with deletes on partitioned views?
same question for updates. I think I have the same problem for updates.
Any help will be useful.
thanks!!
View 1 Replies
View Related
Nov 20, 2006
In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:
DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL
SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?
Gerrit
View 3 Replies
View Related
Apr 21, 2007
Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.I have been looking for an answer to my problem, but unfortunately I couldn’t find one. So I created a user here on www.asp.net just for making this post.
Before I continue I just want to apologies if there is another post where this question is already answered.
Please watch this Print Screen I just took: � http://www.bewarmaronsi.com/Capture.JPG “
As you can see the “INSERT, UPDATE, and DELETE Statements� are disabled, and that’s exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.
The MDF file is located in the App_data folder and is called ASPNETDB.
And when I try to add custom SQL statements, it gives me Syntax error near “=�. Something like that. I bought the Total Training Set1 package and it works perfect in their examples.
I just want to thank you for reading my post and I hope that you got some useful information for me.
By the way, I’, from Sweden so you have to excuse me if my English is rusty.
Thanks!
PS: Can it be that I’m running windows Vista?
View 4 Replies
View Related
Mar 26, 2008
I have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance
the screenshot links:
http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG
View 7 Replies
View Related
Oct 25, 2004
Auditors want us to track when Insert, Update and Delete failures occur. Is this possible in SQL 2000?
They also want us to track schema changes. Is this possible?
Thanks, Dave
View 5 Replies
View Related
Dec 4, 2006
I previously asked What does "Do not replicate DELETE statements" do? to make sure I was correct on my thinking of what "Do not replicate DELETE statements" does. And after finding out it does what I would like it to do, create an archive db, I've tried several testing scenarios to see if it would work. So far I have been unable to not replicate delete statements. I'm not sure if I'm not setting a property right or what and any guidance would be appreciated.
Here is what I've done.
Created a blank db to be used as a subscriber and created a test db with some random data in a table.
Setup a New Publication on the db with the random data as "Transactional Publication"
Selected the following articles and properties in the publication
Tables
DELETE Delivery Format = Do not replicate DELETE statements
Views
Default Values
Stored Procedures
Default Values
User Functions
Default Values
Selected the default options for the rest of the New Publication Wizard steps and clicked finish.
Created a Pull Subscription on the new publication that I just created.
Let it initialize.
Then did a select count(*) query on the test table on the publication (18k+ rows) and subscriber (18k+ rows)
Then did a delete t-sql from the test table on the publication.
Then did a select count(*) query on the test table on the publication (0 rows) and subscriber (0 rows).
Now shouldn't it not delete the records on the subscription db?
View 6 Replies
View Related
Jul 20, 2006
Hi,I'm new to ASP.NET and having a problem configuring the SqlDataSource control. I am using the standard ASP.NET 2.0 "aspnetdb" database to manage user accounts. The problem is this:When using the wizard to configure my SqlDataSource control, the option to auto-generate the Insert/Update/Delete SQL statements are grayed out. I've searched this forum and found that this can be a symptom of no primary keys in the tables. However, there are primary keys (UserId), which is the default schema as generated by asp.net (aspnet_regsql.exe). When I use the wizard, I make the following choices:How would you like to retrieve data from your database?-> Select "Specify columns from a table or view"-> Select the "vw_aspnet_MembershipUsers" view from the "Name:" drop-down list-> Select "UserId", "Email", "UserName" from "Columns:"After this, still no option to auto-generate I/U/D statements. Any thoughts on why this isn't working??? Thanks,Leah.
View 1 Replies
View Related
Jul 17, 2015
We are using MS SQL Server 2008. I am running a batch job which deletes 21 days older records(6-7 million records). But daily we have transaction is going on in the database. When the delete occurs, all the insert statements got blocked and waits till the delete statement to complete. May I know why the blocking occurs?Â
View 3 Replies
View Related
May 12, 2015
I need to breakdown some information with just initials (sometimes 2, sometimes 3 if the initials are already used) SQL Server throws the error of:
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
What should I alter, or how should I write this query so I can use all of the needed case statements? (their are actually about 24 when statements, but this is just to get a working example to display)
Select
case
when employee_name Like 'Jorge Jones' Then 'JJ'
when employee_name Like 'Mike Mikes' Then 'MM'
when employee_name Like 'Albert Alvarez' Then 'AA'
when employee_name Like 'Hernandez-Sotata' Then 'HS'
[code]....
View 5 Replies
View Related
Apr 23, 2008
I have an SQL data source on my page and I select "Table". On the next screen I pick the fields I want to show. Then I click the "Advanced" button because I want to allow Inserts, updates and deletes. But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too. Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success
View 2 Replies
View Related
Jun 5, 2015
I have a database with two tables (Table1 and Table2)
looking for something like:
Table1:
+----+-------+----------+
| id  | Name | email   |
+----+-------+----------+
| Â 1 | name1 | mail1 Â Â |
| Â 2 | name2 | mail2 Â Â |
| Â 3 | name3 | mail3 Â Â |
| Â 4 | name4 | mail4 Â Â |
| Â 5 | name5 | mail5 Â Â |
| Â 6 | name6 | mail6 Â Â |
+-- +-------+----------+
Table2:
+----+------------+---------+------+
| id  | table1_ID | fee    | type |
+---+-------------+---------+------+
| Â 1 | Â Â 1 Â Â Â Â Â | 20000 Â | Â Â 1 |
| Â 2 | Â Â 3 Â Â Â Â Â | 1000 Â Â | Â Â 1 |
| Â 3 | Â Â 1 Â Â Â Â Â | 10000 Â | Â Â 1 |
| Â 4 | Â Â 3 Â Â Â Â Â | 1000 Â Â | Â Â 1 |
| Â 5 | Â Â 5 Â Â Â Â Â | 500 Â Â Â | Â Â 1 |
| Â 6 | Â Â 5 Â Â Â Â Â | 500 Â Â Â | Â Â 2 |
| Â 7 | Â Â 1 Â Â Â Â Â | 60000 Â | Â Â 2 |
| Â 8 | Â Â 2 Â Â Â Â Â | 1000 Â Â | Â Â 2 |
+----+------------+---------+-------+
i want the query that return:
+--------+-------+---------------------------------+---------------------------------+--------+
| name  | email | a:sum(fee) where type=1  | b:sum(fee) where type=2  |  b/10  |
+--------+-------+---------------------------------+---------------------------------+--------+
|name1 | mail1 | Â Â Â Â 30000 Â Â Â Â Â Â Â Â Â Â Â Â | Â Â Â 60000 Â Â Â Â Â Â Â Â Â Â Â Â | Â 6000|
|name2 | mail2 | Â Â Â Â Â 0 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | Â Â Â Â 1000 Â Â Â Â Â Â Â Â Â Â Â Â Â | Â 100 Â Â |
|name3 | mail3 | Â Â Â Â Â 2000 Â Â Â Â Â Â Â Â Â Â Â Â | Â Â Â Â Â 0 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | Â 0 Â Â Â |Â
|name4 | mail4 | Â Â Â Â Â 0 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | Â Â Â Â Â 0 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | Â 0 Â Â Â |
|name5 | mail5 | Â Â Â Â Â 500 Â Â Â Â Â Â Â Â Â Â Â Â Â | Â Â Â Â 500 Â Â Â Â Â Â Â Â Â Â Â Â Â | Â 50 Â Â |
|name6 | mail6 | Â Â Â Â Â 0 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | Â Â Â Â Â 0 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | Â 0 Â Â Â |
+--------+-------+---------------------------+---------------------------------------+---------+
View 4 Replies
View Related
Aug 2, 2006
I have SQL server Express. How can you import a database, using transact sql code, onto a server from your local PC.
View 11 Replies
View Related
Jun 3, 2015
So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....
CASE
WHEN [AddressType] = 'M'
THEN [MailingAddress].[Address1]
[MailingAddress].[Address2]
[MailingAddress].[City]
[MailingAddress].[State]
[MailingAddress].[Zipcode]
WHEN [AddressType] = 'D'
THEN [DefaultAddress].[Address1]
[DefaultAddress].[Address2]
[DefaultAddress].[City]
[DefaultAddress].[State]
[DefaultAddress].[Zipcode]
View 3 Replies
View Related
Oct 13, 2015
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
View 3 Replies
View Related
Jun 17, 2015
STEP1:
CREATE TABLE Trace(Statement VARCHAR(MAX))
INSERT INTO TraceÂ
VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted  from History'),
('Insert into Account  Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')
[code]....
now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.
View 12 Replies
View Related
Aug 5, 2015
I have a table that keeps track of all changes that were performed in an application. There is a column called "old value" and column called "new value". There are some values in the application that don't require data therefore the "old value" or "new value" values can be empty. These columns are an nvarchar data type because the value can be text or numbers or dates. An example is "ReceivedDate". There is a report that is generated based on this table.
What is happening is the query in the report dataset is adding dates when it should be displaying empty. They query is using "CASE/WHEN/THEN". What I need is "When the column is "RecievedDate" and it is not null then convert it to a date". This is for formatting purposes.
This is an example of the table:
UpdateColumn
Old Value
New Value
ReceivedDate
 7/8/2015 5:00:00 AM
ReceivedDate
7/8/2015 12:00:00 AM
7/9/2015 5:00:00 AM
ReceivedDate
7/9/2015 12:00:00 AM
So, the first time it was updated there was no value but it was replaced with July 8, 2015 and so on.This is what the report is displaying
This is the query:
CASE UpdateColumn
... WHEN 'ReceivedDate' THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...
I tried adding
CASE UpdateColumn
...
WHEN 'ReceivedDate' IS NOT NULL
THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...
But it did not like the "IS NOT NULL".
View 9 Replies
View Related
Aug 20, 2015
I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code. Each block is the same except for the date. How could I rewrite these 30 union statements into a more concise query?
Each client (people_id) has a distinct agency_id_number. So the code below totals the number of distinct clients/day and groups them by programs and facilities.
Sample data that you can use to test this report:
IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census
create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)
[Code] ....
View 3 Replies
View Related
Jun 19, 2015
I want to include GO statements to execute a large stored procedure in batches, how can I do that?
View 9 Replies
View Related
May 30, 2013
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
View 7 Replies
View Related
Jun 15, 2015
I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
View 8 Replies
View Related
Oct 22, 2015
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
View 7 Replies
View Related
Aug 19, 2015
I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
View 2 Replies
View Related
May 18, 2004
Hello All,
I work for a company that packages MSDE and MSSQL 8.0 because our software requires storing client information in a database. The problem is that we've found that if the our users have another DB engine installed (BDE or Paradox usually), our program has trouble using MSDE to access the database and thus making our software inaccessible. It behaves as if this other database engine locks on our database before MSDE can. I'm basically looking for help on either how to uninstall these other db engines or how to get around this problem so that both MSSQL and other db engines can co-exist in one system.
View 12 Replies
View Related
Jan 16, 2008
I have a server with sql 2000 and 2005 installed on it. When I try to connect to the msde 2000 database with an app or with managment studio express I get the following error
"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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476"
But When I go through dos and type osql -S(local)database -E
it connects to the correct msde instance. How can I fix this with out uninstalling sql server 2005????
View 6 Replies
View Related
