May 2, 2013 at 3:41 am
Hi,
Pls i'm having the error 'ERROR 823: I/0 error (torn page) detected during read at the offset 0x00000000182e000 in file D:\program files\microsoft\SQL server\MSSQL\data\msdbdata.mdf' when i when i want to view jobs. Before now, i notice my jobs weren't running thing i notice my msdb database was flagged as suspect. I usually run the below script when i get any of my databases is flagged as suspect.
#### For database flagged as suspect and reduce log size#####
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
BEGIN TRAN
UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = '<Your Database Name Goes Here>'
IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
--------------------------------------------------------------------
DBCC REBUILD_LOG('<Your Database Name Goes Here>','C:\filePath\filename.LDF')
ALTER DATABASE <Your Database Name Goes Here> SET MULTI_USER
GO
After running this my msdb database was no longer flagged as suspect but on trying to start my jobs again, i want able to view them with the 'torn page' error displaying.
Pls note that i don't have a backup of my msdb database. This is what i have tried so far as suggested by other people.
I have ran sqlservr -c -T 3608, moved/renamed the msdbdata.mdf and msdblog.ldf files, run instmsdb to recreate the msdb database. and restarted the server without trace 3608.
GilaMonster: this is the result of 'DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS'
Server: Msg 7995, Level 16, State 1, Line 1
Database 'Msdb' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKDB processing.
May 2, 2013 at 4:26 am
http://msdn.microsoft.com/en-us/library/aa197950%28v=sql.80%29.aspx
Script out your jobs first, all logins, backup history, DTS packages, linked servers, server-level permissions. Since you have no backups, there's no other solution here, that corruption's not repairable.
Afterwards check your IO subsystem and sort out a backup strategy
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2021 at 7:23 pm
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply