SQL SERVER 9003 error solution: sql9003

SQL SERVER 9003 error solution: sql9003

SQLSERVER 9003 error solution only applies to SQL2000

(Applicable only to SQL2000)

"The new database 'pos' cannot be opened '. The create database is aborted. (Microsoft SQL Server, error: 9003 )"

The 9003 error may be due to the log file, and the database file may be damaged. Therefore, the dbcc checkdb command is used.

The method is as follows:

1. We use the default method to create a database for recovery (such as pos ). It can be created in SQL Server Enterprise Manager.

2. Stop the database server.

3. Delete the log file pos_log.ldf of the database you just generated, and overwrite the generated database data file pos_data.mdf with the mdf file to be restored.

4. Start the database server. The database pos status is "questionable ". At this time, you cannot perform any operations on this database.

5. Set the database to allow direct operation of system tables. In SQL Server Enterprise Manager, right-click the database Server and select "properties". On the "Server Settings" Page, select "allow direct modification to system directory. You can also use the following statement.

use master go exec sp_configure 'allow updates',1 go reconfigure with override go 

6. Set pos to Emergency Repair Mode

update sysdatabases set status=-32768 where dbid=DB_ID('pos') go

At this time, you can see in SQL Server Enterprise Manager that the database is in "read-only, suspicious, offline, and emergency mode" to see the tables in the database, but only the system tables

7. perform the following recovery operations to recreate the database log file.

dbcc rebuild_log('pos','D:\Program Files\Microsoft SQL Server\MSSQL\Data\pos_log.ldf') go

If the following prompt is displayed during execution:
Server: Message 5030, level 16, status 1, Row 1
The database cannot be locked for this operation.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
Note that your other programs are using the database. If you opened the system table of the pos database using SQL Server Enterprise Manager in step 6, you can exit SQL Server Enterprise Manager.
The prompt for correct execution should be similar:
Warning: the database's 'pos' log has been rebuilt. Transaction consistency is lost. Dbcc checkdb should be run to verify physical consistency. The database must be reset and redundant log files may need to be deleted.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
In SQL Server Enterprise Manager, you can see that the database status is "only for DBO ". Now you can access the user tables in the database.

8. Verify Database Consistency (omitted)

dbcc checkdb('pos') go

The general execution result is as follows:

CHECKDB finds 0 allocation errors and 0 consistency errors (in the 'pos 'database ).
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

9. Set the database to normal

exec sp_dboption 'pos','dbo use only','false' go

If no error occurs, Congratulations. Now we can use the recovered database normally.

10. In the last step, we need to restore the "allow direct modification to the system directory" set in Step E. It is dangerous to directly operate system tables. Of course, we can recover the data in SQL Server Enterprise Manager or use the following statement.

exec sp_configure 'allow updates',0 go reconfigure with override go

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!