Having a corrupt Model DB after movement of system DB datafiles to separate drives.

Have you ever wondered how movement of system DB datafiles, could affect your SQL Server?

Recently, I came across a situation wherein I had moved the Model/MSDB datafiles from the contemporary location (C:\…\MSSQL\Data) to a separate drive using the following method-

1) ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = ‘new_path\os_file_name’ ) 2) Stopped SQL Server services. 3) Moved the file or files to the new location. 4) Restart services using Config. Manager and services came up fine. 5) Verified the file changes by running- SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files

Everything seems to be working fine as per expected and changes were reflected in sysaltfiles table after the above process was followed.

But the moment I removed the files from the actual location (C:\…\MSSQL\Data) & restarted the Windows server, SQL Server services didn’t come up automatically (Tried Manually as well) and in turn, there was an error in Event Viewer/Error log –

Event ID: 9003
The log scan number (19:492:1) passed to log scan in database ‘model’ 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.

It clearly indicated the corruption of Model DB.

I couldn’t start SQL Server (Not in single user mode with trace 3608 and with -m option), so there was no point in restoring the Model DB with previous night backup.

To overcome this issue, I copied the model datafiles from the same server (Under C:\…MSSQL\Binn\Templates) to my desired location and tried starting SQL Server services. They came up smoothly without any issues :).

Now, you must restore model backup because you have taken files from the RTM build.

Things are working fine and as per expectations.

Moral of the story:: Remember, we have a template folder in default installation directory from where we can extract the datafiles, in case of such issues.

Hope this helps you too.

Cheers!
Aman Kharbanda

Error: 17836 // Length specified in network packet payload did not match number of bytes read.

One of the prospect in hardening SQL Server includes changing the port from default 1433 to a customized one.

And while making such changes, application teams unknowingly try to establish connections and in turn,  we notice the below errors in SQL Server error log file-

Error: 17836, Severity: 20, State: 17.
Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. 

Users can lose connectivity to SQL Server if such error message is reported in the SQL Server error log file.

This issue occurs when the Detect services running on non-standard ports option is enabled in the Discovery module. The event is logged because SQL Server 2005/8/8R2 is unable to interpret the incoming packet.

Perform a nslookup of the CLIENT IP Address that is listed in the error message and find out what computer it is that is connecting.  Then you need to check that machine and determine what specifically is connecting to the SQL Server.  You might get more infromation from doing a SQL Trace for the Errors and Warnings Event Class and have the ClientProcessID column in the trace data.  When the error spikes, you might get the PID for the process that is connecting from that machine, and then you can find that process in Task Manager on that machine by adding the PID to the data displayed (View -> Select Columns).

If you have antivirus running on your system and SQL Server is not running default port then Disable the Detect services running on non-standard ports option in the scan configuration.

Hope this helps.

Cheers!
Aman Kharbanda