SqlServer資料庫置疑的解決辦法

SqlServer資料庫置疑的解決辦法

關鍵詞： SqlServer    置疑                                          

 

工作中已經碰到兩次這種情況了，想想還是應該把他記錄下來，利人利己。

通常這個問題是由於硬碟空間不夠或硬碟讀寫錯誤造成的。

問題現象：

資料庫後面有“置疑”字樣，查看系統事務日記出現以下錯誤：
錯誤1---------------------------------------------
錯誤: 823，嚴重度: 24，狀態: 2
I/O error 23(資料錯誤 (迴圈冗餘檢查)。) detected during read at offset 0x00000000200000 in file 'C:/Program Files/Microsoft SQL Server/MSSQL/Data/Jiapei_Data.MDF'.
錯誤2---------------------------------------------
錯誤: 3313，嚴重度: 21，狀態: 2
恢複資料庫 'Jiapei' 的日誌中記錄的操作時出錯。出錯位置在日誌記錄 ID (274:377:2)。
錯誤3---------------------------------------------
錯誤: 3313，嚴重度: 21，狀態: 2
Error while redoing logged operation in database 'Jiapei'. Error at log record ID (274:377:2).
資料庫可以分離，但分離後無法附加，附加時出現“823”號錯誤。

解決方案：

1、建立一同名資料庫（檔案名稱，檔案組都和原來的一樣），然後停止資料庫服務，用原來檔案替換建立的資料庫檔案，啟動資料庫，該資料庫被設為suspect

2、把資料庫改成緊急模式：
sp_configure 'allow', 1
reconfigure with override
update sysdatabases set status = 32768 where name = '資料庫名'
3、把LDF檔案改名，再執行
DBCC REBUILD_LOG ('資料庫名', 'E:/fdzz/database/fdzz1204_Log.LDF' )
4、恢複資料庫緊急模式
update sysdatabases set status = 0 where name = '資料庫名'
執行
restore database 資料庫名 WITH RECOVERY
sp_configure 'allow', 0
reconfigure with override
5、然後用DBCC CHECKDB ('資料庫名')看看有沒有錯誤
6、如果上面還是不行，試試吧資料庫設為緊急模式，應該可以看到資料了，在把資料匯出到一個新的資料庫。

 

其他有用的操作：

/*--重設置疑狀態
1.系統方法:
如果 sql server 因為磁碟機不再有可用空間，而不能完成資料庫的恢複，
那麼 microsoft sql server 2000 會返回錯誤 1105
並且將 sysdatabases 中的 status 列設為置疑。按下面的步驟解決這個問題：
執行 sp_resetstatus。
文法為：
sp_resetstatus '資料庫名'
用 alter database 向資料庫添加一個資料檔案或記錄檔。
停止並重新啟動 sql server。
用新的資料檔案或記錄檔所提供的額外空間，sql server 應該能完成資料庫的恢複。
釋放磁碟空間並且重新運行恢複操作。
sp_resetstatus 關閉資料庫的置疑標誌，但是原封不動地保持資料庫的其它選項。
--*/
--2.手工重設置疑狀態
use master
go
sp_configure 'allow updates',1 reconfigure with override
go
declare @dbname varchar(30)
set @dbname='你要處理的資料庫名'
if @@trancount > 0
print '進行中交易處理,操作不能進行'
else if suser_id()!=1
print '你不是系統管理員(sa),不能進行此操作'
else if not exists(select 1 from master..sysdatabases where name=@dbname)
print '你要操作的資料庫不存在'
else if not exists(select 1 from master..sysdatabases where name= @dbname and status & 256 = 256)
print '你的資料庫沒有被置疑'
else
begin
begin tran
update master..sysdatabases set status = status ^ 256 where name = @dbname
if @@error != 0 or @@rowcount != 1
rollback tran
else
begin
commit tran
print '操作成功,請重新啟動SQL'
end
end
go
sp_configure 'allow updates', 1 reconfigure with override
go

--------------------------------------------------------------------------------

可是現在我已經將這個資料庫分離出去了，又不能附加進來，所以那個操作sp_resetstatus 就玩不起來了

--------------------------------------------------------------------------------

右鍵置疑狀態的資料庫-->所有任務-->離線
右鍵離線狀態的資料庫-->所有任務-->聯機
重設置疑狀態
如果 SQL Server 因為磁碟機不再有可用空間，而不能完成資料庫的恢複，那麼
Microsoft? SQL Server? 2000 會返回錯誤 1105 並且將 sysdatabases 中的 status
列設為置疑。按下面的步驟解決這個問題：
1.. 執行 sp_resetstatus。
2.. 用 ALTER DATABASE 向資料庫添加一個資料檔案或記錄檔。
3.. 停止並重新啟動 SQL Server。
用新的資料檔案或記錄檔所提供的額外空間，SQL Server 應該能完成資料庫的恢
複。
4.. 釋放磁碟空間並且重新運行恢複操作。
sp_resetstatus 關閉資料庫的置疑標誌，但是原封不動地保持資料庫的其它選項。
注意 只有在您的主要支援提供者指導下或有疑難解答建議的做法時，才可以使用
sp_resetstatus。否則，可能會損壞資料庫。
由於該過程修改了系統資料表，系統管理員必須在建立這個過程前，啟用系統資料表更新。要啟
用更新，使用下面的過程：
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
過程建立後，立即禁用系統資料表更新：
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
只有系統管理員才能執行 sp_resetstatus。執行該過程後，立即關閉 SQL Server。
文法為：
sp_resetstatus database_name
下面的例子將關閉 PRODUCTION 資料庫的置疑標誌。
sp_resetstatus PRODUCTION
下面是結果集：
Database 'PRODUCTION' status reset!
WARNING: You must reboot SQL Server prior to accessing this database!
sp_resetstatus 預存程序代碼
下面是 sp_resetstatus 預存程序的代碼：
IF EXISTS ( SELECT * from sysobjects where name = 'sp_resetstatus' )
DROP PROCEDURE sp_resetstatus
GO
CREATE PROC sp_resetstatus @dbname varchar(30) AS
DECLARE @msg varchar(80)
IF @@trancount > 0
BEGIN
PRINT 'Can''t run sp_resetstatus from within a transaction.'
RETURN (1)
END
IF suser_id() != 1
BEGIN
SELECT @msg = 'You must be the System Administrator (SA)'
SELECT @msg = @msg + ' to execute this procedure.'
RETURN (1)
END
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @dbname) != 1
BEGIN
SELECT @msg = 'Database ' + @dbname + ' does not exist!'
PRINT @msg
RETURN (1)
END
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @dbname AND status & 256 = 256) != 1
BEGIN
PRINT 'sp_resetstatus can only be run on suspect databases.'
RETURN (1)
END
BEGIN TRAN
UPDATE master..sysdatabases SET status = status ^ 256
WHERE name = @dbname
IF @@error != 0 OR @@rowcount != 1
ROLLBACK TRAN
ELSE
BEGIN
COMMIT TRAN
SELECT @msg = 'Database ' + @dbname + ' status reset!'
PRINT @msg
PRINT ''
PRINT 'WARNING: You must reboot SQL Server prior to '
PRINT ' accessing this database!'
PRINT ''
END
GO