May 10, 2011 at 7:44 am
Can somebody please help me.My sql server does not start and when checked,I found the following info in dump file in LOG folder :
2011-05-08 15:34:43.08 Server Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2011-05-08 15:34:43.08 Server (c) 2005 Microsoft Corporation.
2011-05-08 15:34:43.08 Server All rights reserved.
2011-05-08 15:34:43.08 Server Server process ID is 18408.
2011-05-08 15:34:43.08 Server Authentication mode is MIXED.
2011-05-08 15:34:43.08 Server Logging SQL Server messages in file 'K:\DATA\MSSQL.6\MSSQL\LOG\ERRORLOG'.
2011-05-08 15:34:43.08 Server This instance of SQL Server last reported using a process ID of 11016 at 5/8/2011 2:38:33 PM (local) 5/8/2011 9:38:33 PM (UTC). This is an informational message only; no user action is required.
2011-05-08 15:34:43.08 Server Registry startup parameters:
2011-05-08 15:34:43.08 Server -d K:\DATA\MSSQL.6\MSSQL\DATA\master.mdf
2011-05-08 15:34:43.08 Server -e K:\DATA\MSSQL.6\MSSQL\LOG\ERRORLOG
2011-05-08 15:34:43.08 Server -l K:\DATA\MSSQL.6\MSSQL\DATA\mastlog.ldf
2011-05-08 15:34:43.09 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-05-08 15:34:43.09 Server Detected 16 CPUs. This is an informational message; no user action is required.
2011-05-08 15:34:43.17 Server Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
2011-05-08 15:34:50.20 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2011-05-08 15:34:50.20 Server Error: 17311, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2011-05-08 15:34:50.20 Server Using 'dbghelp.dll' version '4.0.5'
2011-05-08 15:34:50.20 Server **Dump thread - spid = 0, PSS = 0x00000000, EC = 0x00000000
2011-05-08 15:34:50.20 Server ***Stack Dump being sent to K:\DATA\MSSQL.6\MSSQL\LOG\SQLDump0018.txt
2011-05-08 15:34:50.20 Server * *******************************************************************************
2011-05-08 15:34:50.20 Server *
2011-05-08 15:34:50.20 Server * BEGIN STACK DUMP:
2011-05-08 15:34:50.20 Server * 05/08/11 15:34:50 spid 0
2011-05-08 15:34:50.20 Server *
2011-05-08 15:34:50.20 Server * ex_handle_except encountered exception C0000005 - Server terminating
2011-05-08 15:34:50.20 Server *
2011-05-08 15:34:50.20 Server *
2011-05-08 15:34:50.20 Server * MODULE BASE END SIZE
2011-05-08 15:34:50.20 Server * sqlservr 01000000 02C09FFF 01c0a000
2011-05-08 15:34:50.20 Server * ntdll 7C800000 7C8C2FFF 000c3000
2011-05-08 15:34:50.20 Server * kernel32 77E40000 77F41FFF 00102000
2011-05-08 15:34:50.20 Server * MSVCR80 78130000 781CAFFF 0009b000
2011-05-08 15:34:50.20 Server * msvcrt 77BA0000 77BF9FFF 0005a000
2011-05-08 15:34:50.20 Server * MSVCP80 7C420000 7C4A6FFF 00087000
2011-05-08 15:34:50.20 Server * ADVAPI32 7D1E0000 7D27BFFF 0009c000
2011-05-08 15:34:50.20 Server * RPCRT4 77C50000 77CEFFFF 000a0000
2011-05-08 15:34:50.20 Server * Secur32 76F50000 76F62FFF 00013000
2011-05-08 15:34:50.20 Server * USER32 77380000 77410FFF 00091000
2011-05-08 15:34:50.20 Server * GDI32 77C00000 77C48FFF 00049000
2011-05-08 15:34:50.20 Server * CRYPT32 761B0000 76242FFF 00093000
2011-05-08 15:34:50.20 Server * MSASN1 76190000 761A1FFF 00012000
2011-05-08 15:34:50.20 Server * MSWSOCK 71B20000 71B60FFF 00041000
2011-05-08 15:34:50.20 Server * WS2_32 71C00000 71C16FFF 00017000
2011-05-08 15:34:50.20 Server * WS2HELP 71BF0000 71BF7FFF 00008000
2011-05-08 15:34:50.20 Server * USERENV 76920000 769E1FFF 000c2000
2011-05-08 15:34:50.20 Server * opends60 333E0000 333E6FFF 00007000
2011-05-08 15:34:50.20 Server * NETAPI32 71C40000 71C96FFF 00057000
2011-05-08 15:34:50.20 Server * SHELL32 7C8D0000 7D0CEFFF 007ff000
2011-05-08 15:34:50.20 Server * SHLWAPI 7D180000 7D1D1FFF 00052000
2011-05-08 15:34:50.20 Server * comctl32 77420000 77522FFF 00103000
2011-05-08 15:34:50.20 Server * psapi 76B70000 76B7AFFF 0000b000
2011-05-08 15:34:50.20 Server * instapi 48060000 48069FFF 0000a000
2011-05-08 15:34:50.20 Server * sqlevn70 4F610000 4F7B8FFF 001a9000
2011-05-08 15:34:50.20 Server * SQLOS 344D0000 344D4FFF 00005000
2011-05-08 15:34:50.20 Server * dbghelp 47CE0000 47DF4FFF 00115000
2011-05-08 15:34:50.20 Server *
2011-05-08 15:34:50.20 Server * Edi: 007BD98C: 0010E198 000DF598 0010E0E4 00000188 00000190 00000018
2011-05-08 15:34:50.20 Server * Esi: 00000000:
2011-05-08 15:34:50.20 Server * Eax: 007BD870: 000042AC 00000000 00000000 77E4BEF7 00000000 00000002
2011-05-08 15:34:50.20 Server * Ebx: 0000003F:
2011-05-08 15:34:50.20 Server * Ecx: 007BDEE0: 00000000 00010007 00000000 00540052 0010E19C 00000000
2011-05-08 15:34:50.20 Server * Edx: 0000003D:
2011-05-08 15:34:50.20 Server * Eip: 77E4BEF7: 10C2C95E 01093D00 840FC000 00000095 F29DE950 9090FFFF
2011-05-08 15:34:50.20 Server * Ebp: 007BD8C0: 007BD904 021A29C4 000042AC 00000000 00000000 00000000
2011-05-08 15:34:50.20 Server * SegCs: 7813001B: 00000000 00000000 00000000 00000000 00000000 00000000
2011-05-08 15:34:50.20 Server * EFlags: 00000246:
2011-05-08 15:34:50.20 Server * Esp: 007BD86C: 00000000 000042AC 00000000 00000000 77E4BEF7 00000000
2011-05-08 15:34:50.20 Server * SegSs: 78130023: 00000000 00000000 00000000 00000000 00000000 00000000
2011-05-08 15:34:50.20 Server * *******************************************************************************
2011-05-08 15:34:50.20 Server * -------------------------------------------------------------------------------
2011-05-08 15:34:50.20 Server * Short Stack Dump
2011-05-08 15:34:50.22 Server 77E4BEF7 Module(kernel32+0000BEF7)
2011-05-08 15:34:50.22 Server 021A29C4 Module(sqlservr+011A29C4)
2011-05-08 15:34:50.22 Server 021A6880 Module(sqlservr+011A6880)
2011-05-08 15:34:50.22 Server 021A642D Module(sqlservr+011A642D)
2011-05-08 15:34:50.22 Server 02414429 Module(sqlservr+01414429)
2011-05-08 15:34:50.22 Server 77E761B7 Module(kernel32+000361B7)
2011-05-08 15:34:50.22 Server 77E792A3 Module(kernel32+000392A3)
2011-05-08 15:34:50.23 Server Stack Signature for the dump is 0x1A336D5C
May 10, 2011 at 7:50 am
Is the OS 32 bit or 64 bit?
What's the total memory?
How much is available?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2011 at 7:59 am
Assuming this system USED to start up successfully, SOMETHING has changed:
1) check permissions on drives, directories, files that have sql server data.
2) did someone enable windows Data Execution Prevention stuff?
3) review AD for changes to account used for SQLServer.exe service login
4) anyone changed memory settings for sql server recently?
5) can you start in single user mode from command prompt?
6) does the server actually become usable after this error or does the startup stop?
7) any new antivirus or antimalware crap loaded on the server?
MANY things to investigate here. If this is an important box, get Microsoft support or a knowledgeable consultant on the phone to get up and running as quickly as possible. Forums are NOT the optimal way to solve critical outages!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 10, 2011 at 8:00 am
Yes its a 32 bit
and
available memory was 29918 MB
Free system page table entries was 93985
May 10, 2011 at 8:02 am
This is probably because the BPool is not able to grow during SQL startup. If you have multiple instances of SQL on the box, then make sure that the max server memory of all the instance put together is less than the physical memory available on the server. Also, check if the Available MBytes (Perfmon Memory counter) on the server shows a low value (eg. 250MB or lesser). In such a case, you have memory pressure on your instance and the SQL process is not able to acquire enough memory for the BPool during startup. What is your SQL Server Max Server Memory value?
Try starting SQL Server with -c -m -T3608 -f flags and see if the SQL instance starts up. If it does, reduce the max server memory and try starting SQL Server without the trace flags.
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: TroubleshootingSQL
Twitter: @banerjeeamit
May 10, 2011 at 8:03 am
TheSQLGuru (5/10/2011)
Assuming this system USED to start up successfully, SOMETHING has changed:1) check permissions on drives, directories, files that have sql server data.
2) did someone enable windows Data Execution Prevention stuff?
3) review AD for changes to account used for SQLServer.exe service login
4) anyone changed memory settings for sql server recently?
5) can you start in single user mode from command prompt?
6) does the server actually become usable after this error or does the startup stop?
7) any new antivirus or antimalware crap loaded on the server?
MANY things to investigate here. If this is an important box, get Microsoft support or a knowledgeable consultant on the phone to get up and running as quickly as possible. Forums are NOT the optimal way to solve critical outages!!!
since 32 bit, review AWE/PAE/3GB settings in boot.ini and sql config. also see if someone put too large a number in for -g startup param if it is in place.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 10, 2011 at 8:11 am
Actually there are is a total of 32gb ram configured for this server.
There are totally 3 intances running on the server including the trouble giver instance.
We have pae in boot.ini
We have awe enabled for all the 3 Instances
We have max memory for all the 3 Instances each with 2147483647
Please assist.
May 10, 2011 at 8:20 am
Set Max Server Memory for all the instances in the following way:
Max Server Memory of Instance1 + Max Server Memory of Instance 2 + Max Server Memory of Instance 3 < Total Physical RAM - 2GB.
Leaving out 2GB for OS use. But if you have other applications or services which require physical memory, then this calculation might differ. If after setting the max server memory, the SQL instance doesn't restart, then check the Available MBytes on the server. If that is still low, then you would need to restart the other two instances to ensure that the memory requirements for all the three instances can be met after the max server memory has been set correctly. If SQL instances don't back off their memory usage, since you have not capped the max server memory, the SQL instance still may not start due to lack of available memory.
Also you are base Service Pack 2 for SQL Server which is a very outdated build. We corrected a bunch of memory related issues on later builds of SQL Server 2005. I would suggest applying Service Pack 4 at a minimum to make sure you don't have unprecendented memory usage on your SQL instance due to some known issue already corrected on a later build.
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: TroubleshootingSQL
Twitter: @banerjeeamit
May 10, 2011 at 8:29 am
Thanks everybody.
Here is the info from server (used perfmon counters)
Available memory(in mb) - 29914
Free page table entries - 93985
server a
-------
target server memory(kb) - 27785264
total server memory(kb) - 280320
server b
-------
target server memory(kb) - 27785264
total server memory(kb) - 42240
Plz assist.
May 10, 2011 at 8:34 am
As I said, set MAX SERVER MEMORY on all the instances. It looks like you are experiencing paging on the SQL Server instances. But that is a different topic altogether. The paging is probably due to not capping the SQL instances max server memory. Once that is done, check if you can start the other instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: TroubleshootingSQL
Twitter: @banerjeeamit
May 10, 2011 at 8:47 am
I'd agree with @troubleshootingsql. Set max memory in each instance, with the sum of your settings being 2GB less than the total memory.
Or to make it easy, set each instance to 10GB as max memory. That will give you 10 + 10 + 10 + 2 = 32GB
May 10, 2011 at 10:22 am
I truly thank everybody for their valuable inputs.
Will try to apply all those mentioned here.
May 10, 2011 at 11:14 am
One final thing: if you have Lock Pages In Memory setting enabled, you probably should consider turning that off since you have multiple instances.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 10, 2011 at 12:38 pm
Agree with SQLGuru here. You should disable LPIM privilege for the service accounts till you have managed to profile the memory usage for the SQL instances on the box and figured out how much memory each instance requires.
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: TroubleshootingSQL
Twitter: @banerjeeamit
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply