Shulou(Shulou.com)05/31 Report--

SQL SERVER 9003 wrong solution, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

SQLSERVER 9003 error resolution applies only to SQL2000

(for SQL2000 only)

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

Looking at the 9003 error, I thought that it might be due to the log file, and that the database file might be corrupted, so I thought of the dbcc checkdb instruction.

The methods are as follows:

1. We use the default method to set up a database for recovery (such as pos). Can be built 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 database data file pos_data.mdf that you just generated with the database mdf file you want to restore.

4. Start the database server. At this point, you will see that the status of the database pos is doubtful. Nothing can be done on this database at this time.

5. Set the database to allow direct operating system tables. This operation can select the database server in SQL Server Enterprise Manager, right-click, select "Properties", and select "allow direct modification of the system directory" on the "Server Settings" page. You can also use the following statement to do so.

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 point, 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 only the system tables.

7. Perform a real restore 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: server: message 5030, level 16, status 1, line 1 fails to exclusively lock the database to perform the operation. DBCC execution completed. If DBCC outputs an error message, contact your system administrator. Indicate that your other programs are using the database, and if you just opened the system table of the pos library using SQL Server Enterprise Manager in step 6, just quit SQL Server Enterprise Manager. The prompt for the correct completion should look like: warning: the log of the database 'pos' has been rebuilt. Transaction consistency 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 excess log files. DBCC execution completed. If DBCC outputs an error message, contact your system administrator. When you open it in SQL Server Enterprise Manager, you will see that the status of the database is "for DBO only". You can now 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 are no errors, congratulations, you can now use the restored database normally.

10. In the last step, we will restore the item "allow direct modification of the system directory" set in step E. Because usually direct operating system table is a more dangerous thing. Of course, we can restore it in SQL Server Enterprise Manager, or we can do it with the following statement

Exec sp_configure 'allow updates',0 go reconfigure with override go, after reading the above, have you mastered the solution to the error of SQL SERVER 9003? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.