"stop before startup" after "Using large pages for buffer pool" during SQL Server startup
Worked on a SQL Server startup issue which I would like to share here:
Here is how the SQL Server errorlog looked like
2012-04-19 03:43:26.50 Server Microsoft SQL Server 2005 - 9.00.4035.00 (Intel IA-64)
Nov 24 2008 12:59:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
...
...
2012-04-19 03:43:26.52 Server Registry startup parameters:
2012-04-19 03:43:26.52 Server -d J:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2012-04-19 03:43:26.52 Server -e J:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2012-04-19 03:43:26.52 Server -l J:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
...
...
2012-04-19 03:43:26.52 Server -T 834
...
...
2012-04-19 03:43:26.53 Server Large Page Extensions enabled.
2012-04-19 03:43:26.53 Server Large Page Granularity: 16777216
2012-04-19 03:43:26.57 Server Large Page Allocated: 32MB
2012-04-19 03:43:26.59 Server Large Page Allocated: 32MB
2012-04-19 03:43:26.69 Server Using large pages for buffer pool.
2012-04-19 03:48:27.12 Server Service control: stop before startup
So I checked with another server on what should be the next step in the SQL Server error log after "Using larges pages..." since that's where SQL Server seems to have failed and it is supposed to be
"xxxxx MB of large page memory allocated"
So it seems like large page memory allocation failed. When we look at the startup parameters, we see trace flag 834 and this trace flag forces SQL Server to use large pages for Buffer pool memory allocations.
As per https://support.microsoft.com/kb/920093:
"... Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages cannot be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated to SQL Server...."
So in our case there was already another SQL instance running on the same node blocking this instance in allocating Large pages for buffer pool so we gave options to Cx to decide removing 834 trace flag or add more memory.
This could also happen if the large page allocation is taking longer time causing timeout. Note that server we troubleshooted is on a clustered environment.
Sakthivel Chidambaram, SQL Server Support