Solution to SQL Server 2000 9003 error (SQL2000 only)

Time:2020-1-19

About sqlserver 9003 error resolution only for SQL2000:

Unable to open new database ‘pos’. Create database aborted. (Microsoft SQL server, error: 9003)“

Looking at the 9003 error, I think it may be due to the log file. Looking at the database file, I think of the DBCC CHECKDB instruction

The method is as follows:

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

2. Stop the database server.

3. Delete the log file pos_log.ldf of the database just generated, and overwrite the database data file just generated with the database MDF file to be recovered

pos_data.mdf。

4. Start the database server. At this point, you will see that the database POS status is in doubt. No operation can be performed on this database at this time.

5. Set database to allow direct operating system tables. In this operation, you can select a database server in SQL Server Enterprise Manager, right-click, and select properties. On the server settings page, select allow direct modification of system directory. You can also use the following statements.


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

6. Set POS as 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 / suspect / offline / emergency mode”. You can see the tables in the database, but only the system tables are available

7. Perform the real recovery operation below to rebuild the database log file


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

During execution, if you encounter the following prompt:

Server: message 5030, level 16, status 1, line 1

Failed to lock the database exclusively to perform the operation.

DBCC execution completed. If DBCC outputs an error message, contact your system administrator.

This indicates that your other programs are using the database. If you have just opened the system table of POS library using SQL Server Enterprise Manager in step 6, you can exit SQL Server Enterprise Manager.

The prompt to complete correctly should be similar to:

Warning: the log for database ‘pos’ has been rebuilt. The consistency of the transaction has been lost. DBCC CHECKDB should be run to verify physical consistency. The database options will have to be reset and redundant log files may need to be removed.

DBCC execution completed. If DBCC outputs an error message, contact your system administrator.
In SQL Server Enterprise Manager, you will see that the database status is “only for dbo use”. Now you can access the user table in the database.

8. Verify database consistency (can be omitted)


dbcc checkdb('pos') 
go

The general implementation results are as follows:

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘pos’.
DBCC execution completed. If DBCC outputs an error message, contact your system administrator.

9. Set the database to normal state


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

If there is no error, Congratulations, you can use the recovered database normally now.

10. In the last step, we need to restore the item “allow direct modification of system directory” set in step E. Because it is a dangerous thing to operate the system table directly. Of course, we can restore it in SQL Server Enterprise Manager or use the following statements to complete


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

The above is the SQL Server 2000 9003 error solution introduced by Xiaobian to you (only applicable to SQL2000). I hope it can help you. If you have any questions, please leave a message to me and Xiaobian will reply to you in time. Thank you very much for your support of the developepaer website!

Recommended Today

How to choose a cluster server?

The site group server is generally used to store multiple websites, and the security and IP requirements are relatively high. Therefore, when selecting the station group server, we should choose according to the characteristics of the station group server. Several factors should be considered when selecting the server of station cluster: 1. Station group server […]