SQL Server instance service stops regularly

Question

I had install the SQL Server Exppress edition on the production computer, the problem is the service stops regularly after a couple of days. I need to restart the service to make my program working. This is an unexpected behaviour, how can I solve this issue permanently. I had also made some modification in the service properties but problem still persist.

Shahriar Nikkhah · Answer

Hi Devendar Yes you are right but when you have your SQL Server running you can go to the log and see when and maybe how/who shut the server down. get the service running go to the logs and investigate how the server was down find a solution or post the error/message

Shahriar Nikkhah · Answer

SQL express on production?????????????? OK, but i don't like it, anyways I am going to assume that a policy from the OS is forcing the SQL to shut down , check the SQL Logs and see why/how/who got the service turned off Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key , LogDate datetime null , ProcessInfo nvarchar(100) null , LogText nvarchar(4000) null ) Insert Into @ErrorLog (LogDate, ProcessInfo, LogText) Exec master..xp_readerrorlog Select * From @ErrorLog --Where CharIndex('Backup', ProcessInfo) = 0 Order By LogID Desc BEGIN TRAN Declare @OldestLog datetime, @FirstLog int Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key , LogDate datetime null , ProcessInfo nvarchar(100) null , LogText nvarchar( max ) null ) Declare @EnumLogs Table (ArchiveNum int not null primary key , ArcDate Datetime not null , LogFileSize bigint not null ) --SELECT @OldestLog = Max(ArchiveFileDate) FROM [tbl_ArchiveErrorLogs] Set @OldestLog = '01/19/2018' Insert Into @EnumLogs Exec master..xp_enumerrorlogs Select Top 1 @FirstLog = ArchiveNum From @EnumLogs Where ArcDate Order By ArcDate Desc If @FirstLog Is Null Begin Select Top 1 @FirstLog = ArchiveNum From @EnumLogs Order By ArchiveNum Desc End While @FirstLog >= 0 Begin Insert Into @ErrorLog (LogDate, ProcessInfo, LogText) Exec master..xp_readerrorlog @FirstLog -- SELECT @FirstLog Set @FirstLog = @FirstLog - 1 End Select * From @ErrorLog Order By LogID Desc ROLLBACK

Devendra Kumar · Answer

@Sankara Krishnan Venugopal Already tried that, I had already attached the screenshot @Shahriar Nikkhah Sir, if the service is not running then how can I execute the query? How can I look the issue outside the box.

Shahriar Nikkhah · Answer

SQL express on production?????????????? OK, but i don't like it, anyways I am going to assume that a policy from the OS is forcing the SQL to shut down , check the SQL Logs and see why/how/who got the service turned off Just dig into the logs you will find why. some codes that might help you to read the logs PLEASE RUN them one by one and in some cases line by line Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key , LogDate datetime null , ProcessInfo nvarchar(100) null , LogText nvarchar(4000) null ) Insert Into @ErrorLog (LogDate, ProcessInfo, LogText) Exec master..xp_readerrorlog Select * From @ErrorLog --Where CharIndex('Backup', ProcessInfo) = 0 Order By LogID Desc BEGIN TRAN Declare @OldestLog datetime, @FirstLog int Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key , LogDate datetime null , ProcessInfo nvarchar(100) null , LogText nvarchar( max ) null ) Declare @EnumLogs Table (ArchiveNum int not null primary key , ArcDate Datetime not null , LogFileSize bigint not null ) --SELECT @OldestLog = Max(ArchiveFileDate) FROM [tbl_ArchiveErrorLogs] Set @OldestLog = '01/19/2018' Insert Into @EnumLogs Exec master..xp_enumerrorlogs Select Top 1 @FirstLog = ArchiveNum From @EnumLogs Where ArcDate Order By ArcDate Desc If @FirstLog Is Null Begin Select Top 1 @FirstLog = ArchiveNum From @EnumLogs Order By ArchiveNum Desc End While @FirstLog >= 0 Begin Insert Into @ErrorLog (LogDate, ProcessInfo, LogText) Exec master..xp_readerrorlog @FirstLog -- SELECT @FirstLog Set @FirstLog = @FirstLog - 1 End Select * From @ErrorLog Order By LogID Desc ROLLBACK --------------------------------------------------------------------------------------------------- --- 1 = SQL Server logs (Management) , 2 = SQL Agent Error log EXEC Master..sp_enumerrorlogs 1 EXEC Master..sp_enumerrorlogs 2 --------------------------------------------------------------------------------------------------- --- 0 = current , 2 = SQL Agent log EXEC sp_readerrorlog 0 , 1 EXEC sp_readerrorlog 0 , 2 --------------------------------------------------------------------------------------------------- -- This extended stored procedure returns the list of Windows NT groups and their description. -- To get the list of the Windows NT groups, run: EXEC xp_enumgroups --------------------------------------------------------------------------------------------------- EXEC xp_readerrorlog 0 EXEC sp_readerrorlog 0

Sankara Krishnan Venugopal · Answer

HI, Please refer the following link, http://answerhub.flowsoftware.com/questions/583/why-does-sql-server-services-keep-on-stopping.html Thanks.

Shahriar Nikkhah · Answer

I see "Report Server Windows Service" in the XML but i prefer that you check the "Event Viewer" for your Windows, it migh give you a better understanding. check the "Event Viewer"

Devendra Kumar · Answer

@Shahriar Nikkhah I am getting only the xml form of error, from that it cannot be determined what was causing the problem. How can I solve the problem. Its very strange behavious that caused on the client pc and sometimes on my pc also.

Shahriar Nikkhah · Answer

that is good you are getting close to the issue just find the issue in the SQL Log, or maybe in the OS Log (like Event Log in windows)

Devendra Kumar · Answer

It happens to my pc also. I got the error message from the eventlog but could not get any hint - Event xmlns = "http://schemas.microsoft.com/win/2004/08/events/event" > - System > Provider Name = "Report Server Windows Service (SQLEXPRESS)" /> EventID Qualifiers = "0" > 107 EventID > Level > 2 Level > Task > 5 Task > Keywords > 0x80000000000000 Keywords > TimeCreated SystemTime = "2018-04-14T10:04:05.230526600Z" /> EventRecordID > 57154 EventRecordID > Channel > Application Channel > Computer > DEVENDRA-PC Computer > Security /> System > - EventData > Data > Report Server Windows Service (SQLEXPRESS) Data > EventData > Event >