Link to home
Start Free TrialLog in
Avatar of mgmanoj
mgmanoj

asked on

Error 9003

Database turned up in suspect mode.  We found drive was not having space. So we created the space & added new log file to recover. It did not worked. used sp_resetstatus & restarted the sql server that also did not worked.  Detached the database & tried to attach again but now its not attaching. Unfortunately sp_single_file_Db also not creating new log file as at the time of detaching the database, database was having two log files.

Error 9003 gets displayed when tried to attach the database.

Thanks for the help in advance.

Manoj
Avatar of xenon_je
xenon_je

When you executed sp_resetstatus  what it returned? There was any error?, it returned 0 ? (0 is for success)

Related to suspect here is what BOL says that you should have done:

1) Execute sp_resetstatus.

2) Use ALTER DATABASE to add a data file or log file to the database.

3) Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.

4) Free disk space and rerun recovery.

As I can see you made ths steps but in different order....

If I understood weell the sp_attach_single_file_db didn't worked either (it would have crated a new log file, regardless how many log file you had before.....)

So if you execute the sp_attach_single_file_db  you receive any errors (or this is the moment when you receive Error 9003??)

waiting your feedback,
        xenon
Avatar of mgmanoj

ASKER

done previously sp_resetstatus it turned the status to normal

added the log file & restarted the server, database turned into suspect again.

Detached the database

tried to use sp_attach_single_file_db  -- it gives error 945

945 means you can not use sp_attach_single_file_db for database having more than one log file.

now it is not attaching back with all the original log files also.

- Manoj
ASKER CERTIFIED SOLUTION
Avatar of xenon_je
xenon_je

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mgmanoj

ASKER

I fixed it. Created new database with same file size, name & log files.  Shut down the instance. Replaced only the data file. Restarted the server. Database came in suspect mode. Changed the mode to emergency mode via updating sysdatabases table.  now i can retrieve the data.  So with dts copying all data to new database.

Thanks for all your help.

-Manoj
Avatar of mgmanoj

ASKER

I fixed it. Created new database with same file size, name & log files.  Shut down the instance. Replaced only the data file. Restarted the server. Database came in suspect mode. Changed the mode to emergency mode via updating sysdatabases table.  now i can retrieve the data.  So with dts copying all data to new database.

Thanks for all your help.

-Manoj
mgmanoj:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.