Broken Table 1

[Zukkster]

I have a table that has just stopped working. When I try to run a query on it, it just runs and runs.

I can't drop the table, I can't delete the data. When I go to the task pad view it says the table is zero rows but is 33,408 KB

What could be wrong?

 

[SQLSister]

LOok at DBCC CHECKTABLE in Books online.

 

[JayKusch]

Have you tried to delete the table already. It may be that your connection is still showing a ghost. Go ahead and and right click on the server name and "disconnect". Then connect back in and see if the table still exists.

Thanks

J. Kusch

 

[Zukkster]

Thanks SQLSister DBCC was the answer. It was a little more complicated than I expected ... so for the benefit of anyone experiencing the same problem.

I had to go through a few stages before I could run the DBCC

First I had to kick of any users so that I could put the database into single user mode and then run the DBCC code. I found this code to kick off user conections

CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON
DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go

Then when this had run I could run the code to put the database into single user mode, fix the table and put it back, using the following code

EXEC sp_dboption 'dbname' , 'single user', true
DBCC CHECKTABLE ('tablename', REPAIR_FAST)
EXEC sp_dboption 'dbname', 'single user', false

I used repair fast because I didn't care if I lost data it was only a staging table.