Hello,

I have, for quite a while, been trying to diagnose a problem where my SQL
2005 server shuts down. Over a year of monitoring this probelm I have only
been able to find 2 consistant things :
The SQL server is running the same job
The shutdown (listed in event log as "unexpected") ocurs atthe same time
10:32 pm


This shutdown may happen twice in the same week....but then may not happen
for a month the 2 items above are the only consistant facts about this
problem

The job that is always exectuing when the shutdaow ocors is a big import of
data from an orcale database.

Since I dont what I am doing ...... I statred off with examining every
statement that is in any log I can find and make sure there are no server
hardware issues...so that is all done no reported hardware issues.

Here are some SQL details ( I am not a SQL person)
The server is running Server 2003 Standard w/4gb RAM with PAE enabled and
the /3GB switch

The Server page file is pointed at a dedicated 36gb drive
The job that is executing when this unexpected shutdown ocurs is sucking a
large chunk of data into a SQL DB that has a transaction log that is
specified to be FULL recovery model and was originally defined to be 35gb in
size residing on a dedicted 36gb drive that ALL databases ( there are about
8 databases on this SQL INSTANCE) are pointed to for their TX log
files.....remember I said "originally" we have since moved this big TX log
file to a raid 5 array . It just seemed wierd to have a TX log file
defined to be almost as big as the whole 36gb drive

Right now the server is up..and it did not shutdown last night and no "jobs
are running" ...but here are some operational stats:

in Task Manager

SQLSERVER.exe is using 2,424,412 memory


and PF usage = 2.92. gb


The current pagefile setting = 6144


Can anyone tell me these stats and setup detials are OK ??

Thanks

First off the /PAE is not needed since you only have 4GB of memory so remove
that. I can't remember if the /3GB is used with that version and edition of
Windows or not but you might want to try taking that out as well to give
more memory to the OS. The log file doesn't need to be as large as the db.
That is a result of not running log backups often enough while in FULL
recovery mode. You can most likely shrink that to a more reasonable size.
Putting log files on a RAID 5 is generally the wrong thing to do since it is
write intensive. A Raid 1 or Raid 10 would be better. But the bottom line
is I would run a Perfmon and SQL Trace near the time the event happens so
you can get some info on what is occurring at that time.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Dan DeCoursey" <DanDeCoursey (AT) discussions (DOT) microsoft.com> wrote

Dan DeCoursey (DanDeCoursey (AT) discussions (DOT) microsoft.com) writes:
If you look in the SQL Server error log, is there a stack dump? This may
give a clue.

How is the import from the Oracle database done? Is that a linked-server
query? In that case, my prime suspect would be the OLE DB provider used
to connect to Oracle. The stack dump in the SQL Server log should confirm
that. (Since the provider runs in-process an error like an Access Violation
can cause the entire SQL Server to wave bye-bye.)

You find the SQL Server error log in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG or similar
directory.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog (esquel (AT) sommarskog (DOT) se) writes:
I forgot to mention that you need to look at the files ERRORLOG.1 and
on. The current log, named only ERRORLOG, while not include any stack
dump from a crash that brought down SQL Server. (Unless SQL Server has
not been restarted since the most recent crash.)


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Well..... I had another "expert" tell me that I would need the PAE switch due
to "
Microsotft recommends you set you page file = 1.5x physical ram

if you try to do that with no PAE switch you get
"please select a maximun size equal to or less thant 4096"

this was expalined as : If you do not use PAE then the processor is still
only able to "ADDRESS" a max of 4gb. PAE is an INTEL feature not a Microsoft
feature. Physical or virtual ....memory is memory and you still need to use
the PAE switch in order to expand the procesors ability to ADDRESS ram above
4gb


So I involke the PAE swtich and viola....I now could specify the
MICROSOFT recommended 1.5x 4096 = 6144gb page file size


what do you think of my understanding of PAE so far ????

now onto /3gb
Root problem the server just shuts down and the only consistant fact Ihave
need able to document is the same SQL job is executing every single tiem this
happens......... so you recomend shffleing more ram to the OS would be a
better approach thant more memory to SQL ? please explain yourthinking ?

The TXLOG file..... the original DB developer defined the TXlog file = 35gb
in size......... it did not grow to that size.....this is what blew my mind..

there is a dedicated 36gb drive for txlog files and I find this one file
itself...defined to be 35bg and then auto grow 100mb when needed........

let me know what you all thinks about these detiasl I have rebutted

thanks








"Andrew J. Kelly" wrote:

See answers In-Line

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Dan DeCoursey" <DanDeCoursey (AT) discussions (DOT) microsoft.com> wrote

That is a very old recomendation and really only needed if you want a full
memory dump. If you enable the mini dump the swap file can be 4GB or less
without any issues as long as SQL Server is the only real app on the box.

You didn't say how you are doing this import and what all is involved but it
is very likely the component is using memory outside of SQL Server and thus
is starving the OS for memory at some point. The Oracle drivers are
notorious for eating lots of memory for no reason. If SQL Server has too
little memory it may not perform well but it should not crash the instance
or reboot the box. But if the OS doesn't have enough that can be a different
story.

There is no reason to keep it that large though.

The bottom line is you need to debug this further and gather more details as
to what is happening. Perfmon and trace are the tools that can help you to
see what is going on with SQL Server and the OS at that time.

I really appreciate you "going back and forth " with me on these
issue......as you can guess my head is spinning right about now.....

I have read all over the 1.5X for sizing the pagefile and all the other
whizbang stuff ( PAE and AWE) only to have you shoot it all down......


Ok on to the task mgr and perfmon....... what metrics do you recommend I
select in perfmon...and the job that seems to shutthe serverdown runs at
10:30pm after hours

thankls again

"Andrew J. Kelly" wrote: