could use some help doing a simple update and/or delete

Question

Post reply

could use some help doing a simple update and/or delete

jkurzner

SSC Journeyman

Points: 92
More actions
August 23, 2014 at 3:03 pm

#310365

I have a SQL Express hosted database with a hosting company that has a table that seems to have a lot of data in it. The table has an identity key with a field called ID and there are less than 500000 records. The table has worked fine for a long time, but all of the sudden appears to not let me do updates or deletes. Even a simple delete from tablex where id
DELETE from TableA where TableA.ID = 342000;
will not execute. I can run select queries but they do seem to take a long time unless I am selecting from the database in key order using a record limiter such as SELECT TOP 1000 * FROM TABLEA
I checked the properties of the database in SSMS and there appears to be 300MB of available space and 690MB in use.
I do not have SysAdmin access since it is on a hosted platform and the SQL techs are away for the weekend. Anything I can do to try and fix the problem? I need to delete a bunch of records and nothing seems to work.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What do you mean by Even a simple delete from tablex where id ... will not execute.?

Is there any error message?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jkurzner SSC Journeyman Points: 92 More actions · Answer 2

Nope, no error message. The query appears to execute but to delete a single record by ID doesn't complete. I let the query run for an hour and it never deleted the record, never generated an error either.

jkurzner SSC Journeyman Points: 92 More actions · Answer 3

Solved my own problem

ALTER DATABASE DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

USE DATABASE ;

GO

DELETE

FROM TABLEA

where ID=342000

GO

ALTER DATABASE DATABASE SET MULTI_USER;

GO

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Out of plain curiosity, what is this database used for?

😎

jkurzner SSC Journeyman Points: 92 More actions · Answer 5

It's used for a sports league website. The table that keeps track of email communication was the one that had an issue.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

jkurzner (8/23/2014)
It's used for a sports league website. The table that keeps track of email communication was the one that had an issue.

So the heavy handed single user mode approach isn't causing any issues then?

😎

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 7

It does look like blocking issue. When you set the database to a single user mode, the process that was blocking you, was killed and the resources that you needed were released. Next time that it happens, try to see who is blocking you and if there is a blocking chain.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jkurzner SSC Journeyman Points: 92 More actions · Answer 8

Thanks. I think there is a server process that accessed the file to do some cleaning and then send out the emails. I killed the process, but maybe there was a deadly embrace of some kind. In any event, I'm glad I know a way to fix it.

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 9

jkurzner (8/24/2014)
Thanks. I think there is a server process that accessed the file to do some cleaning and then send out the emails. I killed the process, but maybe there was a deadly embrace of some kind. In any event, I'm glad I know a way to fix it.

So you are not worried about missing data? You will lose all sessions and all session data while taking db to single user mode. All pending inserts, updates and deletes will be lost.

Are you sure this the right way to handle situation like this?

jkurzner SSC Journeyman Points: 92 More actions · Answer 10

In this case, I am not concerned about missing data. I was more concerned about having the website down due to a problem with not being able to remove the offending records. The site is now fully operational and the queued records that were stuck have now been properly processed. Without sysadmin access and no ability to wait until Monday it seemed the best course of action at the time. If you have a better suggestion on a method to try and remove the records during this type of situation in the future, please let me know. I'd be grateful.