SQL SERVER 2000 9003 Error Resolution (applicable to SQL2000 only) _mssql

About SQL Server 9003 Error resolution applies only to SQL2000:

"Unable to open new database ' POS '. CREATE DATABASE aborted. (Microsoft SQL Server, Error: 9003) "

Looking at the 9003 error, think of the possible cause of the log file, and then look at the database file may be corrupted, so think of the DBCC CHECKDB directives.

The method is as follows:

1. We use the default method to establish a database (such as POS) for recovery use. Can be built in SQL Server Enterprise Manager.

2. Deactivate the database server.

3. Delete the log file pos_log.ldf the database you just generated, overwrite the database data file you just generated with the database MDF file you want to restore

Pos_data.mdf.

4. Start the database server. You will see that the status of the database pos is "suspect." No action can be made on this database at this time.

5. Setting the database allows direct operating system tables. This action allows you to select the database server in SQL Server Enterprise Manager, right-click, select Properties, and select the Allow direct modifications to system directories on the server Settings page. You can also use the following statement to implement it.

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

6. Set up POS for Emergency Repair mode

Update sysdatabases set status=-32768 where dbid=db_id (' pos ') go 

You can see in SQL Server Enterprise Manager that the database is in "read only \ suspect \ offline \ Emergency mode" to see the tables inside the database, but only the system tables

7. The following performs a real recovery operation 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 information:

Server: Message 5030, Level 16, State 1, line 1

Failed to lock the database to perform the operation.

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

Indicates that your other program is using the database, and if you have just opened the system table of the POS library using SQL Server Enterprise Manager in step 6, quit SQL Server Enterprise Manager.

The prompt for completion should resemble the following:

Warning: Log of database ' POS ' has been rebuilt. The consistency of the transaction has been lost. DBCC CHECKDB should be run to verify physical consistency. You will have to reset the database options, and you may need to delete the extra log files.

DBCC execution completed. If DBCC prints an error message, contact your system administrator.
When you open the SQL Server Enterprise Manager, you will see that the state of the database is "for dbo use only." You can now access the user table inside the database.

8. Verify database consistency (can be omitted)

DBCC CHECKDB (' pos ') go 

The results of the general implementation are as follows:

CHECKDB found 0 allocation errors and 0 consistency errors (in database ' POS ').
DBCC execution completed. If DBCC prints an error message, contact your system administrator.

9. Set the database to a normal state

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

If there is no error, then congratulations, it is now normal to use the restored database.

10. In the final step, we will restore the "allow direct modifications to the system directory" set in step e. Because the ordinary direct operating system table is a more dangerous thing. Of course, we can recover in SQL Server Enterprise Manager, or we can use the following statement to complete

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

The above is a small set to introduce the SQL SERVER 2000 9003 Error resolution (only applicable to SQL2000), I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!