asked
Run Stored Procedure at Startup
I am trying to run a stored procedure at startup of the SQL instance. I created the proc in the Master database and it contains linked servers. I have setup the procedure to run at startup but when the instance restarts it does not run. I have queried xp_readerrorlog and see the message that the procedure launched > Launched startup procedure 'TestProc'. When I run the procedure manually it runs fine and when I set it up in a SQL Agent Job to run at startup it runs fine. Why would I not be able to get it to run at startup? I also created a simple proc that inserts the time into a table on startup and that works fine so I know my setup is correct and it has something to do with the linked servers.
Did you read the link in my answer? The problem you are having is most likely because the linked servers are not available when the master database is first recovered (and your procedure executes). I think you should be fine with an agent job. I suppose you could add a procedure to master that logs an error and/or sends an email if agent doesn't start. You could have it wait for 5 minutes and then use "EXEC xp_servicecontrol 'querystate', 'SQLSERVERAGENT';".
What kind of error handling do you have in the procedure? If you have a TRY/CATCH-block - what's happening in the catch block?
I will need to add in more error handling. Hopefully it will write to the errorlog though. The Proc runs fine when I run it manually.
In order to simplify the issue I created a separate proc that does a simple insert to a table on the current server from the information it pulls from a table on the linked server. I included a TRY/CATCH but it did not write anything to the error log. Once again the proc ran fine manually or within a sql agent job at startup. I am beginning to believe you can not use linked server in a proc at startup. The reason for my hesitation to just include it in a SQL agent job is because the proc adjusts memory in a failover situation, so in the event the SQL Server Agent does not start I would have to run the proc manually to adjust memory to avoiding maxing out memory on the server. Maybe I am just overthinking the risk of the SQL Server Agent not starting up.
The master database is recovered before the instance is made available on the network. Therefore, your startup procedure in master is running before the linked server is available. I am not sure why your procedure didn't log any error, however. I did a little searching and found [this][1] summary of the startup process. [1]:
http://sqltimes.wordpress.com/2013/02/10/sql-server-start-up-steps/
I was getting this error originally: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. Now once I set them on I am no longer getting any errors but the proc is not running successfully.
What is the timeout on your linked server? Is it possible the procedure is stuck waiting for a connection because it is running before the linked server is available?
I set remote login timeout (s) to 100 as a test and they didn't help. Remote query timeout is set to 600. I also set linked server connection timeout to 600 and query timeout to 600.
16 People are following this question.
