deleting rows

I have a table with approx 5 million rows and 36 columns. It takes approx
4 minutes to delete 1 row. The table has 3 indexes in addition to it's primary key and has twelve foreign key constraints. We are still using sequel 7.
There is a backup run every night as part of the nightly maintenence that
reorg/reindexes and checks the database integrity. Any thoughts?

Thankswell I depends what your deleting on. Make sure that you're using an indexed column, and check the execution plan to make sure that it's doing a table seek and not a table scan.

What are you stats for this table set to ?|||I am using a simple delete such as delete from tablename where transsk = 1002
with transsk being the primary key. This has only become a problem once the table grew over a mil rows.|||what kind of index, clustered or non-clustered ?

and did you set a fill factor on the table ?

setting your index properly should bring down your delete to a few seconds.
I've got tables that are 6mil+ rows and a delete takes < 15 secs.|||The primary key is non-clustered with a fill factor of 90.
There are also three indexes. Two non-clustered and one clustered, all three with a fill factor of 90. It's also odd to me that inserting rows is not a problem.|||Inserting a row shouldn't be much of a problem as you don't have to seek to insert a row. If you've got a clustered index, there is a little bit of overhead as the data needs to be arranged logically. IE, it may have to shuffle other rows around to properly fit in the one you are inserting. With a non-clustered index, it can just append the row to the logical group and add an entry into the tree.

What you may want to try for benchmarking purposes is to remove the clustered index and see if you get a performance increase when inserting or deleting. I don't think you'll get much, but it's worth a shot...

have you taken a look at the execution plan for a simple delete like the one you posted ?|||Thanks, I will give that a try by removing the clustered index.
Do you have tables with as many foreign key constraints? I didn't know if 12 was a unusually large amount.

Also, I guess I'm an idiot, what do you mean by execution plan?|||If you open query analyzer, there is a button at the top that will show you the proposed execution plan that SQL server will use when you run that SQL. The execution plan is created based on statistics.

Also, I think 12 FK constraints on one table is *a lot*. You should really only have 1 to 3. That's likely the reason it's taking so long to delete anything, it's got many constraints to check before deleting a row.

Cheers,
-Kilka|||Use this sample and apply your own code and cut and paste what it returns

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SET SHOWPLAN_TEXT ON
GO

DELETE FROM myTable99 WHERE Col1 = 2
GO

SET SHOWPLAN_TEXT OFF
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

DELETING ROW DATA

Hi all,
How to delete one row of data from a table?
--
TSdelete from mytable where ...
"TS" <TS@.discussions.microsoft.com> wrote in message
news:85B4D023-FDA2-4B8A-B5DA-60EFE0268176@.microsoft.com...
> Hi all,
> How to delete one row of data from a table?
> --
> TS|||Thanks, how about if I want to delete all rows in the table?
--
TS
"JT" wrote:

> delete from mytable where ...
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:85B4D023-FDA2-4B8A-B5DA-60EFE0268176@.microsoft.com...
>
>|||delete from mytable
truncate table mytable
Take a look though at Truncate Table in Books on-line.
There are advantages to using this method but also some restrictions.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:39A8C7D5-282F-47C6-ABF5-C301303C8C5D@.microsoft.com...
> Thanks, how about if I want to delete all rows in the table?
> --
> TS
>
> "JT" wrote:
>|||Delete from Yourtablename
"TS" wrote:
> Thanks, how about if I want to delete all rows in the table?
> --
> TS
>
> "JT" wrote:
>|||Does the DBA know you are doing this?
"TS" <TS@.discussions.microsoft.com> wrote in message
news:39A8C7D5-282F-47C6-ABF5-C301303C8C5D@.microsoft.com...
> Thanks, how about if I want to delete all rows in the table?
> --
> TS
>
> "JT" wrote:
>

deleting dups and keeping only 1 row in a batch

I have asked this question before and got some great answers, I just wanted to ask this again. I can detect dups easily, is there a way to get a total number of all dups so that I can delete a certain number at a time, then check the total again for verification that that specific number of dups are gone? I'm still using 2000. If I delete dups, won't it delete the 1 row I want to keep? And I do have a unique identity column.

thx,

Kat

Kat -

You can get the dup rows using Group by with Having clause - see example:

SELECT ident = convert(int,1)

,name = 'harry'

INTO #Temp

INSERT #Temp

(ident, name)

SELECT 2, 'tom'

UNION

SELECT 3, 'dick'

UNION

SELECT 4, 'harry'

SELECT ident = max(ident), name INTO #dupids from #temp group by name having (count(*) > 1)

you can then

DELETE #Temp WHERE ident in (SELECT ident from #Dupids)

count output from the first query should match the second

Thanks

AWAL

|||

Thank you, I haven't had time to test this out but I'm sure it works.

Kat

deleting duplicate records

dear friends,
suppose i've one table, it has only to rows.the two rows are are same as it is.how can i delete one row from that table?

thank you verymuchSELECT DISTINCT *
INTO #HOLDING
FROM >your table<
GO
TRUNCATE TABLE >your table<
GO
INSERT >your table<
SELECT *
FROM #HOLDING
GO
DROP TABLE #HOLDING
GO|||you can add another column of type bigint make it auto increment by setting Identity properties to Yes. then you can mannually delete the row :)

Quote:

Originally Posted by vinod

dear friends,
suppose i've one table, it has only to rows.the two rows are are same as it is.how can i delete one row from that table?

thank you verymuch

sql

deleting duplicate record

in my table, I have several rows that are complete duplicates. Needless to
say, I can't open the table and just delete 1 row.
How do I delete the copies and not all?
Take a look at this link.
I've used it in the past to remove thousands of duplicates (dont ask how
they got there )
Once you've gotten rid of the dups, whack some contraints on where
necessary.
http://support.microsoft.com/default...b;en-us;139444
Immy
"Johnfli" <john@.ivhs.us> wrote in message
news:eXaiytCLGHA.1272@.TK2MSFTNGP10.phx.gbl...
> in my table, I have several rows that are complete duplicates. Needless
> to say, I can't open the table and just delete 1 row.
> How do I delete the copies and not all?
>
>

deleting duplicate record

in my table, I have several rows that are complete duplicates. Needless to
say, I can't open the table and just delete 1 row.
How do I delete the copies and not all?Take a look at this link.
I've used it in the past to remove thousands of duplicates (dont ask how
they got there :) )
Once you've gotten rid of the dups, whack some contraints on where
necessary.
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Immy
"Johnfli" <john@.ivhs.us> wrote in message
news:eXaiytCLGHA.1272@.TK2MSFTNGP10.phx.gbl...
> in my table, I have several rows that are complete duplicates. Needless
> to say, I can't open the table and just delete 1 row.
> How do I delete the copies and not all?
>
>sql

deleting duplicate record

in my table, I have several rows that are complete duplicates. Needless to
say, I can't open the table and just delete 1 row.
How do I delete the copies and not all?Take a look at this link.
I've used it in the past to remove thousands of duplicates (dont ask how
they got there )
Once you've gotten rid of the dups, whack some contraints on where
necessary.
http://support.microsoft.com/defaul...kb;en-us;139444
Immy
"Johnfli" <john@.ivhs.us> wrote in message
news:eXaiytCLGHA.1272@.TK2MSFTNGP10.phx.gbl...
> in my table, I have several rows that are complete duplicates. Needless
> to say, I can't open the table and just delete 1 row.
> How do I delete the copies and not all?
>
>

Deleting dupes in special cases

I need to delete all rows that match at least one of the account_id values of another row *and* that has the same email address. However, if they have the same email address and none of the account_id values then I need to keep it. I've attached a sample dataset along with the expected results.

I have this:
DELETE [acctID_emailAddress_tmp] FROM [acctID_emailAddress_tmp]
JOIN
(select emailaddress, account_id, max(contact_id_tmp) max_cid
from [acctID_emailAddress_tmp]
group by emailaddress, account_id) AS tempImportTable
ON tempImportTable.[emailaddress] = [acctID_emailAddress_tmp].[emailaddress]
WHERE [acctID_emailAddress_tmp].[contact_id_tmp] < tempImportTable.[max_cid]
AND tempImportTable.[account_id] = [acctID_emailAddress_tmp].[account_id];

but it doesn't work since it's keeping the subset of the dupe row(s).

Can someone shed some light?

TIASo why do you keep 2 instead of 1?

EDIT: You won't be able to answer that one..unless you say that because it comes "first"...is that it?|||Because it comes first :)|||OK

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(temp_id int, contact_id_tmp int, account_id int, emailAddress varchar(255))
GO

INSERT INTO myTable99(temp_id, contact_id_tmp, account_id, emailAddress)
SELECT 1, 1, 3, 'test@.acme.com' UNION ALL
SELECT 2, 1, 5, 'test@.acme.com' UNION ALL
SELECT 3, 1, 10, 'test@.acme.com' UNION ALL
SELECT 4, 2, 12, 'test@.acme.com' UNION ALL
SELECT 5, 2, 3, 'test@.acme.com' UNION ALL
SELECT 6, 2, 9, 'test@.acme.com'
GO

SELECT *
FROM ( SELECT * FROM myTable99 l
WHERE contact_id_tmp IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS L
JOIN ( SELECT * FROM myTable99 l
WHERE contact_id_tmp NOT IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS R
ON L.account_id = R.account_Id
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

You can convert that in to a delete...let me know if you can't|||Thanks very much. Looks like this will do the trick if I ever figure out the delete statement for it =\|||Did you cut and paste the example in to QA and run it?

Also, I would use that as SELECT to see the population BEFORE you unleash the delete...oh and make sure you back up the table BEFORE you do the DELETE|||Yes, I ran it in QA and got the expected results. I need to delete R.contact_id_tmp

Currently trying to figure out the delete statement...|||Come on...that's too easy

DELETE FROM myTable99 WHERE contact_id_tmp IN (
SELECT R.contact_id_tmp
FROM ( SELECT * FROM myTable99 l
WHERE contact_id_tmp IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS L
JOIN ( SELECT * FROM myTable99 l
WHERE contact_id_tmp NOT IN (
SELECT MAX(contact_id_tmp)
FROM (
SELECT contact_id_tmp, emailAddress
FROM myTable99
GROUP BY contact_id_tmp, emailAddress
HAVING COUNT(*) > 1) AS XXX)
) AS R
ON L.account_id = R.account_Id
)

GO

SELECT * FROM myTable99
GO|||OK. This just got uglier... Turns out accounts will be stored as a comma separated list in one field. See the attachment for an example.

Now, is it possible to de-dupe from a comma separated list all in one field? I know I could create a new table and loop over each account-emailAddress combo, but I'd like to know if it's possible to avoid doing this (since it will be much slower) and de-dupe straight from the source table.

Thanks again|||Read this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string

Then create a new table where all the values are on 1 row, then use the query in this thread|||I tried the UDF but I'm getting 0 rows back. I've attached the sample dataset (1 row) and the UDF call.|||You really should normalized your data...I feel I'm doing more harm than good...

EDIT: This is pure theft from this Article (http://www.sqlteam.com/item.asp?ItemID=2652) written by A most Valuable Yak (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1176) , Rob Volk (http://weblogs.sqlteam.com/robv/)

USE Northwind
GO

SET NOCOUNT ON
SET NOCOUNT ON
CREATE TABLE myTable99(temp_id int IDENTITY(1,1), contact_id_tmp int, account_id int, emailAddress varchar(255))
CREATE TABLE myTable00(contact_id int, accounts varchar(200), emailAddress varchar(255))
CREATE TABLE Tally(ID int)
GO

INSERT INTO myTable00(contact_id, accounts, emailAddress)
SELECT 1, '1,2,3,10,15', 'test@.acme.com' UNION ALL
SELECT 2, '5,10,20,30', 'test@.acme.com' UNION ALL
SELECT 3, '88,42', 'test@.acme.com' UNION ALL
SELECT 4, '1,2,3', 'test2@.acme.com'
GO

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 100
BEGIN
INSERT INTO Tally(ID) SELECT @.x
SELECT @.x = @.x + 1
END
GO

INSERT INTO myTable99(contact_id_tmp, Account_Id, emailAddress)
SELECT contact_id AS contact_id_tmp,
NullIf(SubString(',' + accounts + ',' , ID , CharIndex(',' , ',' + accounts + ',' , ID) - ID) , '') AS Account_Id,
emailAddress
FROM Tally, myTable00
WHERE ID <= Len(',' + accounts + ',') AND SubString(',' + accounts + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + accounts + ',' , ID) - ID > 0 --remove this line to keep NULL rows

SELECT * FROM myTable99
GO

-- Now just add the other SQL

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
DROP TABLE Tally
GO|||This is exactly what I needed, but...users have decided to go back and separate each account in individual rows!!

Thanks a lot for your help. I guess I'll check out sqlteam.com from now on before posting here. :)|||Even if they do...the data is still not normalized if you need an operation like this...anyway, it was a neat exercise...|||This is just 1/3 of the entire process. The data is normalized in its final state.

Deleting and Updating from Gridview

Hi,

I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @.TransactionIDAND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid) "

It gives the error that Quantityavailable is not a SET type?

Thanks if you can suggest a remedy!

Jon

jbear123:

UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @.Itemid

That is not valid syntax for an UPDATE statement.

Try this

UPDATE itemsSET Quantityavailable = Quantityavailable + 1, numtaken = numtaken - 1WHERE ItemId = @.ItemId

|||

Thanks!

Deleting a static matrix row

This seems like it should be extremely obvious. I don't know if I'm just going insane or what...

If I add a matrix to my report and then right click a "data cell", I can add columns and rows. I go ahead and add however many columns and rows I want. Now I want to delete one of the rows I just added, how do I do that?

I have tried highling the entire row, but it doesn't allow me to delete it. If I select just one cell and click delete, it deletes the column and not the row. I can just use the "undo" command right after I create it, but if I have already done numerous other things to the report, it removes all of that first.

I must be missing something very obvious here.

Well I thought this would be a very easy question and that I was just missing something extremely obvious... Is it really as hard as I think it is or possible to delete a single static row from a matrix?|||

I'm having the same problem. When you go to 'Matrix Properties', 'Groups' tab, there is an entry called '[Static Group]', but you cannot delete it as the 'Delete' button is disabled.

|||

Found the Solution:

When you add a static row, it creates a staic row and a new value field in the right hand part of the report. You need to delet this value field first and then you can delete the column representing the new Row

Its not exactly clear and is not explained in the help

Deleting a static matrix row

This seems like it should be extremely obvious. I don't know if I'm just going insane or what...

If I add a matrix to my report and then right click a "data cell", I can add columns and rows. I go ahead and add however many columns and rows I want. Now I want to delete one of the rows I just added, how do I do that?

I have tried highling the entire row, but it doesn't allow me to delete it. If I select just one cell and click delete, it deletes the column and not the row. I can just use the "undo" command right after I create it, but if I have already done numerous other things to the report, it removes all of that first.

I must be missing something very obvious here.

Well I thought this would be a very easy question and that I was just missing something extremely obvious... Is it really as hard as I think it is or possible to delete a single static row from a matrix?|||

I'm having the same problem. When you go to 'Matrix Properties', 'Groups' tab, there is an entry called '[Static Group]', but you cannot delete it as the 'Delete' button is disabled.

|||

Found the Solution:

When you add a static row, it creates a staic row and a new value field in the right hand part of the report. You need to delet this value field first and then you can delete the column representing the new Row

Its not exactly clear and is not explained in the help

Deleting a row using a Stored Procedure from a GridView

I am trying to do something that I would think is simple. I have a stored procedure used for deleting a record, and I want to call it from the "Delete" command of a Delete button on a GridView. This incredible simple SP accepts one value, the unique record ID to delete the record like this:

CREATE PROCEDURE usp_DeleteBox/* ******************************************* Delete a record using the Passed ID.********************************************** */(@.pIDas int =Null)ASDELETE FROM [Boxes]WHERE ID = @.pID

When I configured the data source for the GridView, I selected the "Delete" tab and selected my Stored Procedure from the list. As mentioned on another post I saw here, I set the "DataKeyNames" property of the GridView to my id field (called "ID", naturally).

When I click the Delete button on a row, I get this error message: "Procedure or function usp_DeleteBox has too many arguments specified." If I leave the "DataKeyNames" property empty, it does nothing when I click delete.

Can someone tell me the correct way to configure this? I am sure I am missing something obvious, and I would appreciate any suggestions. Thank you!

Do you have any other columns in the DataKeyNames field? Any DeleteParameters on the DataSource? Can you show the GridView and DataSource code ?

|||

Thanks for your quick reply! I have no other colums in the DataKeyNames field. Here is the code you mentioned:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource_Boxes" Font-Names="Arial" ForeColor="#333333" GridLines="None"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="BoxNumber" HeaderText="BoxNumber" SortExpression="BoxNumber" /> <asp:BoundField DataField="PalletNumber" HeaderText="PalletNumber" SortExpression="PalletNumber" /> <asp:BoundField DataField="DestName" HeaderText="DestName" SortExpression="DestName" /> <asp:BoundField DataField="DestCode" HeaderText="DestCode" SortExpression="DestCode" /> <asp:CommandField ButtonType="Button" ShowDeleteButton="True" /> </Columns> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <EditRowStyle BackColor="#999999" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView>

 <asp:SqlDataSource ID="SqlDataSource_Boxes" runat="server" ConnectionString="<%$ ConnectionStrings:BoxTrackSite.My.MySettings.ShippingConnStr%>" SelectCommand="SELECT dbo.Boxes.ID, dbo.Boxes.BoxNumber, dbo.Pallets.PalletNumber, dbo.Destinations.DestName, dbo.Destinations.DestCode FROM dbo.Boxes INNER JOIN dbo.Pallets ON dbo.Boxes.PalletID = dbo.Pallets.ID INNER JOIN dbo.Destinations ON dbo.Boxes.DestID = dbo.Destinations.ID" DeleteCommand="usp_DeleteBox" DeleteCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="pID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource>

I know the SqlDataSource has a DeleteParameter (I don't know how that got there). If I removed that, it does nothing. Any further suggestions? Thanks again for your help.|||

I found a solution, but it wasn't as clean as I would have liked. Following the details inthis post, I found that I was indeed passing two parameters "pID" & "ID" when the SqlDataSource.Deleting event fired. So, as the other people mentioned, I changed my SP variable to be the same name as the identity column in my database ("ID") - and this worked.

It's frustrating that this is the behaviour. The controls should support methods for doing things as I was trying to do it originally. Some developers do not have the "luxury" (or permission) to modify existing SP's.

Deleting a lock or killing a process

Hi everyone,
We are currently having a problem with our SQL server. What seems to have
happened is a lock has been put on a row and then hasnt been removed, so now
we can't update because the lock is still there.
I'm wondering what we can do about this. Is there anyway you can delete or
remove locks in SQL Server?
At the moment, using SQL Sever, I can see a list of locks on the database
and table in question. What happens if I delete them? Each lock is
associated with a process - what happens if I kill the process?
Thanks to anyone who can offer any advice on this
kindest regards
SimonHi,
Before killing the process, try to identify the issue.
I feel that u are not commiting the transaction. after a Begin TRAN.
Thanks
Hari
MCDBA
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:uXeVMVsAEHA.3400@.tk2msftngp13.phx.gbl...
> Hi everyone,
> We are currently having a problem with our SQL server. What seems to have
> happened is a lock has been put on a row and then hasnt been removed, so
now
> we can't update because the lock is still there.
> I'm wondering what we can do about this. Is there anyway you can delete or
> remove locks in SQL Server?
> At the moment, using SQL Sever, I can see a list of locks on the database
> and table in question. What happens if I delete them? Each lock is
> associated with a process - what happens if I kill the process?
> Thanks to anyone who can offer any advice on this
> kindest regards
> Simon
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0034_01C40295.3C0B6960
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Killing the process that holds the locks will rollback the transaction =of that process, thereby releasing all of its locks. However, to save =yourself this kind of trouble in the future, you should invest some time =into troubleshooting the problem. There are probably a couple of =options, but all of them come down to why a transaction has not =committed.
See if224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 =Blocking Problems
(http://support.microsoft.com/?id=3D224453) helps you out any. 171224 =INF: Understanding How the Transact-SQL KILL Command Works =(http://support.microsoft.com/?id=3D171224) might also be interesting =for you.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message =news:uXeVMVsAEHA.3400@.tk2msftngp13.phx.gbl...
Hi everyone,
We are currently having a problem with our SQL server. What seems to =have
happened is a lock has been put on a row and then hasnt been removed, =so now
we can't update because the lock is still there.
I'm wondering what we can do about this. Is there anyway you can =delete or
remove locks in SQL Server?
At the moment, using SQL Sever, I can see a list of locks on the =database
and table in question. What happens if I delete them? Each lock is
associated with a process - what happens if I kill the process?
Thanks to anyone who can offer any advice on this
kindest regards
Simon
--=_NextPart_000_0034_01C40295.3C0B6960
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Killing the process that holds the locks will =rollback the transaction of that process, thereby releasing all of its locks. =However, to save yourself this kind of trouble in the future, you should invest =some time into troubleshooting the problem. There are probably a couple of =options, but all of them come down to why a transaction has not =committed.
See if224453 INF: Understanding and Resolving SQL =Server 7.0 or 2000 Blocking Problems(http://support.microsoft.com/?id=3D224453">http://support.microso=ft.com/?id=3D224453) helps you out any. 171224 INF: Understanding How the Transact-SQL KILL =Command Works (http://support.microsoft.com/?id=3D171224">http://support.microso=ft.com/?id=3D171224) might also be interesting for you.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Simon Harvey" wrote in message news:uXeVMVsAEHA.3400=@.tk2msftngp13.phx.gbl...Hi everyone,We are currently having a problem with our SQL =server. What seems to havehappened is a lock has been put on a row and then =hasnt been removed, so nowwe can't update because the lock is still =there.I'm wondering what we can do about this. Is there anyway you can delete orremove locks in SQL Server?At the moment, using SQL =Sever, I can see a list of locks on the databaseand table in question. What =happens if I delete them? Each lock isassociated with a process - what =happens if I kill the process?Thanks to anyone who can offer any advice on thiskindest =regardsSimon

---

--=_NextPart_000_0034_01C40295.3C0B6960--

Deleting a lock or killing a process

Hi everyone,
We are currently having a problem with our SQL server. What seems to have
happened is a lock has been put on a row and then hasnt been removed, so now
we can't update because the lock is still there.
I'm wondering what we can do about this. Is there anyway you can delete or
remove locks in SQL Server?
At the moment, using SQL Sever, I can see a list of locks on the database
and table in question. What happens if I delete them? Each lock is
associated with a process - what happens if I kill the process?
Thanks to anyone who can offer any advice on this
kindest regards
SimonHi,
Before killing the process, try to identify the issue.
I feel that u are not commiting the transaction. after a Begin TRAN.
Thanks
Hari
MCDBA
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:uXeVMVsAEHA.3400@.tk2msftngp13.phx.gbl...
> Hi everyone,
> We are currently having a problem with our SQL server. What seems to have
> happened is a lock has been put on a row and then hasnt been removed, so
now
> we can't update because the lock is still there.
> I'm wondering what we can do about this. Is there anyway you can delete or
> remove locks in SQL Server?
> At the moment, using SQL Sever, I can see a list of locks on the database
> and table in question. What happens if I delete them? Each lock is
> associated with a process - what happens if I kill the process?
> Thanks to anyone who can offer any advice on this
> kindest regards
> Simon
>|||Killing the process that holds the locks will rollback the transaction of th
at process, thereby releasing all of its locks. However, to save yourself t
his kind of trouble in the future, you should invest some time into troubles
hooting the problem. There are probably a couple of options, but all of the
m come down to why a transaction has not committed.
See if224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blockin
g Problems
(http://support.microsoft.com/?id=224453) helps you out any. 171224 INF: Understanding
How the Transact-SQL KILL Command Works ([url]http://support.microsoft.com/?id=171224[
/url]) might also be interesting for you.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message news:uXeV
MVsAEHA.3400@.tk2msftngp13.phx.gbl...
Hi everyone,
We are currently having a problem with our SQL server. What seems to have
happened is a lock has been put on a row and then hasnt been removed, so now
we can't update because the lock is still there.
I'm wondering what we can do about this. Is there anyway you can delete or
remove locks in SQL Server?
At the moment, using SQL Sever, I can see a list of locks on the database
and table in question. What happens if I delete them? Each lock is
associated with a process - what happens if I kill the process?
Thanks to anyone who can offer any advice on this
kindest regards
Simon

Deletes on subscriber in merge replication

Using Merge replication between SQL Server 2000 and SQL Server CE,
is there any way that row deletes could occur on the subscriber without a reinitialize
or explicit delete of row on publisher.

More specifically, if there is a row filter that returns a days worth of data with each
days pull, for example, and the filter looked like
select <columns> from Table where UpdateDate < GETDATE() and UpdateDate >= DATEADD(d,1,GETDATE())
would there be some implicit delete at subscriber each day because data sent changed?

My research indicates this does not happen, but I have a colleague who thinks differently.

Merge replication is trigger-based, meaning a change has to happen for the filter to be evaluated. So basically your filter will not guarantee your subscriber to have rows that meet your filter criteria without some process that does some sort of dummy update to the rows for it to be processed.

However please look at this best practice topic "Best Practices for Time-Based Row Filters": http://msdn2.microsoft.com/en-us/library/ms365153.aspx, it shows how you can accomplish what you want.

|||

Thanks for the clarification. I have been able to convince myself that deletes occur on the subscriber for records outside the filter if I apply some update. I am trying to help a customer with replication performance issues. The application does a reinitialize on every request from the user for synchronization. This is sending 10000 or more rows. When I asked them why they do a reinit every time, they were not sure (the original programmer is gone), but they said there was a problem with deletes taking so long and it was better to just reinit. That is why I asked the original question. I did not know what deletes they could be talking about.

I am curious about the deleted records. It seems that the server must be sending over deletes for every updated record outside the filter. In my case, the filter is not only time based but uses HOST_NAME(). There are about 1000 handhelds out there. So if I used the method of the best-practices article, it would seem that with each synch I would be sending over deletes for all 1000 users each time.

Maybe that was what the original programmer did and what was taking too much time (even though there would be no actual delete, because those other records were never there).

Is this the behavior that would occur?

Thanks again

Ed Santosusso

|||

In SQL 2000, yes deletes are much slower compared to inserts and updates, but this has been improved in SQL 2005 provide you use the new pre-computed partitions.

|||

Moving data in and out of a partition can send over deletes, it's the only way to remove the data from the subscriber. And in SQL 2000, yes deletes are much slower compared to inserts and updates, but this has been improved in SQL 2005 provide you use the new pre-computed partitions.

Deleteing duplicate records from my table

I just discovered that all my records appear twice inside my table, in
other words, they repeat on the row below. How can I delete all of the
duplicates? I'm sure there must be a tidy line of sql to do that.

Thanks,

Billbillzimmerman@.gospellight.com (Bill) wrote in message news:<8da5f4f4.0307310856.79830a8e@.posting.google.com>...
> I just discovered that all my records appear twice inside my table, in
> other words, they repeat on the row below. How can I delete all of the
> duplicates? I'm sure there must be a tidy line of sql to do that.
> Thanks,
> Bill

Maybe run a select distinct query and insert the results into a new
table? If need be, you could then delete all records from your
original table and insert the records back.

That should be reasonably tidy but it is hard to say what the
performance would look like without knowing the specifics.

deleteing duplicate records ?

Hi,
This is my table structure

Name Age

Siva 24
Siva 24
Raghu 25

In this above table siva 24 row is inserted twice . how to delete duplicate record .

If you use SQL Server 2005 the following query will help you..

WITH MYTABLE as (select *, Row_Number() over (order by Name,age) RowId from names)
Delete from MyTable Where Rowid Not in(Select Min(rowId) from MyTable Group By Name,Age);

If you use SQL Server 2000 you should have minimum one unique column (id) to remove the duplicates.

|||

One way (that works with both SQL 2000 and SQL 2005) is to create a new table, add the non-duplicate data to the new table, then drop the old table and rename the new table to the same name as the old table.

Here is a demonstration:

Code Snippet

SET NOCOUNT ON

CREATE TABLE MyTable
( [Name] varchar(25),
[Age] int
)
GO

INSERT INTO MyTable VALUES ( 'Siva', 24 )
INSERT INTO MyTable VALUES ( 'Siva', 24 )
INSERT INTO MyTable VALUES ( 'Raghu', 25 )

SELECT *
FROM MyTable

CREATE TABLE MyNewTable
( [Name] varchar(25),
[Age] int
)
GO

INSERT INTO MyNewTable
SELECT
[Name],
[Age]
FROM MyTable
GROUP BY
[Name],
[Age]
SELECT *
FROM MyNewTable
DROP TABLE MyTable
GO

EXECUTE sp_rename 'MyNewTable', 'MyTable'

SELECT *
FROM MyTable

|||

In this case we can simply use,

Code Snippet

Select Distinct Name, Age Into MyNewTable From MyTable;

Truncate Table MyTable;

Insert Into MyTable Select * From MyNewTable;

Drop Table MyNewTable;

Deleted row information cannot be accessed through the row

Hi,
I got following exception when accessing database using release version of
my C# application:
System.Data.DeletedRowInaccessibleException: Deleted row information cannot
be accessed through the row.
at System.Data.DataRow.GetDefaultRecord()
at System.Data.DataRow.get_Item(String columnName)
at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
Database\TwsUser.cs:line 185
at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
e) in C:\ZCAP\TWS\Tws.cs:line 2068
at System.Windows.Forms.Control.OnLayout(LayoutEventA rgs levent)
at System.Windows.Forms.ScrollableControl.OnLayout(La youtEventArgs
levent)
at System.Windows.Forms.Control.PerformLayout(LayoutE ventArgs args)
at System.Windows.Forms.Control.PerformLayout()
at ZCAP.ZAPP.Tws.InitializeComponent() in
C:\ZCAP\TWS\Tws.designer.cs:line 4025
at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
In following 2 situations, there is no exception:
(1) Running under debug version
(2) In visual studio, if I select "Release", then click "Debug" -> "Start
Debugging"
Your help is highly appreciated!
Hank
"Hang" <hyuan@.zcap.net> wrote in message
news:#LcxwJBaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I got following exception when accessing database using release version of
> my C# application:
> System.Data.DeletedRowInaccessibleException: Deleted row information
> cannot be accessed through the row.
> at System.Data.DataRow.GetDefaultRecord()
> at System.Data.DataRow.get_Item(String columnName)
> at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
> Database\TwsUser.cs:line 185
> at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
> e) in C:\ZCAP\TWS\Tws.cs:line 2068
> at System.Windows.Forms.Control.OnLayout(LayoutEventA rgs levent)
> at System.Windows.Forms.ScrollableControl.OnLayout(La youtEventArgs
> levent)
> at System.Windows.Forms.Control.PerformLayout(LayoutE ventArgs args)
> at System.Windows.Forms.Control.PerformLayout()
> at ZCAP.ZAPP.Tws.InitializeComponent() in
> C:\ZCAP\TWS\Tws.designer.cs:line 4025
> at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
> at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
> In following 2 situations, there is no exception:
> (1) Running under debug version
> (2) In visual studio, if I select "Release", then click "Debug" -> "Start
> Debugging"
>
This is an ADO.NET question, not a SQL Server question. Try posting in the
ADO.NET groups.
David

Deleted row information cannot be accessed through the row

Hi,
I got following exception when accessing database using release version of
my C# application:
System.Data.DeletedRowInaccessibleException: Deleted row information cannot
be accessed through the row.
at System.Data.DataRow.GetDefaultRecord()
at System.Data.DataRow.get_Item(String columnName)
at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
Database\TwsUser.cs:line 185
at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
e) in C:\ZCAP\TWS\Tws.cs:line 2068
at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
levent)
at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
at System.Windows.Forms.Control.PerformLayout()
at ZCAP.ZAPP.Tws.InitializeComponent() in
C:\ZCAP\TWS\Tws.designer.cs:line 4025
at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
In following 2 situations, there is no exception:
(1) Running under debug version
(2) In visual studio, if I select "Release", then click "Debug" -> "Start
Debugging"
Your help is highly appreciated!
Hank"Hang" <hyuan@.zcap.net> wrote in message
news:#LcxwJBaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I got following exception when accessing database using release version of
> my C# application:
> System.Data.DeletedRowInaccessibleException: Deleted row information
> cannot be accessed through the row.
> at System.Data.DataRow.GetDefaultRecord()
> at System.Data.DataRow.get_Item(String columnName)
> at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
> Database\TwsUser.cs:line 185
> at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
> e) in C:\ZCAP\TWS\Tws.cs:line 2068
> at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
> at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
> levent)
> at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
> at System.Windows.Forms.Control.PerformLayout()
> at ZCAP.ZAPP.Tws.InitializeComponent() in
> C:\ZCAP\TWS\Tws.designer.cs:line 4025
> at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
> at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
> In following 2 situations, there is no exception:
> (1) Running under debug version
> (2) In visual studio, if I select "Release", then click "Debug" -> "Start
> Debugging"
>
This is an ADO.NET question, not a SQL Server question. Try posting in the
ADO.NET groups.
David

Deleted row information cannot be accessed through the row

Hi,
I got following exception when accessing database using release version of
my C# application:
System.Data.DeletedRowInaccessibleException: Deleted row information cannot
be accessed through the row.
at System.Data.DataRow.GetDefaultRecord()
at System.Data.DataRow.get_Item(String columnName)
at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
Database\TwsUser.cs:line 185
at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
e) in C:\ZCAP\TWS\Tws.cs:line 2068
at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
levent)
at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
at System.Windows.Forms.Control.PerformLayout()
at ZCAP.ZAPP.Tws.InitializeComponent() in
C:\ZCAP\TWS\Tws.designer.cs:line 4025
at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
In following 2 situations, there is no exception:
(1) Running under debug version
(2) In visual studio, if I select "Release", then click "Debug" -> "Start
Debugging"
Your help is highly appreciated!
Hank"Hang" <hyuan@.zcap.net> wrote in message
news:#LcxwJBaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I got following exception when accessing database using release version of
> my C# application:
> System.Data.DeletedRowInaccessibleException: Deleted row information
> cannot be accessed through the row.
> at System.Data.DataRow.GetDefaultRecord()
> at System.Data.DataRow.get_Item(String columnName)
> at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
> Database\TwsUser.cs:line 185
> at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
> e) in C:\ZCAP\TWS\Tws.cs:line 2068
> at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
> at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
> levent)
> at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
> at System.Windows.Forms.Control.PerformLayout()
> at ZCAP.ZAPP.Tws.InitializeComponent() in
> C:\ZCAP\TWS\Tws.designer.cs:line 4025
> at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
> at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
> In following 2 situations, there is no exception:
> (1) Running under debug version
> (2) In visual studio, if I select "Release", then click "Debug" -> "Start
> Debugging"
>
This is an ADO.NET question, not a SQL Server question. Try posting in the
ADO.NET groups.
David