PROBLEM: Tables are locking up when attempting to save data

[elitecobra]

Over the weekend, one of our out-of-house programmers ran an update to our three main tables. I know these are kind of broadstrokes, but basically he compared the data and updated certain fields when it met certain conditions (lots of rules basically). The three tables are one-to-one and contain a little over a million records. The comparison file contained around 400k records.

The scripts made it through 250k records from the comparison file before he had to stop it for the weekend.

When I came in to test the data yesterday - I was met with problems on my front end application - it would lock up on the write back to the database. I went into EP and experienced the same thing after making any changes to a record, it would just lock up. This only appears to be a problem on the 2 bigger tables of the 3. I currently have 12 gigs or so free on that box and I have already shrunk the log and data files.

I tried removing and re-adding the indexes, but I am freezing up everytime I try to either change or delete the Clustered Index on the Primary Key. I don't know why, but I thought maybe that was my issue.

I know this is pretty broad, but even if someone could give me ideas as to why SQL would lock up like that when trying to just save the data, it would be most helpful.

NOTE: There were NO structure changes in the update process and my restored data from Friday works perfect.

If you need more info, just ask. Thanks in advance for the help.

Don
elitecobra2000@yahoo.com

[MAK]

Did you run "update statistics" after dropping and creating indexes?

And does all your queries have Table hints?

[MAK]

Also check the size of the tempdb and space available on the drive where tempdb is located.

[elitecobra]

Well I have had this on my production and development servers and received the same results.

My development box has 80 gigs free and is getting the same problem.

I am a front end developer and database designer by experience. I didn't run the queries and I have no idea about Table hints. The guy who runs the queries is not in house so I don't have an answer off hand.

Where do I look to check the size of the tempdb?

Thanks again,

Don

[MAK]

open query analyser and execute the following


use tempdb
go
--This will show you the space used
sp_spaceused
go
--This will show the location of the tempdb
select * from sysfiles
go




Try this on your development. (login as SA)

Use yourdatabasename
go
sp_updatestats
go

Then connect from the front end.

[elitecobra]

Development Server Info:

Tempdb - dbsize 8.75mb - 7.38mb
Reserved 640kb, Data 200kb, Index Size 352kb, Unused 88kb

[MAK]

Did you run the below statement and test it.

Use yourdatabasename
go
sp_updatestats
go


Also check if there is any change in the network settings like Full duplex, auto config or half duplex on the network cards. (Ask your network admin)

[elitecobra]

I ran the update statistics - kept running even after I left last night.

Is there anything else I can do from here? Also I mentioned a clustered index not responding - is there any way I can delete that fix it? I am worried that is causing my problems.

[skhanal]

Did the update complete?

You may be blocked by the open transaction from the update.

do

dbcc opentran(yourdbname)

to see if you have any transaction open. If the transaction did not complete then you have to decide whether you will let it run forever or kill the session and it will start rolling back. This could also take a while to complete.

When you modify the index, open query analyzer independent of enterprise manager (that is don't start from EM) and run sp_who, see if you are blocked and by whom.

[elitecobra]

Ok, I will try that next, but yes the update did complete.

[MAK]

Try using non clustered index. easy to maintain , drop and recreate.

[elitecobra]

Well that is part of my problem that I described above. I can't do anything with the Clustered Index on those 2 tables.

I actually recreated my tables with all of the data and added my Primary Key, but that just recreated the Clustered Index (DOH!).

Not sure what to do here, but we have narrowed it down to it either being a size or index issue when comparing it to the old data.

I am absolutely lost at this point though.

[MAK]

This will create nonclustered index of that primary key
create table mytest (id int, name char (10) constraint mytestpk primary key nonclustered)

[MAK]

another way.

CREATE TABLE [dbo].[mytest] (
[id] [int] NULL ,
[name] [char] (10))
ON [PRIMARY]
GO

ALTER TABLE [dbo].[mytest] WITH NOCHECK ADD
CONSTRAINT [mytestpk] PRIMARY KEY NONCLUSTERED
(
[name]
) ON [PRIMARY]
GO

[elitecobra]

Just wanted to follow up with everyone that helped attempt to solve my problem.

It became obvious that the problem was in the process we were running to update the data. And we had actually run this successfully before. The process takes 3 days and as I had mentioned he had to stop the process because there wasn't enough time over the weekend to finish it. In doing this SQL created LOCKS on my tables and ids. I had no idea to even look there, but now I do.

Just thought I would share it with you guys in case something like this comes up again.

Don