Startup Procedures

The first thing you have to do is make sure that the scan for startup procs configuration is set to 1.

EXEC sp_configure 'scan for startup procs'

And if it isn’t then we turn it on.

EXEC sp_configure 'scan for startup procs', 1

Output:

Configuration option ‘scan for startup procs’ changed from 0 to 1. Run the RECONFIGURE statement to install.

There is no reason to run RECONFIGURE since it won’t be used until after the next reboot anyway. Now that the system will actually use our startup SP we can create the SP we want and mark it to be run on startup.

I’m using some code of Aaron Bertrand’s (b/t) that I found in this question on Stack Overflow and modified it to suit my needs here.

CREATE PROCEDURE sp_AgentStartup AS
DECLARE @agent NVARCHAR(512);

SELECT @agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')), 
  N'SQLServerAgent');

CREATE TABLE #QueryState (CurrentServiceState varchar(20))

INSERT INTO #QueryState
EXEC master.dbo.xp_servicecontrol 'QueryState', @agent;

IF EXISTS (SELECT 1 FROM #QueryState WHERE CurrentServiceState = 'Stopped.')
	EXEC master.dbo.xp_servicecontrol 'Start', @agent;
GO
EXEC sp_procoption 'sp_AgentStartup','startup','on'
GO

sp_procoption is a system stored procedure that lets us change the options on a stored procedure and in this case set it to run on startup. Note: The account that is running SQL Server needs to have permissions to start the Agent service. (Or do whatever your startup stored procedure does.) You can have as many stored procedures running on startup as you want but remember the more you have the longer it’s going to take for your instance to start.

Run job on startup of the Agent

Ok, now that the Agent is started, if we have something more complicated we want to run, we can tell a job to run on startup. (Startup of the Agent.) It’s really pretty easy though. It’s just a matter of creating a schedule with a schedule type of Start automatically when SQL Server Agent starts.

While at the 2015 Summit I walked into the last few minutes of Russ Thomas’s (b/t) session on Stress Inoculation: Maintaining Performance Under Pressure. As a side note I was under some heavy stress myself at that particular moment (I was waiting for my session to start) which explains why I was wandering around the hall aimlessly.

From what I saw it was a great session. The premis is that you won’t be nearly as stressed out during a disaster if you’ve practiced resolving that particular disaster. Sounds reasonable right? As part of his session he did these great interactive demos. He brought people up from the audience and had them try to solve a difficult problem while he harassed them (gently) in the background. Anyone who has dealt with a server crash (for example) will realize this is a pretty good way to demonstrate the type of stress you might be experiencing. At the moment I walked in one of the audience members was being challenged. He was being asked to re-start a SQL Instance where the drive that held tempdb was no longer available. As Russ put it this is a low occurance high liability issue. (It doesn’t happen very often but when it does it’s a big deal.)

So in case it ever happens to you (or me) here we go:

Starting a SQL Server without tempdb

The situation: Your server is down. The drive/directory where tempdb is supposed to be doesn’t exist. Who knows why. Maybe those evil SAN guys forgot to re-attach your storage during a DR situation. You may or may not realize it but SQL Server will not start without tempdb. Which is fine. Just move it to a location that exists right? Well, yes. That is an important step. So here is how we

Move tempdb

1. Run an alter database

   -- Pulled straight out of MSDB
   USE master;
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
   GO

2. Stop and re-start SQL Server

 
Ok, so if you can tell me how to run an ALTER DATABASE when the instance is down I’ll be hugely impressed. Actually beyond impressed. Stunned even. We are going to have to move tempdb though. We will need it once we are back up again.

Step one is to bring the instance back up in some form or another. There are two ways (well that I can think of off the top of my head) to start up SQL Server. We can use the sqlservr.exe command to run from a command shell or use SSCM (SQL Server Configuration Manager).

Either way we need to start up SQL Server with minimal configuration (use parameter -f). One of the benefits of minimal configuration is that it doesn’t require tempdb. Be warned it also puts the instance in single user mode. So if you start the instance up and the Agent starts up with it you will need to shut it down before you can connect to the instance. Also as with any time you are in single user mode you only want to connect via SQLCMD or a query window in SSMS (no object explorer).

Note: You can also use trace flag 3608 which tells SQL Server not to start up or recover any database but Master.

Now that SQL Server is started up we can use the above code to move tempdb to an existing location and then re-start SQL Server without the startup flag or trace flag.

And after that nice long winded explanation here is the quick and easy demo.

The first thing I did was to move the tempdb of one of my instances to it’s own directory.

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\tempdb\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'c:\tempdb\templog.ldf');
GO

Next I shut down that instance and deleted the directory. Now when I try to start the instance I get these errors in the event log.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\tempdb\tempdb.mdf’.
FCB::Open failed: Could not open file c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\tempdb\tempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
The SQL Server (SQLEXPRESS2012) service terminated with the following service-specific error:
The specified resource name cannot be found in the image file.

Not terribly surprising right? I do want to point out that this tells us exactly what the error is. There are any number of reasons an instance might not start, this error tells us exactly what we need to do.

Now in this particular case we can fix the problem by just re-creating the missing directory but let’s pretend that isn’t an option (the whole drive is missing for instance). So let’s re-start the instance using the -f flag. First I open SSCM and go to the properties of the instance.

Then on the Startup Parameters tab I add a parameter -f.

Hit the Apply button then re-start the instance.

Now we move tempdb back where it belongs. Just to make the whole process fun I got this nifty error when connecting to the instance:

I’m not sure why but I connected anyway. Once connected we run our fix script:

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\templog.ldf');
GO

Now back to SSCM and the instance properties where we remove the -f flag.

Restart the instance again.

And we are back to normal.

Alright so maybe that wasn’t as quick as I said. It was easy though wasn’t it

Actually Pausing or Resuming is pretty easy. There are several ways to do it but probably the easiest is to right click on the instance name in the Configuration Manager (or SSMS) and select Pause (or Resume depending). Note: In SSCM the list of instance services is under SQL Server Services and in SSMS it’s the connection in the Object Explorer.

You can also use windows net commands.

net pause MSSQL$instancename

net continue MSSQL$instancename

Now that you know how, you still probably shouldn’t pause or resume an SQL Server instance if you don’t know what it actually does. From BOL:

When you pause an instance of Microsoft SQL Server, users that are connected to the server can finish tasks, but new connections are not allowed.

Interesting. So existing connections are unaffected but new connections are not allowed. Sounds useful. Useful how you might ask? Well lets say I need to run maintenance on a server. I let everyone who is currently connected know that they need to get out. One of the devs comes to me and let’s me know he has a batch process that is almost finished and could I please give him 10 more minutes. The problem is that if I wait I’m going to have a whole new group of people logged in. So what do I do?

Pause the instance!

Open 3 seperate query windows. Run the following code in 2 of them.

WAITFOR DELAY '00:01:30'

Next pause your instance.

Then run this code in the 3rd window.

SELECT * FROM sys.dm_exec_requests WHERE session_id > 50

You can see that not only do the running queries continue to run but new queries executed within an existing connection also run.

Now try to open a new connection.

Now we see an error letting us know that the server is in fact paused and we can’t create new connections.

At this point we can shut down connections as they complete their tasks and once everyone is cleared out finish our maintenance.

Once we are done, if we didn’t actually stop and restart the instance, we should go ahead and resume it.

As always make sure you have tried this out and are comfortable with how it works before trying it in production.