[dba-SQLServer]Repairing a corrupt table

 if you do it really quick you may get by with it:
 
select 'kill', spid from master.dbo.sysprocesses (nolock) where
db_name(dbid) = 'your_database'
print "exec sp_dboption 'your_database', 'single', true"
print "dbcc checkdb('your_database', REPAIR_ALLOW_DATA_LOSS )
 
copy the output into the qa window (make sure you're not in the
database, use master to do it) and run it.
 
if this doesn't fly, - yup, remove the publication.  But I would also
try to offload the table in question to a temporary permanent table,
truncate the original table, and see if the results of dbcc checkdb
changed.

 
 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark
Boyd
Sent: Friday, October 10, 2003 8:47 AM
To: SQLServerList
Subject: [dba-SQLServer]Repairing a corrupt table



I am in the process of repairing a corrupt table.

My backup software is failing with the following message: 'Table
Corrupt: Object ID 734690461, Index ID 2. Keys out of order on page
(1:78526), slots 145 and 146.'

It seems table MSmerge_contents has become corrupt.  This table is used
in the replication with another SQL Server.

I ran DBCC_CHECKDB to make sure this was the only table with corruption
issues.  Now I want to run the repair option, but am prompted with a
message stating the database needs to be in single-user mode in order to
repair.  I tried putting the db into single-user mode, but am prompted
with an error message telling me the option can't be changed while
another user is in the database.  I'm pretty sure no one else is in it,
but am thinking the subscription to the other server needs to be removed
to do this.

Has anyone dealt with this sort of issue before?

How can I tell if anyone is connected to the database?

 

TIA,

Mark Boyd

Sr. Systems Analyst

McBee Associates, Inc.

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20031010/e6bc2431/attachment.html>