BIG PROBLEM!

  • I have a database that was SQL 7 and suspect. We tried to reset the status and that didn't work properly. Then we tried a few times to get it dettached. We finally got it dettached but can't get it reattached. I keep getting: 602: "could not find row in sysindexes"

    And it references a row and a db id and another index number. We tried it on other DB servers with the same failure. We tried it without the LDF and it still fails. I am in a real pinch here. We can't run DBCC CHeckTable obviously because the database is not attached unless there is an emergency way to get it attached.

    Any help would be MOST appreciated.

    Thanks,

    Neil

  • I'm not seeing anything come up in the MS KB and it's not a situation I've come across before. I thought there was a Russian company that came out with a program that did this, but I can't remember who it was, and since there weren't any previous users, I don't know if I'd trust 'em in any case... data corruption, etc. could mean the forced reattach is meaningless.

    Would a support call to Microsoft be out of the question?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • I am having real problems with this server. I get this error in my logs when I try to start the agent.

    SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).

    Neli

  • What account is associated with the SQL Agent service? Is that account a member of SYSADMIN?

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • I did our admin user and local system and neither worked!!! There has to be some corruption somewhere in there... 🙁

    Neil

  • have you tried attaching to another sql server instance?

  • Is the SQL Server Agent running under an NT account. If so, is this computer still in the domain?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Its not on a domain. The user didn't matter. After a few reboots it started working. Something is really wrong with the install. It has about 105 SQL DBs on there. Do you have any information on a full migration of these DBs?

    Thanks,

    Neil

    p.s. Can I test for corruption within the system tables?

  • Look at DBCC CHECKDB and DBCC CHECKTABLE in BOL.

  • Anything else that would detect problems and fix corruption?

    I have a couple more DB's in suspect states 🙁

    Neil

  • If the DBCC doesn't do it, I think your chances are getting slim.

  • When the database does not want too get out of recovery mode, try too use the following procedure too retsore only the MDF with this procedure :

    shutdown sql

    move the current database file or rename it

    restart sql server

    create a new database of the same name and log file and location as the old database and log file

    get rid of the old database.

    you may be able to right click delete it in this situation or used sp_removedb

    create a new database of the right size and shape with correct log and data file locations

    stop sql

    rename the new databases.mdf or delete it if you don't have enough space - do not touch the .ldf

    move back in the old database .mdf file or rename it back again

    restart sql server

    it should come up suspect

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

    1. From a query window, set the status so that you can update the system

    tables by running the following query:

    use Master

    go

    sp_configure "allow", 1

    go

    reconfigure with override

    go

    2. Then set the status of the DB that is giving you the problem (XXXXX) into

    Emergency Mode by running the following query:

    update sysdatabases set status = 32768 where name = '<DBName>'

    go

    checkpoint

    go

    shutdown with nowait

    go

    3. Go into the data directory (MSSQL7\DATA) and rename the log file associated

    the DB in question (XXXX.ldf) to some

    temporary name, such as XXXX.TMP.

    4. Exit the query window.

    5. Then start up SQL Server from a DOS command window by issuing:

    sqlservr -c -T3608 -T4022.

    6. Bring up another query window and verify that the DB is in emergency mode

    by issuing:

    select Name, Status from Sysdatabases where name = '<DB_Name>'

    7. Verify that the status is 32768. If it is, then issue the query:

    dbcc traceon(3604)

    dbcc rebuild_log('<DB_Name>','<log_filename>') <--- You will need

    the quotation marks

    REBUILD_LOG should take less than 5 minutes even on a very large

    database. It should complete with the message

    DBCC execution completed

    8. Take the database out of bypass recovery mode by issuing the command

    update sysdatabases set status = 0 where name = '<DBName>'

    9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and

    restart SQL server. Verify the status of the

    database by running DBCC NEWALLOC and DBCC CHECKDB on the database.

  • I would like to know if there is an easy way to move all the user and databases to another SQL server. Is there s big migration tool that works well and won't cause problems?

    Thanks,

    Neil

  • If the server they are on is unstable and has caused corruption then most likely you will not be able to do. However, stop SQL and copy databases over to the new server then use sp_attach_db, use are stored in master which if the server is acting flaky I would be sceptical of but on the site there is a discussion on how to move use accoutn between server. The only issues you may run into is if new use sids are created then you may have to run sp_change_users_login with the autofix option to correct.

  • I was looking for a more automated process. There are 110 databases on there.

    The good news is it looks like a physical memory issue. We replaced RAM and we will see how it goes.

    For future reference I would like to find some sort of restore to another machine option for SQL dbs.

    Thanks,

    Neil

Viewing 15 posts - 1 through 15 (of 16 total)

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