SQL Server 9003 troubleshooting

Time:2020-1-16

Sqlserver 9003 error resolution only applies to SQL2000

(SQL2000 only)

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 pos_data.mdf just generated with the database MDF file to be recovered.

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, select properties, and select allow direct modification of system directory on the server settings page. 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 / doubt / offline / emergency mode”. You can see the tables in the database, but there are only system tables

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, please contact your system administrator.
This indicates that your other programs are using the database. If you just opened the system table of POS Library in step 6 using SQL Server Enterprise Manager, 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 you may need to delete redundant log files.
DBCC execution completed. If DBCC outputs an error message, please contact your system administrator.
When you open SQL Server Enterprise Manager, you will see that the database status is “dbo only”. 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, please 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 recover in SQL Server Enterprise Manager, or we can use the following statements to complete


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

Thank you for reading, hope to help you, thank you for your support!

Recommended Today

[reading notes] calculation advertising (Part 3)

By logm This article was originally published at https://segmentfault.com/u/logm/articles and is not allowed to be reproduced~ If the mathematical formula in the article cannot be displayed correctly, please refer to: Tips for displaying the mathematical formula correctly This article isComputing advertising (Second Edition)Reading notes. This part introduces the key technology of online advertising, which is […]