DBCC checkdb 的时候提示：无法使用闩锁类型 SH 读取并闩锁页

Question

环境：WIN2003+SQL 2008

数据库可以正常使用。发现有些作业执行不了，才DBCC。

DBCC CHECKDB 的时候，提示： 无法使用闩锁类型 SH 读取并闩锁页 (1:6)。23(数据错误(循环冗余检查)。)

dbcc checkdb(‘库名’,REPAIR_ALLOW_DATA_LOSS) 
dbcc checkdb(‘库名’,REPAIR_REBUILD) 

都提示上面的错误。

希望指点

谢谢

Answer 1

That means there were connections to the db, you have to kill those connections first then run dbcc.

Answer 2

你执行下面语句的时候需要单用户模式才能执行，如果是单用户模式那么不会存在数据库连接

dbcc checkdb(‘库名’,REPAIR_ALLOW_DATA_LOSS) 
dbcc checkdb(‘库名’,REPAIR_REBUILD) 

如果还有连接那么运行下面这句话的时候说设置单用户模式失败

除非没有数据库连接，才能成功

---

给我写信： QQ我：

Answer 3

---

给我写信： QQ我：

不断执行查询，因为数据库还存在连接

Answer 4

That means there were connections to the db, you have to kill those connections first then run dbcc.

没有数据库连接啊。我这个是晚上执行报错的。况且，我是DW库，即使有，也只能读，难道读也不行？

谢谢

Answer 5

It needs exclusive access to repair data.

Answer 6

执行

select spid from sys.sysprocesses with (nolock) where dbid=db_id('DBName')

然后将查询出来的SPid Kill掉.

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 7

执行

select spid from sys.sysprocesses with (nolock) where dbid=db_id('DBName')

然后将查询出来的SPid Kill掉.

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

晚上的作业，怎么KILL？KILL下，不是整个作业都失败了，等于没跑数据。还是说，让作业自动判断失败的话，就KILL 下，再执行作业？

我昨天把所有数据都清了，跑起来是正常的。

Answer 8

If you want to repair db, have to stop all user processes.

Answer 9

可以尝试一下DBCC CHECKDB  with TABLOCK 参数。

Answer 10

If you want to repair db, have to stop all user processes.

其他用户是可以停用的，关键是怎么修复？

用 

dbcc checkdb(‘库名’,REPAIR_ALLOW_DATA_LOSS) 
dbcc checkdb(‘库名’,REPAIR_REBUILD) 

？

以前用上面语句修复过，修复完还是不行。

Answer 11

DBCC can't repair all kind of corruptions, that's why need good backups.

Answer 12

What kind of corruption did you get?

Answer 13

What kind of corruption did you get?

DBCC 执行完，就提示这个。

无法使用闩锁类型 SH 读取并闩锁页 (1:6)。23(数据错误(循环冗余检查)。)

Answer 14

My question is why you run dbcc with repair option? What was result of 'dbcc checkdb (db_name)'?