Unable to start up Server Instance

So I had a little panic situation earlier today however it is now resolved and this is how I resolved it.

The Preprod instance was down and I could not get it back up again.

When I tried to start the instance I received an error message telling me that “The request failed or the service did not respond in a timely fashion”

I checked event viewer, but there nothing helpful there “The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.”

I then tried to start the service using services.msc again no good “This service on local computer started and then stopped. some service stop automatically if then are not in use by other service or programs ”

I then checked the log file “E:\Microsoft SQL Server\MSSQL11.PREPROD\MSSQL\Log\ERRORLOG” Also unhelpful “Failed allocate pages: FAIL_PAGE_ALLOCATION 1”

Finally I rememeber changing the memory setting on preprod earlier today and allocating all it’s to memory to the default instance and so I used the startup parameter -f , which starts the instance up in minimal config mode, to quote books online “This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.” This was successful I got the instance up and running in min config mode :>D

I now logged in on the DAC connection and ran EXEC sys.sp_configure. max server memory (MB) had a config value of 128 and a running value of 2147483647.

I ran the following code
EXEC sys.sp_configure N’max server memory (MB)’, N’10000′
GO
RECONFIGURE WITH OVERRIDE
GO

we now had a config value of 10000 and a run value of 10000

I stopped the server, removed the minimal config startup parameter and restarted the service. Success.

Looking through the logs there is no record from when I changed the maximum memory value earlier in the day so I assume something must have gone wrong and it took a stupidly large value in error which caused the troubles.

Anyway it is now fixed. Yay.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: