asked
Unable to start MSSQLSERVER service from sql server configuration manager
Hello, I tried moving the System databases,after changing the start up parameter to the new location i triedstarting the MSSQLSERVER
service.It runs successfully for few minutes and within few seconds it automatically stops. i am unable to open the SSMS.When ichecked error logs, this is detailed i found "The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/servername.domain] for the SQL Server service". Any suggestion could be appreciated!
The error message you stated in the original post is not really an error. The SPN registration/de-registration is a standard part of the server startup and shutdown. As I understand it, this is to allow proper kerberos authentication across servers within an active directory environment. I think you need to focus on the server service account and the permissions it has on the folder where you moved the database files to. I suspect that is where the problems stem from.
It is my understanding that the message you are reading is a normal message when SQL Service stops. Do you mind listing the steps you took to move your system databases? Also tell us which SQL version you are working with. Are there any other errors in your SQL Server error log and or Windows Event viewer?
Steps i followed. 1.Moved Tempdb from default location to new location. 2.Moved Msdl and model db to new location 3.Command i tried for moving the above db is: USE DataBaseName; GO ALTER DATABASE DataBaseName MODIFY FILE ( NAME = logicalname, FILENAME = N'New location' ); GO 4.Navigate to SQL Server Configuration Manager,went to advanced tab changed -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf TO -dD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf After changing the startup parameter,i tried re-starting mssqlserver in SQL Server Configuration
Manager.It failed. So i when checked the event log if found database is not
asccessible.So i manually copied the database from default location and pasted it in the new location. And gave full right's to all system database.
Version i am using is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Developer Edition on Windows NT 6.0 (Build 6001: Service Pack 1) error which foung in the error logs: Logon Error: 18456, Severity: 14, State: 38. 2011-05-14 18:53:01.26 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database. [CLIENT: ] 2011-05-14 18:53:01.31 Server The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/Servername.domain ] for the SQL Server service.
