Sql-server – Unexpected Shutdown affects SQL Server DB start at OS start up

sql serversql-server-2008startup

I have an application that uses a sql server db (SQL Express 2008 R2) and starts when Windows OS starts. When the pc shutdown unexpectedly and then restart the pc the application run automatically but it' can't query the DB
I looked for the error log and it shows no record for starting the DB and I have
to open the SSMS and query the DB. Only after that the db will start (even after seconds from starting the OS)

2018-02-28 13:12:51.05 Server      Server process ID is 2420.
2018-02-28 13:12:51.50 spid7s      Starting up database 'master'.
2018-02-28 13:12:55.60 spid7s      Recovery is complete. This is an informational message only. No user action is required.
2018-02-28 13:12:58.87 spid52      Starting up database 'ReportServer$SQLEXPRESS2008R2TempDB'.
2018-02-28 13:35:27.38 spid57      Starting up database 'DBName'.

as you can see there are about 23 minutes between starting master and my db.
I also checked the service and it's running fine in the OS start.

I have 2 questions:

1- What SQL Server make to start the db and can I do that programmatically?

2- I noticed that starting 'ReportServer$SQLEXPRESS2008R2TempDB' is the beginning of the delay and it may be the cause of the delay, so can I change the order of starting the databases on the server?

Thanks in advance

Best Answer

Your DB has AUTO_CLOSE ON (which is typical of SQL Express instances, like yours). It means that the database is not opened until first accessed. This is when the first mention of it appears in the SQL ERRORLOG file.

The delay of 23 minutes is because your application is not opening the database. If you think this is a problem, it must be in your app. Are you sure your app, which must be an NT service, is configured to auto-start on OS boot?

Related Solutions

Sql-server – Start Sql Server agent when sql server starts

There are couple of steps that you can do to address the problem that you faced :

Have a startup stored procedure using sp_procoption that runs and checks and alerts you that SQL Agent is not running.

IF EXISTS (  SELECT 1 
       FROM MASTER.dbo.sysprocesses 
       WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
 SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
END 
ELSE 
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'yourDBMailProfile',
@recipients = 'dbateam@somecompany.com',
@body = 'Please check the status of SQL Agent. It is not running !',
@query = 'SELECT @@SERVERNAME AS [InstanceName], 0 AS [SQLServerAgentRunning]',
@subject = 'SQL Agent is not running',
@attach_query_result_as_file = 0 ; -- set it to 1 to receive as txt file attachment
END

Set SQL Agent service to restart

Oracle cannot start

Your permissions are incorrect.

Oracle recommends role separation, which it appears you've followed by creating separate oracle and grid users. But I'm willing to wager that your ORACLE_HOME is under /u01/app/database or similar, which means that the chmod and chown commands you ran also changed permissions and ownership on the database home, too. This defeated the role separation Oracle expected, and your database will not start.

The permission structure of an Oracle grid/database installation is far more complex than a simple chmod/chown can duplicate. For one thing, the database binary ($ORACLE_HOME/bin/oracle) requires a setguid bit:

chmod 6751 $ORACLE_HOME/bin/oracle

You also need to have correct ownership of the binary, which should be oracle in your case:

chown oracle:oinstall $ORACLE_HOME/bin/oracle

Those two commands may fix the problem, but I wouldn't trust that everything will work as it should. You could easily get into a situation where fixing one thing just shows you the next thing that's broken.

There are specific permissions required for binaries in the GRID_HOME that will differ (and be more restrictive) from the generic 775 applied to the root directory. Notably, the listener will not start correctly if the permissions and ownership are not correct.

Unfortunately while it may be possible to repair this manually, it is not straightforward. You may be able to correct the permissions in the grid installation with:

$GRID_HOME/crs/install/rootcrs.pl

But, it may just be easier to start from scratch, particularly if you want to avoid future issues and don't have a working installation to compare against.

The original gateway issue may have been that your environment was not set correctly. The gateway installation is applied to the database home; the path you noted is the grid home and includes the word "ASM" suggesting that you were attempting to install in the ASM (storage) database instead of the actual database. ASM is part of the grid installation and will be distinct from your working database.

Best Answer

Related Solutions

Sql-server – Start Sql Server agent when sql server starts

Oracle cannot start

Related Question