table corrupted - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

table corrupted

Author

Topic

kh_wpl
Starting Member

7 Posts

Posted - 2012-01-09 : 02:57:31

Hello
I have sql database with 65 table.
one of the table name is tbl_part with 12000 records data.

all of table works correctly.
but when work with tbl_part (select, update ...) sql server raise time out error .

for repair this problem i have restart sql server service from control panel then tbl_part works well.
but after many hour tbl_part again corrupted and sql server raise time out error for them (however other tables works without any problem)

is there any way to make it correct.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2012-01-09 : 04:08:38

It doesn't sound like you have a corrupt table with a time out error, could you post the error message what your receiving from SQL Server

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-09 : 04:50:56

That's not corruption. That's a poor performing query.

--
Gail Shaw
SQL Server MVP

kh_wpl
Starting Member

7 Posts

Posted - 2012-01-11 : 03:26:49

Select * from Part

it does take 20 minutes but doesn't complete

Kristen
Test

22859 Posts

Posted - 2012-01-11 : 04:08:48

Its being BLOCKed, or possible there is a corruption in the database.

If you Stop/Start SQL Service and the query works then it was being blocked. You could also use sp_Who or SSMS to check for blocking if you are familiar with that?

To check for corruption:


USE MyDatbase
GO
DBCC CHECKDB WITH NO_INFOMSGS	-- , ALL_ERRORMSGS

or use


DBCC CHECKDB WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY	-- , ALL_ERRORMSGS

if you have SQL 2008

You ought to check for corruption periodically (say, once a week) in any case, just so if it happens you know relatively soon after it happens, and not months-later when it will be much harder to correct / re-key corrupted / missing data.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-11 : 06:04:14

Kristen, this is almost certainly not database corruption (corruption causes high severity errors, not timeouts)

99% chance this is blocking, maybe forwarding records, possibly fragmentation (but neither of those would go away after a restart). Corruption is just about the last thing I would think of when dealing with just timeouts and no other errors.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2012-01-11 : 06:08:12

quote:
Originally posted by GilaMonster

Kristen, this is almost certainly not database corruption (corruption causes high severity errors, not timeouts)

Yeah, I agree, but its a bit more than a timeout, the task is never completing. Sounds very similar to a failure I had a while back with a memory fault that caused SQL to just "stall" rather than report an error

But easy enough to check for blocking of course - if the O/P is not familiar with sp_Who / SSMS then just restarting SQL Service will do the trick

and then the query can be retried immediately after the restart

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 10:09:04

please run sp_who2 (in a new window) after you run your query

SELECT * FROM tbl_Part

Also...can you run

SELECT TOP 100 * FROM tbl_part, and does that hang?

Tell us if you see a blkby with a number in any of the rows

First...are you using SQL Server management Studio? Or something else

Are you sure you are not blocking yourself?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources