November 20, 2002 at 3:45 pm
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
November 20, 2002 at 3:55 pm
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
November 20, 2002 at 4:02 pm
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
November 20, 2002 at 4:17 pm
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
November 20, 2002 at 4:21 pm
I did our admin user and local system and neither worked!!! There has to be some corruption somewhere in there... 🙁
Neil
November 20, 2002 at 5:12 pm
have you tried attaching to another sql server instance?
November 20, 2002 at 6:22 pm
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
November 20, 2002 at 8:08 pm
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?
November 20, 2002 at 8:10 pm
Look at DBCC CHECKDB and DBCC CHECKTABLE in BOL.
November 20, 2002 at 8:18 pm
Anything else that would detect problems and fix corruption?
I have a couple more DB's in suspect states 🙁
Neil
November 20, 2002 at 9:13 pm
If the DBCC doesn't do it, I think your chances are getting slim.
November 21, 2002 at 1:46 am
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.
November 21, 2002 at 7:38 pm
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
November 22, 2002 at 4:49 am
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.
November 22, 2002 at 5:00 am
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