Post reply

MSDB (Suspect) HELP!!!

  • September 7, 2003 at 7:12 pm

    #65481

    Ok for the last week I have had one database go down. All I got was Database Name (Suspect) I have since then deleted the database and restored it . The first one that gave me the error was my distribution db. After trying to delete all publications two days later I got MSDB (Suspect) It indicates that I should check my sql server error log, Problem with that is that that data lives in MSDB. How can I fix this, none of my jobs can run and all DTS packages are gone. I do have a backup of the MSDB however it will not let me restore it!!! What do I do!!!!????

    Edited by - kbrady on 09/07/2003 7:14:39 PM

  • September 7, 2003 at 11:27 pm

    #472960

    Q: MSDB Database won't restore.

    A: Make sure the SQLSERVERAGENT service is not started.

    Problems may arise trying to restore the msdb database if the SQLSERVERAGENT service is started. The MSDB database seems to be the only database affected by this. All of the other databases (except for the master which needs to be started in single user mode) will restore with the SQLSERVERAGENT service started.

    Above is from http://www.ultrabac.com/kb6/htm/UBQ000078.htm

  • September 7, 2003 at 11:31 pm

    #472961

    Have a look at BOL 2000: Resetting the Suspect Status

  • September 8, 2003 at 7:29 am

    #473019

    First, you need to check SQL Server errorlog to see any error messages indicate why MSDB was suspended.

    In order to restore MSDB database, start SQL Server with -T3608, drop the MSDB and restore it from the backup.

    Try to find out the reasons before starting the restoration.

  • September 9, 2003 at 12:26 am

    #473150

    Hi,

    is 'automatically grow file' with the datafiles and the logfiles enabled (check at database-properties)?

    Let me know

    Sven

    forum@svenschimmel.net

  • September 9, 2003 at 5:09 am

    #473169

    The problem was that there was never an issue with not having enough disk space. I did set it to auto growth 10% (default) I have looked at books online and "Resetting the Suspect Status" really only talks about not having enough disk space. The drive that all the data resides on has 32gb left STILL IT LOOKS LIKE I WILL HAVE TO REINSTALL SQL 2000

  • September 9, 2003 at 5:15 am

    #473171

    quote:

    First, you need to check SQL Server errorlog to see any error messages indicate why MSDB was suspended.

    In order to restore MSDB database, start SQL Server with -T3608, drop the MSDB and restore it from the backup.

    Try to find out the reasons before starting the restoration.

    Not sure what you mean in regards to "start SQL Server with -T3608" Also I was unable to look at the log file, at first then I ran:

    -- Run these stored procedures when a database has been marked suspect

    sp_add_data_file_recover_suspect_db "msdb"

    SP_CONFIGURE 'allow updates', 1

    RECONFIGURE WITH OVERRIDE

    GO

    UPDATE master..sysdatabases set status = -32768 WHERE name = 'msdb'

    GO

    SP_CONFIGURE 'allow updates', 0

    RECONFIGURE WITH OVERRIDE

    DBCC DBRECOVER (msdb)

    EXEC sp_resetstatus @dbname = "msdb"

    After that I was able to view content in emergency mode. I still was limited as to what I could see and do. I could not just drop the DB, I could not use the export function. Useless!*^@%^!^%(!)&

  • September 9, 2003 at 7:16 am

    #473201

    quote:

    Not sure what you mean in regards to "start SQL Server with -T3608"

    To start the default instance of SQL Server from a command prompt

    From a command prompt, enter:

    sqlservr.exe -c -m -T3608

    quote:

    Also I was unable to look at the log file

    Go to SQL Server folder \MSSQL\Log to open the log files with NotePad.exe

  • September 9, 2003 at 6:52 pm

    #473342

    why in gods green earth would I want to start SQL in the command prompt???

  • September 9, 2003 at 8:58 pm

    #473350

    See BOL 2000: Using Startup Options

    

    /Ttrace# Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior. 

    

    -m Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. (Typically, this option is used if you experience problems with system databases that should be repaired.) Enables the sp_configure allow updates option. By default, allow updates is disabled.  

    

    -c Shortens startup time by starting an instance of SQL Server independently of the Service Control Manager, so that SQL Server does not run as a Microsoft Windows NT® 4.0 or Windows 2000 service.

  • September 10, 2003 at 12:36 am

    #473356

    quote:

    why in gods green earth would I want to start SQL in the command prompt???

    I guess there are several good reasons for this.

    - Maintenance

    - Disaster recovery

    ....

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • September 10, 2003 at 6:31 am

    #473398

    I guess you have an account that starts the sql server service.

    Does that account have "read" permissions on the folder that contains either the .mdf or .ldf files ?

  • October 13, 2004 at 12:13 am

    #526456

    I have the same problem.

    I opened the errorlog file and i found only this error:

    2004-08-19 13:13:40.28 spid6    Error: 823, Severity: 24, State: 10

    2004-08-19 13:13:40.28 spid6    I/O error 2(The system cannot find the file specified.) detected during read of page buffer..

    2004-08-19 13:13:40.28 kernel   ReadFileHdr: Operating system error 2(The system cannot find the file specified.) encountered.

    2004-08-19 13:13:40.29 spid6    Operating system error 2(The system cannot find the file specified.) on device 'd:\MSSQL7\DATA\msdblog.ldf' during ReadFileHdr.

    How could i solve the problem?

     

    Thanks

    Costica

    cos_zam@yahoo.com

  • October 14, 2004 at 12:43 am

    #526651

    Yesterday, i had the same problem. "msdb suspect"

    I opened c:\mssql7\log\errorlog and i saw that msdbdata.ldf wasn't able to opened.

    So, i talked and i found out that this database is used by SQL Agent and i realized that only maintenance plan was affected.

    when i was sure about this, i tried this :

    1. sp_detach_db msdb

    2. i recreate msdb from queryanalyzer with:

    c:\mssql7\install\instmsdb.sql

     

    this error (msdb suspect) was solved.

    When i tried to make backup for my db xxxxx, i received another error :

    2004-10-14 07:58:42.64 kernel BackupMedium::ReportIoError: write failure on backup device 'D:\MSSQL7\BACKUP\XXXXX\XXXXX'. Operating system error 2(The system cannot find the file specified.).

    2004-10-14 08:09:24.68 kernel BackupMedium::ReportIoError: write failure on backup device 'D:\MSSQL7\BACKUP\XXXXX\XXXXX_20041014_2'. Operating system error 2(The system cannot find the file specified.).

    2004-10-14 08:12:18.93 kernel BackupMedium::ReportIoError: write failure on backup device 'D:\MSSQL7\BACKUP\XXXXX\XXXXX_20041014_2'. Operating system error 2(The system cannot find the file specified.).

    Now, i'm sure that i must repair my HDD.

     

    Good luck!!!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply