MSSQLSERVER service will not start
-
Good Morning - I have a SQL Server 2014 that I inherited that I use as backup server. I would like to use it but MSSQLSERVER service will not start. It worked several months ago but it really just sits, neglected, until someone needs it. I cannot log into SSMS to use any of the utilities there so I am kind of at a loss. Please note, there are no backups of this server. My production server is backed up on a regular basis. This server is used for testing only. When attempting to start the service, I receive this error
In the SQL Server Error Logs, there are a variety of errors beginning with this one:
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:21901; actual 0:0). It occurred during a read of page (1:21901) in database ID 8 at offset 0x0000000ab1a000 in file 'E:\APPS\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DatabaseDat.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Then this one:
Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 824, state 2, severity 24. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2020-06-04 10:03:27.98 spid7s Error: 3417, Severity: 21, State: 3.
2020-06-04 10:03:27.98 spid7s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
While I am very familiar with SSMS, I have no idea what to do with these errors if I cannot access it. That is why I posted in the newbie forum. If I am in the wrong place, please let me know. I would really appreciate it if someone could assist me with this issue. Thank you in advance for your help. Please let me know if I can provide any other information.
-
It appears that someone attempted to perform an upgrade - of some sort. This could be an version, edition or service pack upgrade that failed.
Since this is a 'backup' server that is not currently utilized - I would recommend uninstalling SQL Server and reinstalling, then patching to the same version as your production environment. Once you have a working system, you can then transfer/copy over logins, agent jobs, databases, etc... as needed from production.
You may need a process that removes production data - or mask that data, but that would be needed any time you refreshed this backup anyways.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeffrey - Thanks for the information. It appears that there are at least 2 instances of SQL, possibly 4 (see below). Should I uninstall all of them? There are some dev databases that do not exist anywhere else. Is there any way to repair a SQL Server install or repair these databases without SSMS?
-
I cannot say whether or not those other instances should be removed - it really depends on what they are used for (if they are used) and how you want to proceed.
You may be able to recover using the command line to recover/rebuild the master database. I don't have the link available - but you can search for rebuilding system databases.
Check the services to see what instances of SQL Server are actually installed. This list shows the components installed from each Edition of SSMS that was installed - as well as the components for SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I was looking at information to rebuild the system databases but I am not sure I have the correct information...what would I use between the quotes, the domain account that I am logged in with? Do I enter it with a \ between Domain and user?
SQLSYSADMINACCOUNTS=“DomainNameDomainUser“
Sorry for all of the questions...someone else set this up and even though it is a test server, I don't want to mess it up too badly. I believe I also need to change directory to the folder where this SQL instance is installed, correct?
-
You need to specify the admin account that will be used - one that has sysadmin access to that instance of SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you. I entered that information and tried to follow the directions in this article https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-ver15 but the command fails almost immediately.
I will try to review the setup logs to see if I can figure anything out.
-
After going down that rabbit hole, I stumbled on a way to at least start the service by bypassing the upgrade script. Once I was able to start MSSQLSERVER service, I was able to run DBCC CHECKDB on the first database (not the master) that generated errors. That has opened up a whole new list of challenges...I'm not sure what prompted the upgrade script or if I can stop it from upgrading but I will attempt to address each of the errors and see what I can come up with. I appreciate all of your help.
- berndta wrote:
After going down that rabbit hole, I stumbled on a way to at least start the service by bypassing the upgrade script. Once I was able to start MSSQLSERVER service, I was able to run DBCC CHECKDB on the first database (not the master) that generated errors. That has opened up a whole new list of challenges...I'm not sure what prompted the upgrade script or if I can stop it from upgrading but I will attempt to address each of the errors and see what I can come up with. I appreciate all of your help.
This is why I recommended uninstalling that instance and reinstalling. If you do manage to 'fix' this and get it working - you probably will have spent much more time doing that than it would take to rebuild the instance. The user databases are not going to be current with production - so you already have to refresh them...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply