Ошибка 9003 sql server

Fix Microsoft SQL Server Error 9003 an Easy Way

Table of Content

• Introduction to the Error
• Reason for this SQL Error
• Solution to Fix Error 9003
• Suspect Mode Rectification
• Bringing It All Together

What is SQL Server Model Database Error 9003?

Model database issue or the Microsoft SQL Server error 9003 is a special type of technical message that appears in SQL. These error messages are a result of a wrong sequence or when an unacceptable event ID is tracked for a specific database by the log manager. When these types of error messages appear and crash the system, error messages appear as in the below-mentioned form.

Why SQL Server Error Code 9003 Appears?

Error 9003 of SQL server is generally triggered because of data corruption issues, replication issues among MDF and LDF files, inconsistency, etc. sometimes, this issue is also a result of misconfigured system files or the registry issue of files.

Download Now Purchase Now

Fix SQL Server Model Database Error 9003

To bring database to Normal Mode and to fix model database error, the following resolutions can be implemented. Though there are no straightforward solutions available for rectifying this SQL error yet these techniques are beneficial if properly executed.

Restore from Backup:

Just try deleting the related information or contents, if the model database error appears due to the issue of replication of the data components. After this step, make sure to take restoration of the database from the available archive or backup. This is indeed a good option to backup server database of SQL because it greatly helps in restoring entire data in some really critical situations.

SQL Server Re-installation:

If user has encountered error 9003 then uninstall the present SQL Server program and then try reinstalling it on the system. This will b e great step towards elimination of error in SQL server. Also, it is advised to take a backup of all MDF components like triggers, indexes, database tables, stored procedures, etc. before actually uninstalling the program. Restore all the components back to the database after the files are reinstalled.

DBCC Implementation:

Run DBCC CHECKDB after reinstalling the program so as to check the physical, logical errors and inconsistencies in that particular database components. By using the DBCC CHECKDB command, check the consistency of MDF files i.e, of primary database. Alternatively, user may detach model database by renaming database files and attaching it further. This may prevent SQL error 9003 from appearing.

Also Read: Solution to Fix SQL Server Error 1105 Smartly

Rectification of Suspect Mode

SQL database items are marked as SUSPECT if they are affected by server error 9003. To find out which database has undergone SUSPECT mode and to troubleshoot this erroneous situation, follow the procedure given below.

• By executing DBCC CHECKDB, detect reasons for SUSPECT database.
• Now to detect the actual cause, open the SQL error log.
• After this, just bring the database to ‘Emergency Mode’

NOTE: In SUSPECT mode, it is impossible to connect to database that is why it is important to turn it to Emergency Mode.

• To check inconsistencies among database objects, run DBCC.
• After this, just turn on ‘Single Mode’ in the database.
• Using ‘REPAIR_ALLOW_DATA_LOSS’, recover database.
• Now, turn on ‘Multi-user Mode’ in database by performing suitable SQL command.

NOTE: The resolution methods mentioned-above may cause the withdrawal of SQL error 9003 from the database. Database is then brought to the normal mode when processed in an accurate way however if some mistakes are committed while executing these steps then, loss of data may occur.

Alternate Solution:

If all the above manual solutions fail to fix 9003 error code, then it is best recommended to opt for a third party software like SQL Recovery Software to overcome this issue and resolve 9003 SQL error.

Also Read: Know How to Make SQL Server Faster?

Conclusion

We have thrown lights on SQL Server database error 9003 in this post. Manual methods have been given to resolve 9003 error from MDF and NDF data files. If all the above manual solutions fail to fix 9003 error code, then it is best recommended to opt for a third party SQL MDF Recovery tool to overcome this issue and resolve 9003 SQL error.

Recently I moved up to the next consecutive error number, a 9003 error:

— 

Log Name:      Application
Source:        MSSQLSERVER
Date:          6/15/2017 4:49:59 AM
Event ID:      9003
Task Category: Server
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      Server1.domain.net
Description:
The log scan number (139943:2168:0) passed to log scan in database ‘DBStuff’ is not valid. 

This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). 

If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

—

<Cue Scary Music>

http://www.scaryforkids.com/pics/scary-music.jpg

—

Like the 9002, this is a seemingly scary error with the word “corruption” in it, but when I checked the server it appeared the database was online and I saw connections to DBStuff once I signed on.

The server was throwing a series of the errors constantly in the SQL Error Log (and Windows Application Log) for more than an hour by the time it was escalated to me – constantly every minute or less!

The DBStuff database was a relatively small database (13GB) so I went to run CHECKDB against it manually from a query window to look for corruption, but I couldn’t even get that far:

—

Msg 5901, Level 16, State 1, Line 1
One or more recovery units belonging to database ‘DBStuff’ failed to generate a checkpoint.  This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.

Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.

Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.

Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

Msg 9003, Level 20, State 9, Line 1
The log scan number (139943:2168:0) passed to log scan in database ‘DBStuff’ is not valid.

This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf).

If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

 —

Note that the last error in the series is again the 9003 error shown in the initial Log entry above.

Could there be corruption?

http://s2.quickmeme.com/img/a6/a646c45633cfab95a2aaf727480883aba7de652148fb3601db61853a62d3b3da.jpg

At that point I went Googling and found this from Nathan Courtine at dbi Services:

At first I dismissed the idea because it is talking about replication and I didn’t see any replication on Server1, but when I went to look at the log_reuse_wait_desc as the article mentions, sure enough I found this:

—

SELECT name, log_reuse_wait_desc 

FROM sys.databases

WHERE name = N’DBStuff’

—

As described in the article I tried running sp_removedbreplication against DBStuff since there is no apparent replication (publications or subscriptions) but as the author notes this did not resolve the issue in my case.

The fix described in the article involves setting up a new dummy table in the database in question and creating a publication for that dummy table, which will supposedly goose the system and reset the log_reuse_wait_desc from REPLICATION to some “normal” type such as NOTHING or CHECKPOINT.  After this you can remove the replication and the dummy table and be done.

The catch for us was that the replication components weren’t installed on the Server1 instance during its original install!

Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online. 

Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)

Sigh…

—

After mounting media and installing the Replication components the rest was relatively easy — add a dummy table, create a publication, then drop the publication and dummy table — just as described in the referenced post above.

Eventually we determined that the DBStuff database was restored from another server where it had been involved in a replication topology, which was the source of the offending bits.

—

Hope this helps!