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

• christy.umoren

  SSCommitted

  Points: 1654

  More actions

  May 2, 2013 at 3:41 am

  #292916

  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.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 2, 2013 at 4:26 am

  #1611836

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• August 22, 2021 at 7:23 pm

  This was removed by the editor as SPAM