Suddenly in the next morning i was unable to connect SQL server, our web app was based on sql server session state
manager. I was stuck with the following error.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ComponentModel.Win32Exception: The system cannot find the file specified
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[Win32Exception (0x80004005): The system cannot find the file specified]
[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) +1394
but there i was unable to start the services. I tried everything
like change logon account
restart server
repair SQL server
but again and again i was getting same error, and i decided to review event viewer.
And there what i got was:
The log scan number (4863:168:0) passed to log scan in database 'master' 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.
The description for Event ID 7024 from source Service Control Manager cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
SQL Server (MSSQLSERVER)
%%3417
The locale specific resource for the desired message is not present
After so many investigation i was know able to understand that master.mdf file has been corrupted.
Now i was trying to reset master database, but i had no options as i was unable to login in to sql server through
management studio and through sqlcmd, though i got an idea and installed a new instance of sql server express and
copied the master.mdf & mastlog.ldf from
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA
to
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA
Now we had fresh master mdf and ldf file in MSSQLSERVER and i tried to start sql server service and it worked. :-)
Now i tried to browse my web app and i got
Failed to login to session state SQL server for user 'u_webapp'.
Means i lost all the users and databases from the list and it was a new trouble. :-(
Suddenly i got new idea to restore old master.bak for the same instance which scheduled weekly for me.
But using wizard i got new problem. Unable to restore database, use single login.
I got reference from https://msdn.microsoft.com/en-us/library/ms188236.aspx
and did it through cmd
C:\Users\administrator>net start MSSQLSERVER /m
The requested service has already been started.
More help is available by typing NET HELPMSG 2182.
C:\Users\administrator>net stop MSSQLSERVER
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.
C:\Users\administrator>net start MSSQLSERVER /m
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.
C:\Users\administrator>sqlcmd
1> RESTORE DATABASE master FROM DISK = 'E:\Work\SQL\Database\WEEKLY\master_backu
p_2016_12_09_140036_2866744.bak' WITH REPLACE;
2> go
Processed 456 pages for database 'master', file 'master' on file 1.
Processed 4 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
C:\Users\administrator>sqlcmd
All set now and now my database is working smoothly.
manager. I was stuck with the following error.
The system cannot find the file specified
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ComponentModel.Win32Exception: The system cannot find the file specified
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[Win32Exception (0x80004005): The system cannot find the file specified]
[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) +1394
Investigation
I logged in to Windows server and was trying to start sql server through configuration manager and through services.mscbut there i was unable to start the services. I tried everything
like change logon account
restart server
repair SQL server
but again and again i was getting same error, and i decided to review event viewer.
And there what i got was:
The log scan number (4863:168:0) passed to log scan in database 'master' 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.
The description for Event ID 7024 from source Service Control Manager cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
SQL Server (MSSQLSERVER)
%%3417
The locale specific resource for the desired message is not present
Causes and Solution
After so many investigation i was know able to understand that master.mdf file has been corrupted.Now i was trying to reset master database, but i had no options as i was unable to login in to sql server through
management studio and through sqlcmd, though i got an idea and installed a new instance of sql server express and
copied the master.mdf & mastlog.ldf from
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA
to
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA
Now we had fresh master mdf and ldf file in MSSQLSERVER and i tried to start sql server service and it worked. :-)
Now i tried to browse my web app and i got
Failed to login to session state SQL server for user 'u_webapp'.
Means i lost all the users and databases from the list and it was a new trouble. :-(
Suddenly i got new idea to restore old master.bak for the same instance which scheduled weekly for me.
But using wizard i got new problem. Unable to restore database, use single login.
I got reference from https://msdn.microsoft.com/en-us/library/ms188236.aspx
and did it through cmd
C:\Users\administrator>net start MSSQLSERVER /m
The requested service has already been started.
More help is available by typing NET HELPMSG 2182.
C:\Users\administrator>net stop MSSQLSERVER
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.
C:\Users\administrator>net start MSSQLSERVER /m
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.
C:\Users\administrator>sqlcmd
1> RESTORE DATABASE master FROM DISK = 'E:\Work\SQL\Database\WEEKLY\master_backu
p_2016_12_09_140036_2866744.bak' WITH REPLACE;
2> go
Processed 456 pages for database 'master', file 'master' on file 1.
Processed 4 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
C:\Users\administrator>sqlcmd
All set now and now my database is working smoothly.